Modify Entity Framework's expression tree as close as possible to T-SQL translation\execution

Question

I've been able to modify IQueryable expressions using an ExpressionVisitor and other custom Expressions.

My problem is with third party frameworks that use Entity Framework (e.g. OData), that modify the query inside internal methods and in places that make it hard for me to re-modify the query after they do.

At the end of the process, there is an IQueryable that represents an expression tree. Entity Framework knows how to translate that expression tree into T-SQL and execute it.

I'm looking to modify that Expression\IQueryable as close to execution as possible.

What's the best way I can do so?

Suspicious

can I clarify: what are you actually trying to do here? why do you want to mutate the expression trees, and could you not do it on the way in? – Sharyl

I have an xml column and I want to optimize my queries. So I have some code that knows how to call [xmlcolumn].value(xpath). After I modify the SQL, I need to deal with the projection,so I create a Anonymous type on the fly and project the result to it and then I use reflection to reproject it to my original Entity. Pretty complex, but it works pretty well. My problem starts when I use OData ($select\$expand) to create a custom type (that is not an IQueryable<T>) and make it hard for me to modify the expression tree, especially when the classes are internal. – Suspicious

Answer 1

You can use Entity Framework interception. This allows you to intercept all queries right before execution (including navigation property queries)

Entity Framework allows us to specify different types of interceptors on different aspects of query generation. Each interceptor can modify the query that gets executed. Interceptor types are:

1. IDbCommandInterceptor Methods of this interceptor will be called when executing a query. The query will already have been transformed in SQL and parameters will be set.

2. IDbCommandTreeInterceptor Methods of this interceptor will be called when the command tree is created. The command tree is an AST representation of the command. There are two command trees generated, one expressed in terms of in terms of the conceptual model (DataSpace.CSpace), this command tree will be closer to the LINQ query and another in terms of in terms of the storage model (DataSpace.SSpace)

3. IDbConfigurationInterceptor Methods of this interceptor are called when the DbConfiguration is loaded.

4. IDbConnectionInterceptor Methods of this interceptor are called when connections are established and when transactions occur.

5. IDbTransactionInterceptor Methods of this interceptor are called when transactions are committed or rolled back.

IDbCommandTreeInterceptor offers a good method of grabbing the command and changing it. The AST is pretty simple to understand and Entity Framework already provides the infrastructure for creating new command ASTs based on existing ones (the command AST is an immutable structure so we can’t just change an existing one).

Usage Example:

class CustomExpressionVisitor : DefaultExpressionVisitor
{
    // Override method to mutate the query 
}
class TestInterceptor : IDbCommandTreeInterceptor
{
    public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
    {
        if (interceptionContext.Result.DataSpace == DataSpace.CSpace)
        {
            // We only process query command trees 
            // (there can be others such as insert, update or delete
            var queryCommand = interceptionContext.Result as DbQueryCommandTree;
            if (queryCommand != null)
            {
                // A bit of logging to see the original tree
                Console.WriteLine(queryCommand.DataSpace);
                Console.WriteLine(queryCommand);

                // We call the accept method on the command expression with our new visitor. 
                // This method will return our new command expression with the changes the 
                // visitor has made to it
                var newQuery = queryCommand.Query.Accept(new CustomExpressionVisitor());
                // We create a new command with our new command expression and tell 
                // EF to use it as the result of the query
                interceptionContext.Result = new DbQueryCommandTree
                (
                     queryCommand.MetadataWorkspace,
                     queryCommand.DataSpace,
                     newQuery
                 );
                // A bit of logging to see the new command tree
                Console.WriteLine(interceptionContext.Result);
            }

        }

    }
}

// In code before using any EF context.
// Interceptors are registered globally.
DbInterception.Add(new TestInterceptor());

Note: Query plans are cached, so interception will not get called the first time a query is encountered and cached (the result you specify will be cached not the original). So this is safe to use for changes that are not dependent on context (ex: User, Request, Language).

Elery

Thank you very much for the answer. I was looking for a LINQ-Expression solution, but this is an interesting direction that I'm going to try. – Suspicious

A long time ago I started working on a library that allowed adding conditions automatically to executed queries. Using some reflection I manage to get EF to convert a LINQ expression to DbExpression and stich it to other commands. Mabe some of the code in there helps you, feel free to have a look github.com/dragomirtitian/Dionysos.Db.Autofilter – Elery