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).
Different ways of composing an Expression Tree
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.ExpandoObject/IDictionary<string, object>
- it is the most dynamic way of filtering the data.QueryField/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 and well tested with high-quality. However, the Linq-Expression parser of the library is not as extensive as Entity Framework. 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
andIDictionary<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.