How to Convert an Expression Tree to a Partial SQL Query

How to convert an expression tree to a partial SQL query?

The short answer seems to be that you cannot use a part of EF or LINQ to SQL as a shortcut to translation. You need at least a subclass of ObjectContext to get at the internal protected QueryProvider property, and that means all the overhead of creating the context, including all the metadata and so on.

Assuming you are ok with that, to get a partial SQL query, for example, just the WHERE clause you're basically going to need the query provider and call IQueryProvider.CreateQuery() just as LINQ does in its implementation of Queryable.Where. To get a more complete query you can use ObjectQuery.ToTraceString().

As to where this happens, LINQ provider basics states generally that

IQueryProvider returns a reference to IQueryable with the constructed expression-tree passed by the LINQ framework, which is used for further calls. In general terms, each query block is converted to a bunch of method calls. For each method call, there are some expressions involved. While creating our provider - in the method IQueryProvider.CreateQuery - we run through the expressions and fill up a filter object, which is used in the IQueryProvider.Execute method to run a query against the data store

and that

the query can be executed in two ways, either by implementing the GetEnumerator method (defined in the IEnumerable interface) in the Query class, (which inherits from IQueryable); or it can be executed by the LINQ runtime directly

Checking EF under the debugger it's the former.

If you don't want to completely re-invent the wheel and neither EF nor LINQ to SQL are options, perhaps this series of articles would help:

• How to: LINQ to SQL Translation
• How to: LINQ to SQL Translation - Part II
• How to: LINQ to SQL Translation - Part III

Here are some sources for creating a query provider that probably involve much more heavy lifting on your part to implement what you want:

• LINQ: Building an IQueryable provider series
• Creating custom LINQ provider using LinqExtender

Incorporate partial expression in LINQ/SQL query

You can write a method to construct a new GreaterThanOrEqual expression from two operands

The idea behind this is the following. You want to construct a new expression which looks like this:

td => td.DateShipped >= startDate.Value

using the body of your existing expression td => td.DateShipped, but important thing here is that td in the result expression and td in your GetDateExpression are different things, so if you just will write the greaterThanOrEqual expression without replacing you will get something like this:

td1 => td2.DateShipped >= startDate.Value

Therefore you need to replace td2 with td1 so that the expression looks like I wrote in the beginning. Therefore all the replacer does is replace each ParameterExpression it finds in the expression tree with our filterParam.

You can take a look at the following answers to read about this a bit more:

• Why we need replacer in the first place
• Implementation of ParameterReplacer used in my answer
• Great answer with different solutions for different cases

    Expression<Func<T, bool>> GreaterThanOrEqual(Expression<Func<T, DateTime?>> operand1, Expression<Func<T, DateTime?>> operand2)
    {
        var filterParam = Expression.Parameter(typeof(T));
        var greaterThanOrEqual = Expression.GreaterThanOrEqual(operand1.Body, operand2.Body);
        greaterThanOrEqual = (BinaryExpression) new ParameterReplacer(filterParam).Visit(greaterThanOrEqual);
        
        return Expression.Lambda<Func<T, bool>>(greaterThanOrEqual, filterParam);
    }
                                                                      
    internal class ParameterReplacer : ExpressionVisitor {
        private readonly ParameterExpression _parameter;

        protected override Expression VisitParameter(ParameterExpression node) {
            return base.VisitParameter(_parameter);
        }

        internal ParameterReplacer(ParameterExpression parameter) {
            _parameter = parameter;
        }
    }

Then use it like

    Expression<Func<T, DateTime?>> getDateExpression = m => m.Date;
    Expression<Func<MyClass, bool>> combined = GreaterThanOrEqual(getDateExpression, td => startDate.Value);
    
    query = query.Where(combined);

Convert LINQ Expression to SQL Text without DB Context

This is something I briefly looked into quite a while ago. You may want to have a look at http://iqtoolkit.codeplex.com/ and/or http://expressiontree.codeplex.com/ for ideas. As mentioned by others, Linq query provider building is far from trivial if you do not limit your scope to the minimum set of features you really need.

If your goals relate to "specification" or "query object" notion of repositories according to DDD, this may not be the best direction to take. Instead of CRUD like technology related abstractions, it may be more productive to focus on ways in which the behaviour of the domain can be expressed, with a minimum of direct dependencies on technology related abstractions. As Eric Evans recently discussed, he regrets the focus on the technical building blocks, such as repositories, in his initial descriptions of DDD.

Expression tree condition conversion with selector

I like doing these things, though as others have said, there's probably more efficient and better ways to do it:

void Main()
{
    Expression<Func<int, bool>> pageIndexCondition = idx => idx == 1;
    Expression<Func<BookPage, bool>> converted = ExpressionConverter.Convert(pageIndexCondition);
}

public class ExpressionConverter : ExpressionVisitor
{
    public static Expression<Func<BookPage, bool>> Convert(Expression<Func<int, bool>> e)
    {
        var oldParameter = e.Parameters.First();
        var newParameter = Expression.Parameter(typeof(BookPage), "bp");
        Expression<Func<BookPage, int>> x = (BookPage bp) => bp.PageIndex;
        var property = ((x.Body as MemberExpression).Member as PropertyInfo);
        var memberAccess = Expression.Property(newParameter, property);

        var converter = new ExpressionConverter(oldParameter, memberAccess);
        return (Expression<Func<BookPage, bool>>)Expression.Lambda(converter.Visit(e.Body), newParameter);
    }

    private ParameterExpression pe;
    private Expression replacement;

    public ExpressionConverter(ParameterExpression pe, Expression replacement)
    {
        this.pe = pe;
        this.replacement = replacement;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        if(node == pe)
            return replacement;

        return base.VisitParameter(node);
    }

}

How to build expression tree for ContainsT

Basically you need to process the Method, Object(expression that represents the instance for instance method calls or null for static method calls) and Arguments(a collection of expressions that represent arguments of the called method) properties of the MethodCallExpression class.

Specifically for Contains, you need to avoid (or process differently if needed) the string.Contains method, and also handle static methods like Enumerable.Contains as well as instance methods like ICollection<T>.Contains, List<T>.Contains etc. In order to get the list values (when possible), you have to find some sort of a constant expression.

Here is a sample:

private bool ParseContainsExpression(MethodCallExpression expression)
{
    // The method must be called Contains and must return bool
    if (expression.Method.Name != "Contains" || expression.Method.ReturnType != typeof(bool)) return false;
    var list = expression.Object;
    Expression operand;
    if (list == null)
    {
        // Static method
        // Must be Enumerable.Contains(source, item)
        if (expression.Method.DeclaringType != typeof(Enumerable) || expression.Arguments.Count != 2) return false;
        list = expression.Arguments[0];
        operand = expression.Arguments[1];
    }
    else
    {
        // Instance method
        // Exclude string.Contains
        if (list.Type == typeof(string)) return false;
        // Must have a single argument
        if (expression.Arguments.Count != 1) return false;
        operand = expression.Arguments[0];
        // The list must be IEnumerable<operand.Type>
        if (!typeof(IEnumerable<>).MakeGenericType(operand.Type).IsAssignableFrom(list.Type)) return false;
    }
    // Try getting the list items
    object listValue;
    if (list.NodeType == ExpressionType.Constant)
        // from constant value
        listValue = ((ConstantExpression)list).Value;
    else
    {
        // from constant value property/field
        var listMember = list as MemberExpression;
        if (listMember == null) return false;
        var listOwner = listMember.Expression as ConstantExpression;
        if (listOwner == null) return false;
        var listProperty = listMember.Member as PropertyInfo;
        listValue = listProperty != null ? listProperty.GetValue(listOwner.Value) : ((FieldInfo)listMember.Member).GetValue(listOwner.Value);
    }
    var listItems = listValue as System.Collections.IEnumerable;
    if (listItems == null) return false;

    // Do whatever you like with listItems

    return true;
}

Expression Tree in C#

Compiling and executing codes at run time is always a bit challenging.
The library that you mentioned is just a way.

You can use Roslyn which comes with C# 6.0 and Visual Studio 2015 by Microsoft and the C# team. You can't imagine how powerful is it. Here are some samples and walkthroughs:

https://github.com/dotnet/roslyn/wiki/Samples-and-Walkthroughs

And some other introductions:

https://en.wikipedia.org/wiki/.NET_Compiler_Platform

And here is some sample to create a REPL (something like what you want):

http://www.jayway.com/2015/05/09/using-roslyn-to-build-a-simple-c-interactive-script-engine/

Using Roslyn can simple have something like this:

var csScript =
    string.Format(@"
        var x = Math.Max(Math.Avg({0},3),Math.Avg(low1:3));
        x;
    ", high1, low1);
    //And this from the REPL
    Console.WriteLine(CSharpScriptEngine.Execute(csScript));

---

Related Topics