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.
.png)