Expression Trees

This is a feature that would allow you to compose a conditional expressions (to filter a data) when doing an operation in the database. This condition can be applied in both push/pull operations (i.e.: Insert, Delete, Update and Query).

The composition of the expression trees can be supported by various objects.

Object	Description
Anonymous Types	It is the most simple and direct way of filterting the results. You can use the anonymous object to filter data.
Linq-Expression	It is the most common way of filtering the data. Only works for entity-model based operations.
ExpandoObject or IDictionary<string, object>	It is the most dynamic way of filtering the data.
QueryField or QueryGroup	It is the most advance, efficient, performant and powerful way of composing a tree expression. However, it is a bit tedious and verbose.

The support to the query objects are massive, however, the Linq-Expression parser is not as extensive as other macro-ORMs. Therefore, we highly recommend to always use the QueryGroup and QueryField objects when composing a complex expression.

Anonymous Type

Below is a sample way of querying a data via an anonymous type.

using (var connection = new SqlConnection(connectionString))
{
    var customer = connection.Query<Customer>(new { Id = 10045 }).FirstOrDefault();
}

Or with multiple column.

using (var connection = new SqlConnection(connectionString))
{
    var customer = connection.Query<Customer>(new { FirstName = "John", LastName = "Doe" }).FirstOrDefault();
}

Please be aware that the compiler does not understand the anonymous types, any changes made on the column name would not trigger a pre-compilation exception. Also, the anonymous types only supports the expression-equality and cannot be used for other equalities (i.e.: non-equality, greater or lesser equality, etc).

Linq-Expression

Below is a sample way of querying via Linq expression.

using (var connection = new SqlConnection(connectionString))
{
    var customer = connection.Query<Customer>(e => e.Id == 10045).FirstOrDefault();
}

Or with multiple column.

using (var connection = new SqlConnection(connectionString))
{
    var customer = connection.Query<Customer>(e => e.FirstName == "John" && e.LastName == "Doe" }).FirstOrDefault();
}

Or with other operations.

using (var connection = new SqlConnection(connectionString))
{
    var states = new [] { "Chicago", "Washington", "Kansas" };
    var customers = connection.Query<Customer>(e => states.Contains(e.State) && e.IsActive == false && e.DateOfBirth >= DateTime.Parse("1970-01-01") });
}

ExpandoObject and Dictionary<string, object>

Below is a sample way of querying via an ExpandoObject and/or IDictionary<string, object> object.

using (var connection = new SqlConnection(connectionString))
{
    var where = new Dictionary<string, object>
    {
        { nameof(Customer.Id), 10045 }
    };
    var customer = connection.Query<Customer>(where).FirstOrDefault();
}

Or with multiple column.

using (var connection = new SqlConnection(connectionString))
{
    var where = new new Dictionary<string, object>
    {
        { nameof(Customer.FirstName), "John") },
        { nameof(Customer.LastName), "Doe" }
    };
    var customer = connection.Query<Customer>(where).FirstOrDefault();
}

Both the ExpandoObject and IDictionary<string, object> expression only supports the expression-equality and cannot be used for other equalities (i.e.: non-equality, greater or lesser equality, etc).

QueryField/QueryGroup

Below is a sample way of querying via QueryField.

using (var connection = new SqlConnection(connectionString))
{
    var customer = connection.Query<Customer>(new QueryField(nameof(Customer.Id), 10045)).FirstOrDefault();
}

Or with multiple column.

using (var connection = new SqlConnection(connectionString))
{
    var where = new []
    {
        new QueryField(nameof(Customer.FirstName), "John"),
        new QueryField(nameof(Customer.LastName), "Doe")
    };
    var customer = connection.Query<Customer>(where).FirstOrDefault();
}

Or with other operations.

using (var connection = new SqlConnection(connectionString))
{
    var states = new [] { "Chicago", "Washington", "Kansas" };
    var where = new []
    {
        new QueryField(nameof(Customer.State), Operation.In, states),
        new QueryField(nameof(Customer.IsActive),false),
        new QueryField(nameof(Customer.DateOfBirth), Operation.GreaterThanOrEqual, DateTime.Parse("1970-01-01"))
    };
    var customers = connection.Query<Customer>(where);
}

Please visit our Query Expressions reference implementation page for more details.