Thursday, August 13, 2009

LINQ, Expression trees and ORMapper

In response to the question of Jonathan of my post on expression trees I will post here some code on how an expression tree can be analyzed and used to create your own object relational mapper.

The core of the app is the expression tree visitor. Practically it traverses all the expressions of a LINQ expression and creates the appropriate SQL instructions for it. To get the correct column and table names it uses an interface called IMappingProvider which makes it possible to have different mapping providers. I created an AttributeMappingProvider which provides the mapping by defining attributes on the classes and properties, an XmlMappingProvider which reads the mapping information from an XML file and a DBMappingProvider which reads from a table in a database. My ORMapper is able to create Select and Delete queries by specifying a LINQ expression as where clause.

But enough theory; here is some code :-)
This is the core method which dispatches the incoming expression and calls the appropriate method:
/* /// <summary>
/// This method is the dispatcher for the different expression types.
/// NOTE: not all expression types are considered.
/// </summary>
/// <param name="expression">The expression.</param>
/// <param name="sql">The SQL string builder.</param>
/// <param name="isOnRightSide">Tells if the expression is on the right hand side. Necessary for some expression evaluations.</param> */
private object VisitExpression(Expression expression, StringBuilder sql, bool isOnRightSide)
{
LambdaExpression lambdaExpression = expression as LambdaExpression;
BinaryExpression binaryExpression = expression as BinaryExpression;
MemberExpression memberExpression = expression as MemberExpression;
ConstantExpression constantExpression = expression as ConstantExpression;
UnaryExpression unaryExpression = expression as UnaryExpression;
MethodCallExpression methodCallExpression = expression as MethodCallExpression;
ParameterExpression parameterExpression = expression as ParameterExpression;

if (lambdaExpression != null)
{
VisitExpression(lambdaExpression.Body, sql, false);
}
else if (binaryExpression != null)
{
VisitBinaryExpression(sql, binaryExpression);
}
else if (memberExpression != null)
{
return VisitMemberExpression(sql, isOnRightSide, memberExpression);
}
else if (constantExpression != null)
{
VisitConstantExpression(sql, constantExpression);
}
else if (unaryExpression != null)
{
VisitUnaryExpression(sql, unaryExpression);
}
else if (methodCallExpression != null)
{
VisitMethodCallExpression(sql, methodCallExpression);
}
else if (parameterExpression != null)
{
VisitParameterExpression(sql, parameterExpression);
}
else
{
throw new NotSupportedException(string.Format("The '{0}' is not supported!", expression.GetType().Name));
}

return null;
}


Let's take for example the method to analyze a binary expression:
private void VisitBinaryExpression(StringBuilder sql, BinaryExpression binaryExpression)
{
sql.Append("(");
VisitExpression(binaryExpression.Left, sql, false);
sql.Append(GetOperandFromExpression(binaryExpression));
VisitExpression(binaryExpression.Right, sql, true);
sql.Append(") ");
}

You can see here that for binary expressions (e.g. =, &&, ||) I always open a parenthesis; this creates also unnecessary parenthesis, but makes sure that boolean conditions are evaluated correctly. Then the left expression is evaluated, the operator is added, the right expression is evaluated and finally a closing parenthesis is added.
This is the dispatcher method for the operators:
/* /// <summary>
/// This method is the dispatcher for the operands.
/// NOTE: not all operands are implemented!
/// </summary>
/// <param name="expression">The expression to dispatch.</param>
/// <returns>The appropriate SQL operand.</returns>*/
private string GetOperandFromExpression(Expression expression)
{
string operand = string.Empty;
switch (expression.NodeType)
{
case ExpressionType.And:
operand = "AND";
break;
case ExpressionType.AndAlso:
operand = "AND";
break;
case ExpressionType.Equal:
operand = "=";
break;
case ExpressionType.ExclusiveOr:
operand = "OR";
break;
case ExpressionType.GreaterThan:
operand = ">";
break;
case ExpressionType.GreaterThanOrEqual:
operand = ">=";
break;
case ExpressionType.Not:
operand = "NOT";
break;
case ExpressionType.NotEqual:
operand = "<>";
break;
case ExpressionType.Or:
operand = "OR";
break;
case ExpressionType.OrElse:
operand = "OR";
break;
default:
throw new NotImplementedException();
}

return operand + " ";
}

To get the correct string to add to the sql query I wrote a little method which performs also escaping of the single quote. Additionally it calls the to string method with the invariant culture if the object supports it.
private string FormatValue(object value)
{
if(value == null || value == DBNull.Value)
{
return "NULL";
}

string stringValue = value.ToString();
var invariantMethod = value.GetType().GetMethod("ToString", new Type[] { typeof(CultureInfo) });
if (invariantMethod != null)
{
stringValue = (string)invariantMethod.Invoke(value, new object[] { CultureInfo.InvariantCulture });
}

stringValue = stringValue.Replace("'", "''");

if (value is string || value is DateTime)
{
return string.Format("'{0}'", stringValue);
}
else
{
return stringValue;
}
}


To test all this I created a little sample application and a sample class. The customer class supports the AttributeMappingProvider and specifies the mapping on the class and propeties:
[DBTable("cust")]
class Customer
{
[DBField("nam")]
public string Name { get; set; }

[DBField("num")]
public int Age { get; set; }

[DBField("height")]
public int Height;

[DBField("surname")]
public string Surname;
}

To get some results I wrote this code:
static void Main(string[] args)
{
ORMapper mapper = new ORMapper();
var c1 = new Customer{Name = "cus2"};
var selectQuery = mapper.Select<Customer>(c => c.Name == "hallo" && c.Age == 12 || c.Name == c1.Name && c.Name == 4.ToString() && c.Name.Contains("aaa"));
var deleteQuery = mapper.Delete<Customer>(c => c.Name == "hallo" && c.Age == 12 || c.Name == c1.Name && c.Name == 4.ToString() && c.Name.Contains("aaa"));
Console.WriteLine(selectQuery);
Console.WriteLine(deleteQuery);
Console.ReadLine();
}

which outputs this:
SELECT * FROM cust WHERE (((nam = 'hallo') AND (num = 12)) OR ((((nam = 'cus2') AND (nam = '4')) AND (nam LIKE '%aaa%')) AND (height = 10)));

DELETE FROM cust WHERE (((nam = 'hallo') AND (num = 12)) OR (((nam = 'cus2') AND (nam = '4')) AND (nam LIKE '%aaa%')));

You can cahnge the mapping provider in the constructor of the ORMapper.
A sample file for the XmlAttributeMapper is also included. Here the mapping is specified like this:
<?xml version="1.0" encoding="utf-8" ?>
<mapping>
<Customer table="tbl_cus">
<map property="Name" column="cus_nam"></map>
<map property="Age" column="cus_age"></map>
</Customer>
</mapping>


I did not test the DBMappingProvider, but You can easily adapt it to your needs.

You can download the project and source code files from here.

8 comments:

Juri Strumpflohner said...

Cool approach. I'll take a look at the project.

Anonymous said...

Very very VERY nice work.
A couple of small things though.

1: In FormatValue you should do a NULL and DBNull.Value check to return "NULL"

2: In the VisitMemberExpression method, when you get a value from the fieldInfo object you dont set it in the string builder.

Manfred said...

Thanks for your suggestions! I added now the null and dbnull check and I added support for fields. I also removed the extra space which was added after each closing parenthesis.

If there are bugs or Someone has suggestions, let me know...

Anonymous said...

Dude! This is an awesome idea. I'm stealing it :-)

do u mind if i pick it apart?

Thanx

Manfred said...

Hi,
thanks.
Well You can use the code (also in your commercial projects) but a link to my blog or some notice from where you got the code would be nice.

Anonymous said...

Excellent piece of code!

I had one problem though: local variables were not handled properly.

You mentioned this problem in a blog post (http://manfred-ramoser.blogspot.com/2009/04/linq-expression-tree.html), but if i'm not mistaken, you did not mention how to apply the solution to the published code.

My solution, make following change in in ORMapper.cs (from line 166) :

if (fieldInfo != null && constExpr != null)
{
var value = fieldInfo.GetValue(constExpr.Value);
sql.Append(FormatValue(value));
return value;
}

instead of:

if (fieldInfo != null && constExpr != null)
{
return fieldInfo.GetValue(constExpr.Value);
}

Anonymous said...

Great code!

I ran into an issue with your code in the following scenario:

predicate = t => !(t.Name == "bob");

After visiting the above ! is ignored.

I was able to fix that by adding the following to the VistiUnaryExpression method:

if (unaryExpression.NodeType == ExpressionType.Not) sql.Append("NOT ");

Anonymous said...

I also ran into an issue with using variables like so:

string fname= "bob";
predicate = t => !(t.Name == fname);

To fix this issue this helped me a lot:

http://blogs.msdn.com/b/mattwar/archive/2007/08/01/linq-building-an-iqueryable-provider-part-iii.aspx

you would then user it like so:

public string BuildQueryString(Expression expression)
{
StringBuilder sql = new StringBuilder();

expression = Evaluator.PartialEval(expression);
VisitExpression(expression, sql , false);

return sql.ToString();
}