entity2sql

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.

The code of the entire project is available here

Any advice is welcome.