How To: LINQ To SQL Transformations - Part II






4.16/5 (7 votes)
An article on LINQ To SQL Transformations.
Introduction
This article is the second in a series outlining how to translate LINQ expression trees to SQL statements that can be executed against multiple RDBMS systems and not just Microsoft's SQL Server offerings. The articles will also illustrate how to:
- Correctly and comprehensively translate binary and unary expressions that have valid translations into SQL.
- Translate function calls (e.g.,
customer.FirstName.ToUpper()
) that have SQL equivalents. - Implement
GroupBy
. - Implement the
IQueryable
methodsANY
,ALL
,COUNT
,AVERAGE
etc. - Parameterize queries instead of embedding constants in the SQL transformation.
- Perform caching of previously translated expression trees.
- Potentially not make use of MARS.
Background
In the last article in this series, I outlined how to implement a class used in the implementation, the Binder
. In essence, the function of the binder is to assign values in a DbDataReader
to a newly instantiated object of a given class.
The motivating example will be the following LINQ query:
var customers = from customer in customers
where customer.City == city
select new { Name = customer.ContactName,
Phone = customer.Phone };
This will translate into the following SQL statement:
SELECT t0.ContactName, t0.Phone
FROM dbo.Customers AS t0
WHERE (t0.City = @p0)
and the following method calls:
.Where(customer => (customer.City = value(LinqTest.NorthwindLinq+<>c__DisplayClass1).city))
.Select(customer => new <>f__AnonymousType0`2(Name = customer.ContactName,
Phone = customer.Phone))
The Binder
will translate the Lambda Expression:
customer => new <>f__AnonymousType0`2(Name = customer.ContactName,
Phone = customer.Phone)
to:
reader => new <>f__AnonymousType0`2(Name = IIF(Not(reader.IsDBNull(0)),
reader.GetString(0), Convert(null)),
Phone = IIF(Not(reader.IsDBNull(1)),
reader.GetString(1), Convert(null)))
The Binder: In Depth
The Binder
will need to perform the following tasks in order to perform the above translation:
- Determine the position (or index, if you prefer) in the
DbDataReader
that corresponds to a given field. - Determine the type of the field we wish to retrieve from the
DbDataReader
and generate the appropriate expression. - Handle a few special cases not illustrated in our motivating example. These will be discussed at the end of the article, for the sake of readability.
In the motivating example, the field customer.ContactName
will be located at index 0 of the DbDataReader
.
In the motivating example, customer.ContactName
is a string and is located at index 0. We, therefore, need to generate a call to reader.GetString(0)
.
Let's begin with the field declarations:
private class Binder : ExpressionVisitor {
private readonly LambdaExpression selector = null;
private readonly LambdaExpression binderLambda = null;
private readonly Delegate binderMethod = null;
private readonly Dictionary<string,> fieldPositions = new Dictionary<string,>();
private readonly ParameterExpression reader =
Expression.Parameter(typeof(DbDataReader), "reader");
private static readonly MethodInfo getBoolean =
typeof(DbDataReader).GetMethod("GetBoolean");
private static readonly MethodInfo getByte =
typeof(DbDataReader).GetMethod("GetByte");
private static readonly MethodInfo getChar =
typeof(DbDataReader).GetMethod("GetChar");
private static readonly MethodInfo getDateTime =
typeof(DbDataReader).GetMethod("GetDateTime");
private static readonly MethodInfo getDecimal =
typeof(DbDataReader).GetMethod("GetDecimal");
private static readonly MethodInfo getDouble =
typeof(DbDataReader).GetMethod("GetDouble");
private static readonly MethodInfo getGUID =
typeof(DbDataReader).GetMethod("GetGuid");
private static readonly MethodInfo getInt16 =
typeof(DbDataReader).GetMethod("GetInt16");
private static readonly MethodInfo getInt32 =
typeof(DbDataReader).GetMethod("GetInt32");
private static readonly MethodInfo getInt64 =
typeof(DbDataReader).GetMethod("GetInt64");
private static readonly MethodInfo getString =
typeof(DbDataReader).GetMethod("GetString");
private static readonly MethodInfo getValue =
typeof(DbDataReader).GetMethod("GetValue");
------------------------------------------------------------------------------------------
}
- The selector field will hold a reference to the Lambda Expression we are translating.
- The
binderLambda
field will hold a reference to the result of the translation. - The
binderMethod
is a delegate that will be generated by a call tobinderLambda.Compile()
. - The
fieldPositions
field is a dictionary that will keep track of the fields we need to retrieve from theDbDataReader
and their positions. - The
reader
is a parameter of typeDbDataReader
that will be referenced by the Lambda Expression. - The fields of type
MethodInfo
are used to look up a field in thereader
and return the value required as the required type.
In the motivating example, that will be the expression:
customer => new <>f__AnonymousType0`2(Name = customer.ContactName,
Phone = customer.Phone)
In the motivating example, that will be the expression:
reader => new <>f__AnonymousType0`2(Name = IIF(Not(reader.IsDBNull(0)),
reader.GetString(0), Convert(null)),
Phone = IIF(Not(reader.IsDBNull(1)),
reader.GetString(1), Convert(null)))
Conceptually, it will be employed as follows:
<>f__AnonymousType0`2 anyonymousType =
(<>f__AnonymousType0`2)binder.DynamicInvoke(reader)
The details of its use will be covered in the next article.
In the motivating example, that will be the expression:
reader => new <>f__AnonymousType0`2(Name = IIF(Not(reader.IsDBNull(0)),
reader.GetString(0), Convert(null)),
Phone = IIF(Not(reader.IsDBNull(1)),
reader.GetString(1), Convert(null)))
I will not give a blow by blow account of what every method does; rather, I will give a more detailed conceptual overview.
Conceptual Overview
A LINQ Expression such as:
customer => new <>f__AnonymousType0`2(Name = customer.ContactName,
Phone = customer.Phone)
is a tree that consists of a series of nodes.
In the example above, the expression tree would conceptually look something like so:
Expression.Lambda(Expression.New(anonymousType2Constructor,
new Expression[]{
Expression.MakeMemberAccess(
Expression.Parameter(typeof(customer, "customer"),
typeof(Customer).GetProperty("ContactName")),
Expression.MakeMemberAccess(
Expression.Parameter(customer "customer"),
typeof(Customer).GetProperty("Phone"))
}));
You will recall from above that the reader we shall employ will be generated from the execution of a SQL statement that looks like so:
SELECT t0.ContactName, t0.Phone
FROM dbo.Customers AS t0
WHERE (t0.City = @p0)
The key thing to note is that the fields in the DbDataReader
will be returned in the same order as they are accessed in the Lambda Expression, therefore ContactName
will be in position 0 of the DbDataReader
and Phone
will be in position 1 of the DbDataReader
. That, in essence, is how we know what fields are in what positions.
Now, we simply need to replace references to the customer
parameter with references to the reader
parameter and then replace references to properties/fields of the customer
parameter with calls to the appropriate reader.GetXXX(fieldPosition)
methods. E.g.:
Replace:
Expression.MakeMemberAccess(Expression.Parameter(typeof(Customer), "customer"),
typeof(Customer).GetProperty("ContactName"))
with:
Expression.Call(reader, getString, Expression.Constant(0));
In order to make these changes, we must be able to inspect every node in the expression tree and change that node if necessary. This is where the ExpressionVisitor
discussed in Part 1 comes in. We subclass the ExpressionVisitor
and override the following method:
protected override Expression VisitMemberAccess(MemberExpression m) {
------------
}
That, in essence, is it.
Notes:
- Method calls:
- Lambda queries that do not yield a projection:
Suppose we have a Lambda query such as:
var x = from customer in customers
where customer.City == city
select new { Name = customer.ContactName,
OrderCount = customer.Orders.Count() };
We should expect to generate a SQL statement that looks like:
SELECT t0.ContactName,
(
SELECT Count(*)
FROM dbo.Orders AS t2
WHERE (t2.CustomerID = t0.CustomerID)
)
AS OrderCount
FROM dbo.Customers AS t0
WHERE (t0.City = @p0)
We need to intercept the call to:
customer.Orders.Count()
Please see the source code for details.
Suppose we have a Lambda query such as:
var x = from customer in customers
where customer.City == city
select customer;
We need to instantiate an object of type customer
, and then assign every property that has a corresponding column in the database with the appropriate value from the DbDataReader
.
Please see the source code for details.
Binder Class Listing
private class Binder : ExpressionVisitor {
private readonly LambdaExpression selector = null;
private readonly LambdaExpression binderLambda = null;
private readonly Delegate binderMethod = null;
private readonly Dictionary<string,> fieldPositions = new Dictionary<string,>();
private readonly ParameterExpression reader =
Expression.Parameter(typeof(DbDataReader), "reader");
private static readonly MethodInfo getBoolean =
typeof(DbDataReader).GetMethod("GetBoolean");
private static readonly MethodInfo getByte =
typeof(DbDataReader).GetMethod("GetByte");
private static readonly MethodInfo getChar =
typeof(DbDataReader).GetMethod("GetChar");
private static readonly MethodInfo getDateTime =
typeof(DbDataReader).GetMethod("GetDateTime");
private static readonly MethodInfo getDecimal =
typeof(DbDataReader).GetMethod("GetDecimal");
private static readonly MethodInfo getDouble =
typeof(DbDataReader).GetMethod("GetDouble");
private static readonly MethodInfo getGUID =
typeof(DbDataReader).GetMethod("GetGuid");
private static readonly MethodInfo getInt16 =
typeof(DbDataReader).GetMethod("GetInt16");
private static readonly MethodInfo getInt32 =
typeof(DbDataReader).GetMethod("GetInt32");
private static readonly MethodInfo getInt64 =
typeof(DbDataReader).GetMethod("GetInt64");
private static readonly MethodInfo getString =
typeof(DbDataReader).GetMethod("GetString");
private static readonly MethodInfo getValue =
typeof(DbDataReader).GetMethod("GetValue");
public Delegate BinderMethod {
get {
return binderMethod;
}
}
public Binder(LambdaExpression selector) {
this.selector = selector;
if (selector.Body.NodeType != ExpressionType.Parameter) {
binderLambda = Expression.Lambda(((LambdaExpression)this.Visit(selector)).Body,
reader);
}
else {
binderLambda = GetBindingLambda(selector);
}
binderMethod = binderLambda.Compile();
}
protected override Expression VisitMethodCall(MethodCallExpression m) {
switch (m.Method.Name) {
case "Count":
case "Average":
case "Max":
case "Min":
case "Sum":
break;
default:
return base.VisitMethodCall(m);
}
Debug.Assert(m.Arguments.Count > 0);
Debug.Assert(m.Arguments[0].NodeType == ExpressionType.MemberAccess);
if (GetAccessedType(m.Arguments[0] as MemberExpression) !=
selector.Parameters[0].Type) {
return m;
}
int fieldPosition = GetFieldPosition(m.ToString());
return GetFieldReader(m, fieldPosition);
}
protected override Expression VisitMemberAccess(MemberExpression m) {
Debug.Assert(selector.Parameters.Count == 1);
if (GetAccessedType(m) != selector.Parameters[0].Type) {
return m;
}
int fieldPosition = GetFieldPosition(m);
return GetFieldReader(m, fieldPosition);
}
private Expression GetFieldReader(Expression m, int fieldPosition) {
var field = Expression.Constant(fieldPosition, typeof(int));
var readerExpression = GetReaderExpression(m, field);
var isDbNull = Expression.Call(reader,
typeof(DbDataReader).GetMethod("IsDBNull"),
field);
var conditionalExpression =
Expression.Condition(Expression.Not(isDbNull),
readerExpression,
Expression.Convert(Expression.Constant(null),
readerExpression.Type));
return conditionalExpression;
}
private Expression GetReaderExpression(Expression m, ConstantExpression field) {
MethodInfo getReaderMethod = GetReaderMethod(m);
var readerExpression = Expression.Call(reader, getReaderMethod, field);
if (getReaderMethod.ReturnType == m.Type) {
return readerExpression;
}
return Expression.Convert(readerExpression, m.Type);
}
private static MethodInfo GetReaderMethod(Expression m) {
Type memberType = GetMemberType(m);
MethodInfo getMethod = null;
switch (Type.GetTypeCode(memberType)) {
case TypeCode.Boolean:
getMethod = getBoolean;
break;
case TypeCode.Byte:
getMethod = getByte;
break;
case TypeCode.Char:
getMethod = getChar;
break;
case TypeCode.DateTime:
getMethod = getDateTime;
break;
case TypeCode.Decimal:
getMethod = getDecimal;
break;
case TypeCode.Double:
getMethod = getDouble;
break;
case TypeCode.Int16:
getMethod = getInt16;
break;
case TypeCode.Int32:
getMethod = getInt32;
break;
case TypeCode.Int64:
getMethod = getInt64;
break;
case TypeCode.String:
getMethod = getString;
break;
case TypeCode.Object:
getMethod = getValue;
break;
default:
if (m.Type == typeof(Guid)) {
getMethod = getGUID;
}
else {
getMethod = getValue;
}
break;
}
return getMethod;
}
private int GetFieldPosition(MemberExpression m) {
return GetFieldPosition(m.Member.Name);
}
private int GetFieldPosition(string fieldName) {
int fieldPosition = 0;
if (fieldPositions.ContainsKey(fieldName)) {
fieldPosition = fieldPositions[fieldName];
return fieldPosition;
}
fieldPosition = fieldPositions.Count();
fieldPositions.Add(fieldName, fieldPosition);
return fieldPosition;
}
private static Type GetMemberType(Expression m) {
Type memberType = null;
if (m.Type.Name == "Nullable`1") {
memberType = m.Type.GetGenericArguments()[0];
}
else {
memberType = m.Type;
}
return memberType;
}
private static Type GetAccessedType(MemberExpression m) {
if (m.Expression.NodeType == ExpressionType.MemberAccess) {
return GetAccessedType((MemberExpression)m.Expression);
}
return m.Expression.Type;
}
private LambdaExpression GetBindingLambda(LambdaExpression selector) {
var instanceType = selector.Body.Type;
// this is a hack
var properties = (from property in instanceType.GetProperties()
where property.PropertyType.IsValueType ||
property.PropertyType == typeof(string)
orderby property.Name
select instanceType.GetField("_" + property.Name,
BindingFlags.Instance |
BindingFlags.NonPublic))
.ToArray();
var bindings = new MemberBinding[properties.Length];
for (int i = 0; i < properties.Length; i++) {
var callMethod = GetFieldReader(
Expression.MakeMemberAccess(
Expression.Parameter(instanceType, "param"),
properties[i]),
i);
bindings[i] = Expression.Bind(properties[i], callMethod);
}
return Expression.Lambda(Expression.MemberInit(Expression.New(instanceType),
bindings),
reader);
}
}