loading...

entity2sql

antdimot profile image Antonio Di Motta ・2 min read

I have always had a strong interest in making small libraries that could facilitate the life of a developer. The most useful libraries are those that allow you to solve recurring problems ( possibly without creating others :) )

For this purpose, some time ago, I created a library that would allow me to generate SQL code from the definition of classes that represented my entity data model. It is not an ORM (complete or micro) but a simple generator of SQL code.

I know that there are already many excellent solutions around, but for me, it was an opportunity to analyze an interesting problem and at the same time have full control of the SQL code that I wanted to generate.

The final result is the following:

var sqlBuilder = new SQLStatementBuilder();

var select = sqlBuilder.MakeSelect<User>( o => o.FirstName == "Antonio" || o.LastName == "Di Motta" && o.Age == 150 );
// SELECT t1.ID,t1.FIRST_NAME,t1.LAST_NAME,t1.AGE,t1.RoleID FROM USERS t1 WHERE (t1.FIRST_NAME = 'Antonio' OR (t1.LAST_NAME = 'Di Motta' AND t1.AGE = 150 ))

var join = sqlBuilder.MakeJoin<User,Role>( (u,r) => u.Role.Id == r.Id );
// SELECT t1.ID,t1.FIRST_NAME,t1.LAST_NAME,t1.AGE,t1.RoleID,t2.ID,t2.NAME FROM USERS t1 INNER JOIN ROLES t2 ON t1.RoleID=t2.ID

In order to generate the SQL code, I added metadata (by custom attribute) to the classes of the domain model, below an example:

[TableMap( Name = "USERS" )]
public class User
{
    [PKeyMap( Name ="ID")]
    public int Id { get; set; }

    [ColumnMap( Name ="FIRST_NAME")]
    public string FirstName { get; set; }

    [ColumnMap( Name = "LAST_NAME" )]
    public string LastName { get; set; }

    [ColumnMap( Name = "AGE" )]
    public int Age { get; set; }

    [ColumnMap( Name = "RoleID" )]
    public Role Role { get; set; }
}

[TableMap( Name = "ROLES" )]
public class Role
{
    [PKeyMap( Name = "ID" )]
    public int Id { get; set; }

    [ColumnMap( Name = "NAME" )]
    public string Name { get; set; }
}

The main classes of the library are SQLStatementBuilder and EntityInfo.
The first generates the SQL code by reading the metadata via reflection, which is a very expensive operation in terms of speed, for this reason, it maintains an EntityInfo dictionary for each entity whose metadata it has read.

EntityInfo retains metadata information and It is created on first access to the entity, from the second access on it will no longer be necessary to use reflection.

Alt Text

The code of the entire project is available on github

Any advice is welcome.

Posted on by:

antdimot profile

Antonio Di Motta

@antdimot

Web addicted, passionate about cloud, mobile, running and MOBA games.

Discussion

markdown guide