ExpressionExtensionSQL - is a sample lambda expression to sql converter .NET

Packages

Nugget feed: https://www.nuget.org/packages/ExpressionExtensionSQL/

Package	NuGet Stable	NuGet Pre-release	Downloads
ExpressionExtensionSQL
ExpressionExtensionSQL.Dapper

Features

ExpressionExtensionSQL is a NuGet library which you can add to your project to achieve lambda expression in SQL code. It allows your domain doesn't need to understand SQL language mainly for creating filters. Very suitable for use with SpecificationPattern.

Samples:

Example usage:

 Expression<Func<Merchant, bool>> expression = x => x.Name == "merchant1" && x.CreatedAt>=DateTime.Now;
 var where = expression.ToSql();
 Console.Write(where.Sql);
 

Output will be:

([Merchant].[Name] = @1) AND ([Merchant].[CreatedAt]>=@2)

where.Parameters contains a list of parameters that can be used in ADO queries.

In many cases the name of the table or column in the database is different from the name of the entity or property. For these cases this framework allows to create a mapping through attribute or fluent mapping.

Attribute

Class

   [TableName("tblOrder")]
    public class Order {

        public int Id { get; set; }
        public DateTime CreatedAt { get; set; }
        [ColumnName("amount")]
        public int TotalAmount { get; set; }
    }

Example usage:

 Expression<Func<Order, bool>> expression = x => x.TotalAmount >10 && x.CreatedAt>=DateTime.Now;
 var where = expression.ToSql();
 Console.Write(where.Sql);
 

Output will be:

([tblOrder].[amount] = @1) AND ([tblOrder].[CreatedAt]>=@2)

Classes that have inheritance, their superclasses must be defined as abstract. Otherwise, the table name will be equivalent to that of the parent class.

Example usage:

If the classes are declared like below:

public class Entity {
    public int Id { get; set; }
    public DateTime CreatedAt { get; set; }
}
public class Merchant : Entity {        
    public string Name { get; set; }
}

...

Expression<Func<Merchant, bool>> expression = x => x.Id == 1;
 var where = expression.ToSql();
 Console.Write(where.Sql);

The previous usage example, will be output like this:

([Entity].[Id] = @1)

But, if the Entity class was an abstract class, the output would be like this:

([Merchant].[Id] = @1)

Fluent Mapping

Class

    public class Order {

        public int Id { get; set; }
        public DateTime CreatedAt { get; set; }
        public int TotalAmount { get; set; }
    }

Example usage:

 Configuration.GetInstance().Entity<Order>().ToTable("tblTeste");
 Configuration.GetInstance().Entity<Order>().Property(p => p.TotalAmount).ToColumn("valor");
 
 Expression<Func<Order, bool>> expression = x => x.TotalAmount >10 && x.CreatedAt>=DateTime.Now;
 var where = expression.ToSql();
 Console.Write(where.Sql);
 

Output will be:

([tblTeste].[valor] = @1) AND ([tblTeste].[CreatedAt]>=@2)

The Configuration class is static and can be used anywhere on your system and the configured mapping is reused for all expressions that contain the mapped entity

ExpressExtensionSQL.Dapper

ExpressionExtensionSQL.Dapper is an dapper extension that allows you to use lambda expressions as filters in your Query.

Samples:

        public IQueryable<Period> GetAll(Expression<Func<Order, bool> filter) {

            var query = $@"SELECT * FROM [Order]
                            {{where}}"

            var connection = this.GetConnection();

            return connection.Query<Order>(query, expression: filter);
         }

var result=OrderRepository.GetAll(p=>p.CreatedAt>=DateTime.Now);

In the above example it is important to note the use of {where}. This keyword will tell you where to include the where clause extracted from the expression.