Entity Framework/Core and LINQ to Entities (5) Query Translation Implementation

Latest EF Core version of this article: https://weblogs.asp.net/dixin/entity-framework-core-and-linq-to-entities-5-query-translation-implementation#

EF version of this article: https://weblogs.asp.net/dixin/entity-framework-and-linq-to-entities-5-query-translation #

The previous part demonstrated what are the SQL translations of the LINQ to Entities queries. This part discusses how the translation is implemented. Regarding different database systems can have different query languages or different query APIs, EF/Core implement a provider model to work with different kinds of databases. In EF Core, the base libraries are the Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.Relational NuGet packages. Microsoft.EntityFrameworkCore provides the database provider contracts as Microsoft.EntityFrameworkCore.Storage.IDatabaseProviderServices interface. And the SQL database support is implemented by the Microsoft.EntityFrameworkCore,SqlServer NuGet package, which provides Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseProviderServices type to implement IDatabaseProviderServices. There are other libraries for different databases, like Microsoft.EntityFrameworkCore.SQLite NuGet package for SQLite, etc.

In EF, the EntityFramework NuGet package contains 2 assemblies, EntityFramework.dll and EntityFramework.SqlServer.dll. The base library EntityFramework.dll provides database provider contracts as System.Data.Entity.Core.Common.DbProviderServices abstract class, and the SQL database provider library EntityFramework.SqlServer.dll provides System.Data.Entity.SqlServer.SqlProviderServices to implement DbProviderServices

With this provider model, EF/Core breaks the translation into 2 parts. First, IQueryable query methods work with expression trees, and EF/Core base libraries translate these .NET expression tree to generic, intermediate database expression tree; Then the specific EF/Core database provider is responsible to generate query language for the specific database.

Code to LINQ expression tree#

Before translation, .NET expression tree must be built to represent the query logic. As fore mentioned, expression tree enables function as data. In C#, an expression tree shares the same syntax as functions, but is compiled to abstract syntactic tree representing function’s source code. In LINQ, IQueryable utilizes expression tree to represent the abstract syntactic structure of a remote query.

IQueryable and IQueryProvider#

IQueryable has been demonstrated:

1
namespace System.Linq
2
{
3
    public interface IQueryable<out T> : IEnumerable<T>, IEnumerable, IQueryable
4
    {
5
        // IEnumerator<T> GetEnumerator(); from IEnumerable<T>.
6

7
        // Type ElementType { get; } from IQueryable.
8

9
        // Expression Expression { get; } from IQueryable.
10

11
        // IQueryProvider Provider { get; } from IQueryable.
12
    }
13
}

It is a wrapper of iterator factory, an element type, an expression tree representing the current query’s logic, and a query provider of IQueryProvider type:

1
namespace System.Linq
2
{
3
    public interface IQueryProvider
4
    {
5
        IQueryable CreateQuery(Expression expression);
6

7
        IQueryable<TElement> CreateQuery<TElement>(Expression expression);
8

9
        object Execute(Expression expression);
10

11
        TResult Execute<TResult>(Expression expression);
12
    }
13
}

IQueryProvider has CreateQuery and Execute methods, all accepting a expression tree parameter. CreateQuery methods return an IQueryable query, and Execute methods return a query result. These methods are called inside the Queryable methods.

Queryable methods#

As fore mentioned, Queryable also provides 2 kinds of query methods, sequence queries returning IQueryable query, and value queries returning a query result. Take Where, Select, and First as examples, the following are their implementations:

1
namespace System.Linq
2
{
3
    public static class Queryable
4
    {
5
        public static IQueryable<TSource> Where<TSource>(
6
            this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
7
        {
8
            Func<IQueryable<TSource>, Expression<Func<TSource, bool>>, IQueryable<TSource>> currentMethod =
9
                Where;
10
            MethodCallExpression whereCallExpression = Expression.Call(
11
                method: currentMethod.Method,
12
                arg0: source.Expression,
13
                arg1: Expression.Quote(predicate));
14
            return source.Provider.CreateQuery<TSource>(whereCallExpression);
15
        }
16

17
        public static IQueryable<TResult> Select<TSource, TResult>(
18
            this IQueryable<TSource> source, Expression<Func<TSource, TResult>> selector)
19
        {
20
            Func<IQueryable<TSource>, Expression<Func<TSource, TResult>>, IQueryable<TResult>> currentMethod =
21
                Select;
22
            MethodCallExpression selectCallExpression = Expression.Call(
23
                method: currentMethod.Method,
24
                arg0: source.Expression,
25
                arg1: Expression.Quote(selector));
26
            return source.Provider.CreateQuery<TResult>(selectCallExpression);
27
        }
28

29
        public static TSource First<TSource>(
30
            this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
31
        {
32
            Func<IQueryable<TSource>, Expression<Func<TSource, bool>>, TSource> currentMethod = First;
33
            MethodCallExpression firstCallExpression = Expression.Call(
34
                method: currentMethod.Method,
35
                arg0: source.Expression,
36
                arg1: Expression.Quote(predicate));
37
            return source.Provider.Execute<TSource>(firstCallExpression);
38
        }
39

40
        public static TSource First<TSource>(this IQueryable<TSource> source)
41
        {
42
            Func<IQueryable<TSource>, TSource> currentMethod = First;
43
            MethodCallExpression firstCallExpression = Expression.Call(
44
                method: currentMethod.Method,
45
                arg0: source.Expression);
46
            return source.Provider.Execute<TSource>(firstCallExpression);
47
        }
48

49
        // Other members.
50
    }
51
}

They just build a MethodCallExpression expression, representing the current query method is called. Then they obtain query provider from source’s Provider property. The sequence query methods call query provider’s CreateQuery method to return IQueryable query, and the value query methods call query provider’s Execute method to return a query result. All Queryable methods are implemented in this pattern, except AsQueryable, which is discussed in the previous part.

Build LINQ to Entities abstract syntax tree#

With above Where and Select query methods, a simple LINQ to Entities query can be implemented to return an IQueryable of values:

1
internal static partial class Translation
2
{
3
    internal static void WhereAndSelect(AdventureWorks adventureWorks)
4
    {
5
        // IQueryable<string> products = adventureWorks.Products
6
        //    .Where(product => product.Name.Length > 10)
7
        //    .Select(product => product.Name);
8
        IQueryable<Product> sourceQueryable = adventureWorks.Products;
9
        IQueryable<Product> whereQueryable = sourceQueryable.Where(product => product.Name.Length > 10);
10
        IQueryable<string> selectQueryable = whereQueryable.Select(product => product.Name); // Define query.
11
        foreach (string result in selectQueryable) // Execute query.
12
        {
13
            result.WriteLine();
14
        }
15
    }
16
}

The above example filters the products with Name longer than 10 characters, and queries the products’ Names. By desugaring the lambda expressions, and unwrapping the query methods, the above LINQ to Entities query is equivalent to:

1
internal static void WhereAndSelectLinqExpressions(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> sourceQueryable = adventureWorks.Products; // DbSet<Product>.
4
    ConstantExpression sourceConstantExpression = (ConstantExpression)sourceQueryable.Expression;
5
    IQueryProvider sourceQueryProvider = sourceQueryable.Provider; // EntityQueryProvider.
6

7
    // Expression<Func<Product, bool>> predicateExpression = product => product.Name.Length > 10;
8
    ParameterExpression productParameterExpression = Expression.Parameter(typeof(Product), "product");
9
    Expression<Func<Product, bool>> predicateExpression = Expression.Lambda<Func<Product, bool>>(
10
        body: Expression.GreaterThan(
11
            left: Expression.Property(
12
                expression: Expression.Property(
13
                    expression: productParameterExpression, propertyName: nameof(Product.Name)),
14
                propertyName: nameof(string.Length)),
15
            right: Expression.Constant(10)),
16
        parameters: productParameterExpression);
17

18
    // IQueryable<Product> whereQueryable = sourceQueryable.Where(predicateExpression);
19
    Func<IQueryable<Product>, Expression<Func<Product, bool>>, IQueryable<Product>> whereMethod =
20
        Queryable.Where;
21
    MethodCallExpression whereCallExpression = Expression.Call(
22
        method: whereMethod.Method,
23
        arg0: sourceConstantExpression,
24
        arg1: Expression.Quote(predicateExpression));
25
    IQueryable<Product> whereQueryable = sourceQueryProvider
26
        .CreateQuery<Product>(whereCallExpression); // EntityQueryable<Product>.
27
    IQueryProvider whereQueryProvider = whereQueryable.Provider; // EntityQueryProvider.
28

29
    // Expression<Func<Product, string>> selectorExpression = product => product.Name;
30
    Expression<Func<Product, string>> selectorExpression = Expression.Lambda<Func<Product, string>>(
31
        body: Expression.Property(productParameterExpression, nameof(Product.Name)),
32
        parameters: productParameterExpression);
33

34
    // IQueryable<string> selectQueryable = whereQueryable.Select(selectorExpression);
35
    Func<IQueryable<Product>, Expression<Func<Product, string>>, IQueryable<string>> selectMethod =
36
        Queryable.Select;
37
    MethodCallExpression selectCallExpression = Expression.Call(
38
        method: selectMethod.Method,
39
        arg0: whereCallExpression,
40
        arg1: Expression.Quote(selectorExpression));
41
    IQueryable<string> selectQueryable = whereQueryProvider
42
        .CreateQuery<string>(selectCallExpression); // EntityQueryable<Product>/DbQuery<Product>.
43

44
    using (IEnumerator<string> iterator = selectQueryable.GetEnumerator()) // Execute query.
45
    {
46
        while (iterator.MoveNext())
47
        {
48
            iterator.Current.WriteLine();
49
        }
50
    }
51
}

Here are the steps how the fluent query builds its query expression tree:

Build data source:
The initial source IQueryable is a DbSet instance automatically created by EF/Core. It wraps:
A ConstantExpression expression representing the data source.
A query provider that implements IQueryProvider. In EF Core it is an automatically created EntityQueryProvider instance, and in EF it is DbQueryProvider.
Build Where query:
A predicate expression is built for Where,
Where accepts the IQueryable source. But actually Where only needs the source’s expression and query provider. A MethodCallExpression expression is built to represent a call of Where itself with 2 arguments, the source and the predicate expression. Then source query provider’s CreateQuery method is called with the MethodCallExpression expression just built, and return an IQueryable query, which wraps:
The MethodCallExpression expression representing current Where call
A query provider, which is the same one from the source.
Build Select query:
A selector expression is built for Select
Select accepts the IQueryable returned by Where as source. Again, Select only needs the expression and query provider from source. A MethodCallExpression expression is built to represent a call to Select itself with 2 arguments, the source and the selector expression. Then source query provider’s CreateQuery method is called with the MethodCallExpression expression just built, and return an IQueryable query, which wraps:
The MethodCallExpression expression representing current Select call
A query provider, which is the same one from the source.

So, the final IQueryable query’s Expression property is the final abstract syntactic tree, which represents the entire LINQ to Entities query logic:

1
MethodCallExpression (NodeType = Call, Type = IQueryable<string>)
2
|_Method = Queryable.Select<Product, string>
3
|_Object = null
4
|_Arguments
5
  |_MethodCallExpression (NodeType = Call, Type = IQueryable<Product>)
6
  | |_Method = Queryable.Where<Product>
7
  | |_Object = null
8
  | |_Arguments
9
  |   |_ConstantExpression (NodeType = Constant, Type = IQueryable<Product>)
10
  |   | |_Value = new EntityQueryable<Product>(adventureWorks.GetService<IAsyncQueryProvider>())
11
  |   |_UnaryExpression (NodeType = Quote, Type = Expression<Func<Product, bool>>)
12
  |     |_Operand
13
  |       |_Expression<Func<Product, bool>> (NodeType = Lambda, Type = Func<Product, bool>)
14
  |         |_Parameters
15
  |         | |_ParameterExpression (NodeType = Parameter, Type = Product)
16
  |         |   |_Name = "product"
17
  |         |_Body
18
  |           |_BinaryExpression (NodeType = GreaterThan, Type = bool)
19
  |             |_Left
20
  |             | |_MemberExpression (NodeType = MemberAccess, Type = int)
21
  |             |   |_Member = "Length"
22
  |             |   |_Expression
23
  |             |     |_MemberExpression (NodeType = MemberAccess, Type = string)
24
  |             |       |_Member = "Name"
25
  |             |       |_Expression
26
  |             |         |_ParameterExpression (NodeType = Parameter, Type = Product)
27
  |             |           |_Name = "product"
28
  |             |_Right
29
  |               |_ConstantExpression (NodeType = Constant, Type = int)
30
  |                 |_Value = 10
31
  |_UnaryExpression (NodeType = Quote, Type = Expression<Func<Product, string>>)
32
    |_Operand
33
      |_Expression<Func<Product, string>> (NodeType = Lambda, Type = Func<Product, string>)
34
        |_Parameters
35
        | |_ParameterExpression (NodeType = Parameter, Type = Product)
36
        |   |_Name = "product"
37
        |_Body
38
          |_MemberExpression (NodeType = MemberAccess, Type = string)
39
            |_Member = "Name"
40
            |_Expression
41
              |_ParameterExpression (NodeType = Parameter, Type = Product)
42
                |_Name = "product"

In FE, the difference is, the original IQueryable data source wraps a MethodCallExpression expression, which represents an ObjectQuery instance’s MergeAs instance method call with 1 argument, the MergeOption.AppendOnly enumeration. It means append new entities to the entity cache if any entity is constructed by the query. Entity cache will be discussed in a later part.

This also demonstrates that lambda expression, extension methods, and LINQ query expression are powerful language features of C#. Such a rich abstract syntactic tree can be built by C# code as simple as:

1
internal static void WhereAndSelectQuery(AdventureWorks adventureWorks)
2
{
3
    IQueryable<string> products = adventureWorks.Products
4
        .Where(product => product.Name.Length > 10)
5
        .Select(product => product.Name);
6
    // Equivalent to:
7
    // IQueryable<string> products =
8
    //    from product in adventureWorks.Products
9
    //    where product.Name.Length > 10
10
    //    select product.Name;
11
}

The other kind of query returning a single value works in the similar way. Take above First as example:

1
internal static void SelectAndFirst(AdventureWorks adventureWorks)
2
{
3
    // string first = adventureWorks.Products.Select(product => product.Name).First();
4
    IQueryable<Product> sourceQueryable = adventureWorks.Products;
5
    IQueryable<string> selectQueryable = sourceQueryable.Select(product => product.Name);
6
    string first = selectQueryable.First().WriteLine(); // Execute query.
7
}

Here the initial source and and Select query are the same as the previous example. So this time, just unwrap the First method. The above First query is equivalent to:

1
internal static void SelectAndFirstLinqExpressions(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> sourceQueryable = adventureWorks.Products;
4

5
    IQueryable<string> selectQueryable = sourceQueryable.Select(product => product.Name);
6
    MethodCallExpression selectCallExpression = (MethodCallExpression)selectQueryable.Expression;
7
    IQueryProvider selectQueryProvider = selectQueryable.Provider; // DbQueryProvider.
8

9
    // string first = selectQueryable.First();
10
    Func<IQueryable<string>, string> firstMethod = Queryable.First;
11
    MethodCallExpression firstCallExpression = Expression.Call(
12
        method: firstMethod.Method, arg0: selectCallExpression);
13

14
    string first = selectQueryProvider.Execute<string>(firstCallExpression).WriteLine(); // Execute query.
15
}

In First query, the MethodCallExpression expression is built in the same way to represent current First call. The difference is, query provider’s Execute method is called instead of CreateQuery, so that a query result is returned instead of a query.

Similarly, the last expression tree built inside First, is the final abstract syntactic tree, which represents the entire LINQ to Entities query logic:

1
MethodCallExpression (NodeType = Call, Type = string)
2
|_Method = Queryable.First<string>
3
|_Object = null
4
|_Arguments
5
  |_MethodCallExpression (NodeType = Call, Type = IQueryable<string>)
6
    |_Method = Queryable.Select<Product, string>
7
    |_Object = null
8
    |_Arguments
9
      |_ConstantExpression (NodeType = Constant, Type = IQueryable<Product>)
10
      | |_Value = new EntityQueryable<Product>(adventureWorks.GetService<IAsyncQueryProvider>())
11
      |_UnaryExpression (NodeType = Quote, Type = Expression<Func<Product, string>>)
12
       |_Operand
13
          |_Expression<Func<Product, string>> (NodeType = Lambda, Type = Func<Product, string>)
14
            |_Parameters
15
            | |_ParameterExpression (NodeType = Parameter, Type = Product)
16
            |   |_Name = "product"
17
            |_Body
18
              |_MemberExpression (NodeType = MemberAccess, Type = string)
19
                |_Member = "Name"
20
                |_Expression
21
                  |_ParameterExpression (NodeType = Parameter, Type = Product)
22
                    |_Name = "product"

Again, in FE, the original IQueryable data source wraps a MethodCallExpression expression, instead of ConstantExpression.

And again, the entire abstract syntactic tree can be built by C# code as simple as:

1
internal static void SelectAndFirstQuery(AdventureWorks adventureWorks)
2
{
3
    string first = adventureWorks.Products.Select(product => product.Name).First();
4
    // Equivalent to:
5
    // string first = (from product in adventureWorks.Products select product.Name).First();
6
}

.NET expression tree to database expression tree#

When LINQ to Entities queries are executed by either pulling values from IQueryable, or calling IQueryProvider.Execute, EF/Core compiles .NET expression tree to database expression tree.

Database query abstract syntax tree#

The logic of LINQ to Entities can be represented by .NET expression tree, and EF/Core also use expression tree to represent the database query logic. For example, EF Core base libraries provides the Microsoft.EntityFrameworkCore.Query.Expressions.SelectExpression represents a database SELECT query:

1
namespace Microsoft.EntityFrameworkCore.Query.Expressions
2
{
3
    public class SelectExpression : TableExpressionBase
4
    {
5
        public virtual IReadOnlyList<Expression> Projection { get; } // SELECT.
6

7
        public virtual bool IsDistinct { get; set; } // DISTINCT.
8

9
        public virtual Expression Limit { get; set; } // TOP.
10

11
        public virtual IReadOnlyList<TableExpressionBase> Tables { get; } // FROM.
12

13
        public virtual Expression Predicate { get; set; } // WHERE.
14

15
        public virtual IReadOnlyList<Ordering> OrderBy { get; } // ORDER BY.
16

17
        public virtual Expression Offset { get; set; } // OFFSET.
18

19
        public override Type Type { get; }
20

21
        // Other members.
22
    }
23
}

Here are all the database expressions provided by EF Core, and the Remotion.Linq library used by EF Core:

Expression

AggregateExpression
MaxExpression
MinExpression
SumExpression
AliasExpression
ColumnExpression
CountExpression
DatePartExpression
DiscriminatorPredicateExpression
ExistsExpression
ExplicitCastExpression
InExpression
IsNullExpression
LikeExpression
NotNullableExpression
NullConditionalExpression
PartialEvaluationExceptionExpression
PropertyParameterExpression
QuerySourceReferenceExpression
RowNumberExpression
SqlFunctionExpression
StringCompareExpression
SubQueryExpression
TableExpressionBase
CrossJoinExpression
FromSqlExpression
JoinExpressionBase
InnerJoinExpression
LeftOuterJoinExpression
LateralJoinExpression
SelectExpression
TableExpression
VBStringComparisonExpression

EF provides database command tree, where each node derives from System.Data.Entity.Core.Common.CommandTrees.DbExpression:

DbExpression

DbApplyExpression

DbArithmeticExpression

DbBinaryExpression

DbAndExpression

DbComparisonExpression

DbExceptExpression

DbIntersectExpression

DbOrExpression

DbUnionAllExpression

DbCaseExpression

DbConstantExpression

DbCrossJoinExpression

DbFilterExpression

DbFunctionExpression

DbGroupByExpression

DbInExpression

DbJoinExpression

DbLambdaExpression

DbLikeExpression

DbLimitExpression

DbNewInstanceExpression

DbNullExpression

DbParameterReferenceExpression

DbProjectExpression

DbPropertyExpression

DbQuantifierExpression

DbRelationshipNavigationExpression

DbScanExpression

DbSkipExpression

DbSortExpression

DbUnaryExpression

DbCastExpression

DbDerefExpression

DbDistinctExpression

DbElementExpression

DbEntityRefExpression

DbIsEmptyExpression

DbIsNullExpression

DbIsOfExpression

DbNotExpression

DbOfTypeExpression

DbRefExpression

DbTreatExpression

DbRefKeyExpression

DbVariableReferenceExpression

When representing a complete database query, command tree’s top node is a DbQueryCommandTree instance:
1
namespace System.Data.Entity.Core.Common.CommandTrees
2
{
3
    public abstract class DbCommandTree
4
    {
5
        public IEnumerable<KeyValuePair<string, TypeUsage>> Parameters { get; }
6

7
        // Other members.
8
    }
9

10
    public sealed class DbQueryCommandTree : DbCommandTree
11
    {
12
        public DbExpression Query { get; }
13

14
        // Other members.
15
    }
16
}
DbQueryCommandTree’s Parameters property contains the parameters for the database query, and Query property is the top node of the DbExpression tree. They are similar to LambdaExpression’s Parameters and Body properties.

Compile LINQ expressions to database expressions#

EF Core calls the third party library Remotion.Linq to compile LINQ expression tree to a query model, then EF Core compiles the query model to database expression tree, which is a SelectExpression instance. The following Compile method demonstrates how the compilation can be done. It accepts a LINQ expression tree, and returns a tuple of SelectExpression and its parameters, if any:

1
public static partial class DbContextExtensions
2
{
3
    public static (SelectExpression, IReadOnlyDictionary<string, object>) Compile(
4
        this DbContext dbContext, Expression linqExpression)
5
    {
6
        QueryContext queryContext = dbContext.GetService<IQueryContextFactory>().Create();
7
        IEvaluatableExpressionFilter evaluatableExpressionFilter = dbContext.GetService<IEvaluatableExpressionFilter>();
8
        linqExpression = new ParameterExtractingExpressionVisitor(
9
            evaluatableExpressionFilter: evaluatableExpressionFilter,
10
            parameterValues: queryContext,
11
            logger: dbContext.GetService<IDiagnosticsLogger<DbLoggerCategory.Query>>(),
12
            parameterize: true).ExtractParameters(linqExpression);
13
        QueryParser queryParser = new QueryParser(new ExpressionTreeParser(
14
            nodeTypeProvider: dbContext.GetService<INodeTypeProviderFactory>().Create(),
15
            processor: new CompoundExpressionTreeProcessor(new IExpressionTreeProcessor[]
16
            {
17
                new PartialEvaluatingExpressionTreeProcessor(evaluatableExpressionFilter),
18
                new TransformingExpressionTreeProcessor(ExpressionTransformerRegistry.CreateDefault())
19
            })));
20
        QueryModel queryModel = queryParser.GetParsedQuery(linqExpression);
21

22
        Type resultType = queryModel.GetResultType();
23
        if (resultType.IsConstructedGenericType && resultType.GetGenericTypeDefinition() == typeof(IQueryable<>))
24
        {
25
            resultType = resultType.GenericTypeArguments.Single();
26
        }
27

28
        QueryCompilationContext compilationContext = dbContext.GetService<IQueryCompilationContextFactory>()
29
            .Create(async: false);
30
        RelationalQueryModelVisitor queryModelVisitor = (RelationalQueryModelVisitor)compilationContext
31
            .CreateQueryModelVisitor();
32
        queryModelVisitor.GetType()
33
            .GetMethod(nameof(RelationalQueryModelVisitor.CreateQueryExecutor))
34
            .MakeGenericMethod(resultType)
35
            .Invoke(queryModelVisitor, new object[] { queryModel });
36
        SelectExpression databaseExpression = queryModelVisitor.TryGetQuery(queryModel.MainFromClause);
37
        databaseExpression.QuerySource = queryModel.MainFromClause;
38
        return (databaseExpression, queryContext.ParameterValues);
39
    }
40
}

EF calls ExpressionConverter, PlanCompiler and other components to convert expression tree to database command tree. These APIs are not public.

So above Where and Select query’s expression tree can be converted as:

1
internal static void CompileWhereAndSelectExpressions(AdventureWorks adventureWorks)
2
{
3
    Expression linqExpression =adventureWorks.Products
4
        .Where(product => product.Name.Length > 10)
5
        .Select(product => product.Name).Expression;
6
    (SelectExpression DatabaseExpression, IReadOnlyDictionary<string, object> Parameters) compilation =
7
        adventureWorks.Compile(linqExpression);
8
    compilation.DatabaseExpression.WriteLine();
9
    compilation.Parameters.WriteLines(parameter => $"{parameter.Key}: {parameter.Value}");
10
}

The compiled SelectExpression is the same as the following SelectExpression built on the fly:

1
internal static SelectExpression WhereAndSelectDatabaseExpressions(AdventureWorks adventureWorks)
2
{
3
    QueryCompilationContext compilationContext = adventureWorks.GetService<IQueryCompilationContextFactory>()
4
        .Create(async: false);
5
    SelectExpression databaseExpression = new SelectExpression(
6
        dependencies: new SelectExpressionDependencies(adventureWorks.GetService<IQuerySqlGeneratorFactory>()),
7
        queryCompilationContext: (RelationalQueryCompilationContext)compilationContext);
8
    MainFromClause querySource = new MainFromClause(
9
        itemName: "product",
10
        itemType: typeof(Product),
11
        fromExpression: Expression.Constant(adventureWorks.ProductCategories));
12
    TableExpression tableExpression = new TableExpression(
13
        table: nameof(Product),
14
        schema: AdventureWorks.Production,
15
        alias: querySource.ItemName,
16
        querySource: querySource);
17
    databaseExpression.AddTable(tableExpression);
18
    IEntityType productEntityType = adventureWorks.Model.FindEntityType(typeof(Product));
19
    IProperty nameProperty = productEntityType.FindProperty(nameof(Product.Name));
20
    ColumnExpression nameColumn = new ColumnExpression(
21
        name: nameof(Product.Name), property: nameProperty, tableExpression: tableExpression);
22
    databaseExpression.AddToProjection(nameColumn);
23
    databaseExpression.AddToPredicate(Expression.GreaterThan(
24
        left: new ExplicitCastExpression(
25
            operand: new SqlFunctionExpression(
26
                functionName: "LEN",
27
                returnType: typeof(int),
28
                arguments: new Expression[] { nameColumn }),
29
            type: typeof(int)),
30
        right: Expression.Constant(10)));
31
    return databaseExpression.WriteLine();
32
}

This compiled abstract syntactic tree can be visualized as:

1
SelectExpression (NodeType = Extension, Type = string)
2
|_Porjection
3
| |_ColumnExpression (NodeType = Extension, Type = string)
4
|   |_Name = "Name"
5
|   |_Property = Product.Name
6
|   |_Table
7
|     |_TableExpression (NodeType = Extension, Type = object)
8
|     |_Schema = "Production"
9
|     |_Name = "Product"
10
|     |_Alias = "product"
11
|_Tables
12
| |_TableExpression (NodeType = Extension, Type = object)
13
|   |_Schema = "Production"
14
|   |_Name = "Product"
15
|   |_Alias = "product"
16
|_Predicate
17
  |_BinaryExpression (NodeType = GreaterThan, Type = bool)
18
  |_left
19
  | |_ExplicitCastExpression (NodeType = Extension, Type = int)
20
  |   |_Operand
21
  |     |_SqlFunctionExpression (NodeType = Extension, Type = int)
22
  |       |_FunctionName = "LEN"
23
  |       |_Arguments
24
  |         |_ColumnExpression (NodeType = Extension, Type = string)
25
  |           |_Name = "Name"
26
  |           |_Property = Product.Name
27
  |           |_Table
28
  |             |_TableExpression (NodeType = Extension, Type = object)
29
  |               |_Schema = "Production"
30
  |               |_Name = "Product"
31
  |               |_Alias = "product"
32
  |_Right
33
    |_ConstantExpression (NodeType = Constant, Type = int)
34
    |_Value = 1

In EF, the compiled command tree above is equivalent to the command tree built below:

1
internal static DbQueryCommandTree WhereAndSelectDatabaseExpressions(AdventureWorks adventureWorks)
2
{
3
    MetadataWorkspace metadata = ((IObjectContextAdapter)adventureWorks).ObjectContext.MetadataWorkspace;
4
    TypeUsage stringTypeUsage = TypeUsage.CreateDefaultTypeUsage(metadata
5
        .GetPrimitiveTypes(DataSpace.CSpace)
6
        .Single(type => type.ClrEquivalentType == typeof(string)));
7
    TypeUsage nameRowTypeUsage = TypeUsage.CreateDefaultTypeUsage(RowType.Create(
8
        EnumerableEx.Return(EdmProperty.Create(nameof(Product.Name), stringTypeUsage)),
9
        Enumerable.Empty<MetadataProperty>()));
10
    TypeUsage productTypeUsage = TypeUsage.CreateDefaultTypeUsage(metadata
11
        .GetType(nameof(Product), "CodeFirstDatabaseSchema", DataSpace.SSpace));
12
    EntitySet productEntitySet = metadata
13
        .GetEntityContainer("CodeFirstDatabase", DataSpace.SSpace)
14
        .GetEntitySetByName(nameof(Product), false);
15

16
    DbProjectExpression query = DbExpressionBuilder.Project(
17
        DbExpressionBuilder.BindAs(
18
            DbExpressionBuilder.Filter(
19
                DbExpressionBuilder.BindAs(
20
                    DbExpressionBuilder.Scan(productEntitySet), "Extent1"),
21
                DbExpressionBuilder.GreaterThan(
22
                    DbExpressionBuilder.Invoke(
23
                        ((IObjectCOntextAdapter)adventureWorks).ObjectContext.MetadataWorkspace
24
                            .GetFunctions("LEN", "SqlServer", DataSpace.SSpace).First(),
25
                        DbExpressionBuilder.Property(
26
                            DbExpressionBuilder.Variable(productTypeUsage, "Extent1"), nameof(Product.Name))),
27
                    DbExpressionBuilder.Constant(10))),
28
            "Filter1"),
29
        DbExpressionBuilder.New(
30
            nameRowTypeUsage,
31
            DbExpressionBuilder.Property(
32
                DbExpressionBuilder.Variable(productTypeUsage, "Filter1"), nameof(Product.Name))));
33
    DbQueryCommandTree result = new DbQueryCommandTree(metadata, DataSpace.SSpace, query);
34
    return result.WriteLine();
35
}

This abstract syntactic tree can be visualized as:

1
DbQueryCommandTree
2
|_Parameters
3
|_Query
4
  |_DbProjectExpression (ExpressionKind = Project, ResultType = Collection(Row['Name' = Edm.String]))
5
    |_Input
6
    | |_DbExpressionBinding (VariableType = Product)
7
    |   |_VariableName = 'Filter1'
8
    |   |_Expression
9
    |     |_DbFilterExpression (ExpressionKind = Filter, ResultType = Product)
10
    |       |_Input
11
    |       | |_DbExpressionBinding (VariableType = Product)
12
    |       |   |_VariableName = 'Extent1'
13
    |       |   |_Expression
14
    |       |     |_DbScanExpression (ExpressionKind = Scan, ResultType = Collection(Product))
15
    |       |       |_Target = Products
16
    |       |_Predicate
17
    |         |_DbComparisonExpression (ExpressionKind = GreaterThan, ResultType = Edm.Boolean)
18
    |           |_Left
19
    |           | |_DbFunctionExpression (ExpressionKind = Function, ResultType = Edm.Int32)
20
    |           |   |_Function = Edm.Length
21
    |           |   |_Arguments
22
    |           |     |_DbPropertyExpression (ExpressionKind = Property, ResultType = Edm.String)
23
    |           |       |_Property = ‘Name’
24
    |           |       |_Instance
25
    |           |         |_DbVariableReferenceExpression (ExpressionKind = VariableReference, ResultType = Product)
26
    |           |           |_VariableName = 'Extent1'
27
    |           |_Right
28
    |             |_DbConstantExpression (ExpressionKind = Constant, ResultType = Edm.Int32)
29
    |               |_Value = 10
30
    |_Projection
31
      |_DbNewInstanceExpression (ExpressionKind = NewInstance, ResultType = Row['Name' = Edm.String])
32
        |_Arguments
33
          |_DbPropertyExpression (ExpressionKind = Property, ResultType = Edm.String)
34
            |_Property = "Name"
35
            |_Instance
36
              |_DbVariableReferenceExpression (ExpressionKind = VariableReference, ResultType = Product)
37
                |_VariableName = 'Filter1'

Similarly, the other Select and First query’s expression tree is compiled to abstract syntax tree the same as the following:

1
internal static SelectExpression SelectAndFirstDatabaseExpressions(AdventureWorks adventureWorks)
2
{
3
    QueryCompilationContext compilationContext = adventureWorks.GetService<IQueryCompilationContextFactory>()
4
        .Create(async: false);
5
    SelectExpression selectExpression = new SelectExpression(
6
        dependencies: new SelectExpressionDependencies(adventureWorks.GetService<IQuerySqlGeneratorFactory>()),
7
        queryCompilationContext: (RelationalQueryCompilationContext)compilationContext);
8
    MainFromClause querySource = new MainFromClause(
9
        itemName: "product",
10
        itemType: typeof(Product),
11
        fromExpression: Expression.Constant(adventureWorks.ProductCategories));
12
    TableExpression tableExpression = new TableExpression(
13
        table: nameof(Product),
14
        schema: AdventureWorks.Production,
15
        alias: querySource.ItemName,
16
        querySource: querySource);
17
    selectExpression.AddTable(tableExpression);
18
    IEntityType productEntityType = adventureWorks.Model.FindEntityType(typeof(Product));
19
    IProperty nameProperty = productEntityType.FindProperty(nameof(Product.Name));
20
    selectExpression.AddToProjection(new ColumnExpression(
21
        name: nameof(Product.Name), property: nameProperty, tableExpression: tableExpression));
22
    selectExpression.Limit = Expression.Constant(1);
23
    return selectExpression.WriteLine();
24
}

And this abstract syntactic tree can be visualized as:

1
SelectExpression (NodeType = Extension, Type = string)
2
|_Limit
3
| |_ConstantExpression (NodeType = Constant, Type = int)
4
|   |_Value = 1
5
|_Porjection
6
|   |_ColumnExpression (NodeType = Extension, Type = string)
7
|   |_Name = "Name"
8
|   |_Property = Product.Name
9
|   |_Table
10
|     |_TableExpression (NodeType = Extension, Type = object)
11
|     |_Schema = "Production"
12
|     |_Name = "Product"
13
|     |_Alias = "product"
14
|_Tables
15
  |_TableExpression (NodeType = Extension, Type = object)
16
    |_Schema = "Production"
17
    |_Name = "Product"
18
    |_Alias = "product"

In EF, the compiled command tree above is equivalent to the command tree built below:

1
internal static DbQueryCommandTree SelectAndFirstDatabaseExpressions(AdventureWorks adventureWorks)
2
{
3
    MetadataWorkspace metadata = ((IObjectContextAdapter)adventureWorks).ObjectContext.MetadataWorkspace;
4
    TypeUsage stringTypeUsage = TypeUsage.CreateDefaultTypeUsage(metadata
5
        .GetPrimitiveTypes(DataSpace.CSpace)
6
        .Single(type => type.ClrEquivalentType == typeof(string)));
7
    TypeUsage nameRowTypeUsage = TypeUsage.CreateDefaultTypeUsage(RowType.Create(
8
        EnumerableEx.Return(EdmProperty.Create(nameof(Product.Name), stringTypeUsage)),
9
        Enumerable.Empty<MetadataProperty>()));
10
    TypeUsage productTypeUsage = TypeUsage.CreateDefaultTypeUsage(metadata
11
        .GetType(nameof(Product), "CodeFirstDatabaseSchema", DataSpace.SSpace));
12
    EntitySet productEntitySet = metadata
13
        .GetEntityContainer("CodeFirstDatabase", DataSpace.SSpace)
14
        .GetEntitySetByName(nameof(Product), false);
15

16
    DbProjectExpression query = DbExpressionBuilder.Project(
17
        DbExpressionBuilder.BindAs(
18
            DbExpressionBuilder.Limit(
19
                DbExpressionBuilder.Scan(productEntitySet),
20
                DbExpressionBuilder.Constant(1)),
21
            "Limit1"),
22
        DbExpressionBuilder.New(
23
            nameRowTypeUsage,
24
            DbExpressionBuilder.Property(
25
                DbExpressionBuilder.Variable(productTypeUsage, "Limit1"), nameof(Product.Name))));
26
    DbQueryCommandTree commandTree = new DbQueryCommandTree(metadata, DataSpace.SSpace, query);
27
    return commandTree.WriteLine();
28
}

And this abstract syntactic tree can be visualized as:

1
DbQueryCommandTree
2
|_Parameters
3
|_Query
4
  |_DbProjectExpression (ExpressionKind = Project, ResultType = Collection(Row['Name' = Edm.String]))
5
    |_Input
6
    | |_DbExpressionBinding (VariableType = Product)
7
    |   |_VariableName = 'Limit1'
8
    |   |_Expression
9
    |     |_DbLimitExpression (ExpressionKind = Limit, ResultType = Collection(Product))
10
    |       |_Argument
11
    |       | |_DbScanExpression (ExpressionKind = Scan, ResultType = Collection(Product))
12
    |       |   |_Target = Products
13
    |       |_Limit
14
    |         |_DbConstantExpression (ExpressionKind = Constant, ResultType = Edm.Int32)
15
    |           |_Value = 1
16
    |_Projection
17
      |_DbNewInstanceExpression (ExpressionKind = NewInstance, ResultType = Row['Name' = Edm.String])
18
        |_Arguments
19
          |_DbPropertyExpression (ExpressionKind = Property, ResultType = Edm.String)
20
            |_Property = ‘Name’
21
            |_Instance
22
              |_DbVariableReferenceExpression (ExpressionKind = VariableReference, ResultType = Product)
23
                |_VariableName = 'Limit1'

Compile LINQ query method calls#

EF Core first calls Remotion.Linq library to compile LINQ query method call nodes to QueryModel. Under Remotion.Linq.Parsing.Structure.IntermediateModel namespace, Remotion.Linq provides IExpressionNode interface, and many types implementing that interface, where each type can process a certain kind of query method call, for example:

MethodCallExpression node representing Queryable.Where call is processed by WhereExpressionNode, and converted to Remotion.Linq.Clauses.WhereClause, which is a part of QueryModel
MethodCallExpression node representing Queryable.Select call is processed by SelectExpressionNode, and converted to Remotion.Linq.Clauses.SelectClause, which is a part of QueryModel
MethodCallExpression node representing Queryable.First or Queryable.FirstOrDefault call is processed by FirstExpressionNode, and converted to Remotion.Linq.Clauses.ResultOperators.FirstResultOperator, which is a part of QueryModel

etc. Then EF Core continues to compile QueryModel to SelectExpression. For example:

WhereClause is converted to predicate child nodes of the SelectExpression
SelectClause is converted to projection child nodes of the SelectExpression
FirstResultOperator is converted to limit child node of the SelectExpression

etc.

In EF, the fore mentioned ExpressionConverter is a huge type. It has tons of nested translator types for all supported expression tree nodes. For example

WhereTranslator compiles Queryable.Where node to FilterDbExpression node

SelectTranslator compiles Queryable.Select node to ProjectDbExpression node

FirstTranslator compiles Queryable.First or Queryable.FirstOrDefault to LimitDbExpression node

etc.

Compile .NET API calls#

The above Where query’s predicate has a logic to call string.Length and compare the result to a constant. EF Core provides translator types under Microsoft.EntityFrameworkCore.Query.ExpressionTranslators.Internal namespace to translate these .NET API calls. Here MemberExpression node representing string.Length call is processed by SqlServerStringLengthTranslator, and converted to a SqlFunctionExpression node representing SQL database function LEN call:

1
namespace Microsoft.EntityFrameworkCore.Query.ExpressionTranslators.Internal
2
{
3
    public class SqlServerStringLengthTranslator : IMemberTranslator
4
    {
5
        public virtual Expression Translate(MemberExpression memberExpression) =>
6
            memberExpression.Expression != null
7
            && memberExpression.Expression.Type == typeof(string)
8
            && memberExpression.Member.Name == nameof(string.Length)
9
                ? new SqlFunctionExpression("LEN", memberExpression.Type, new Expression[] { memberExpression.Expression })
10
                : null;
11
    }
12
}

There are many other translators to cover other basic .NET APIs of System.String, System.Enum, System.DateTime, System.Guid, System.Math, for example:

MethodCallExpression node representing string.Contains call (e.g. product.Name.Contains(“M”)) is processed by SqlServerContainsOptimizedTranslator, and converted to a BinaryExpression node representing SQL database int comparison, where the left child node is a SqlFunctionExpression node representing SQL database function CHARINDEX call, and the right child node is a ConstantExpression node representing 0 (e.g. CHARINDEX(N’M’, product.Name) > 0)
MethodCallExpression node representing Math.Ceiling call is processed by SqlServerMathCeilingTranslator, and converted to SqlFunctionExpression node representing SQL database function CEILING call
MemberExpression node representing DateTime.Now or DateTime.UtcNow property access, is processed by SqlServerDateTimeNowTranslator, and converted to SqlFunctionExpression node representing SQL database function GETDATE or GETUTCDATE call

etc.

There are also a few other APIs covered with other EF Core components. For example, In Remotion.Linq, MethodCallExpression node representing Enumerable.Contains or List.Contains call is converted to to Remotion.Linq.Clauses.ResultOperators.ContainsResultOperator. Then in EF Core, ContainsResultOperator is processed by Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor. and converted to InExpression node representing SQL database IN operation.

As fore mentioned, EF provides nested translator types inside ExpressionConverter.There are also many other translators covering .NET APIs of System.String, Microsoft.VisualBasic.Strings, System.Decimal, System.Enum, System.DateTime, System.DateTimeOffset, Microsoft.VisualBasic.DateAndTime, System.Math, System.Guid, System.Nullable, System.Data.Spatial.DbGeography, System.Data.Spatial.DbGeometry, etc.For example,

MethodCallExpression node representing string.Contains call (e.g. product.Name.Contains(“M”)) is processed by StringContainsTranslator, and converted to a DbLikeExpression node representing SQL database LIKE operation (e.g. product.Name LIKE N’%M%’).

MethodCallExpression node representing Math.Ceiling call is processed by CanonicalFunctionDefaultTranslator, and converted to DbFunctionExpression node representing SQL database function CEILING call

MemberExpression node representing DateTime.Now or DateTime.UtcNow property access, is processed by SqlServerDateTimeNowTranslator, and converted to DbFunctionExpression node representing SQL database function SYSDATETIME or SYSUTCDATETIME call

Similar to EF Core, in EF MethodCallExpression node representing Enumerable.Contains or List.Contains call is not processed by translators, but by System.Data.Entity.Core.Objects.ELinq.LinqExpressionNormalizer.

Remote API call vs. local API call#

Apparently EF/Core can only compile the supported .NET API calls, like the above string.Length call. It cannot compile arbitrary API calls. The following example wraps the string.Length call and result comparison with constant into a custom predicate:

1
private static bool FilterName(string name) => name.Length > 10;
2

3
internal static void WhereAndSelectWithCustomPredicate(AdventureWorks adventureWorks)
4
{
5
    IQueryable<Product> source = adventureWorks.Products;
6
    IQueryable<string> products = source
7
        .Where(product => FilterName(product.Name))
8
        .Select(product => product.Name); // Define query.
9
    products.WriteLines(); // Execute query.
10
    // SELECT [product].[Name]
11
    // FROM [Production].[Product] AS [product]
12
}

At compile time, the predicate expression tree has a MethodCallExpression node representing FilterName call, which apparently cannot be compiled to SQL by EF/Core. In this case, EF Core execute FilterName locally.

When EF fails to compile query, it throws exception. So in EF, the above example throws NotSupportedException: LINQ to Entities does not recognize the method ‘Boolean FilterName(System.String)’ method, and this method cannot be translated into a store expression. To make it work, the Where query has to be manually specified as local LINQ to Objects query:
1
internal static void WhereAndSelectWithLocalPredicate(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IEnumerable<string> products = source
5
        .Select(product => product.Name) // LINQ to Entities.
6
        .AsEnumerable() // LINQ to Objects.
7
        .Where(name => FilterName(name)); // Define query, IEnumerable<string> instead of IQueryable<string>.
8
    products.WriteLines(); // Execute query.
9
}

Compile database function call#

EF Core does not support database function call.

Not all database APIs has .NET built-in APIs to translated from, for example, there is no mapping .NET API for SQL database DATEDIFF function. EF provides mapping methods to address these scenarios. As fore mentioned, EF implements a provider model, and these mapping methods are provides in 2 levels too:

In EntityFramework.dll, System.Data.Entity.DbFunctions provides mapping methods supported by all database providers, like DbFunctions.Reverse to reverse a string, DbFunction.AsUnicode to ensure a string is treated as Unicode, etc. These common database functions are also called canonical functions.

In EntityFramework.SqlServer.dll, System.Data.Entity.SqlServer.SqlFunctions provides mapping methods from SQL database functions, like SqlFunctions.Checksum method for CHECKSUM function, SqlFunctions.CurrentUser for CURRENT_USER function, etc.

The following LINQ to Entities query calculates the number of days between current time and photo’s last modified time. In the following LINQ to Entities query expression tree, the MethodCallExpression node representing DbFunctions.DiffDays call can be compiled by EF, and is converted to a DbFunctionExpression node representing canonical function Edm.DiffDays call:
1
internal static void DbFunction(AdventureWorks adventureWorks)
2
{
3
    var photos = adventureWorks.ProductPhotos.Select(photo => new
4
    {
5
        LargePhotoFileName = photo.LargePhotoFileName,
6
        UnmodifiedDays = DbFunctions.DiffDays(photo.ModifiedDate, DateTime.UtcNow)
7
    });
8
    adventureWorks.Compile(photos.Expression).WriteLine();
9
    photos.WriteLines();
10
    // SELECT
11
    //    1 AS [C1],
12
    //    [Extent1].[LargePhotoFileName] AS [LargePhotoFileName],
13
    //    DATEDIFF (day, [Extent1].[ModifiedDate], SysUtcDateTime()) AS [C2]
14
    //    FROM [Production].[ProductPhoto] AS [Extent1]
15
}
The following example filters the product’s names with a pattern. The SqlFunction.PatIndex call is compiled by EF, and converted to SQL database function SqlServer.PATINDEX call:
1
internal static void SqlFunction(AdventureWorks adventureWorks)
2
{
3
    IQueryable<string> products = adventureWorks.Products
4
        .Select(product => product.Name)
5
        .Where(name => SqlFunctions.PatIndex(name, "%Touring%50%") > 0); // Define query.
6
    products.WriteLines(); // Execute query.
7
    // SELECT
8
    //    [Extent1].[Name] AS [Name]
9
    //    FROM [Production].[Product] AS [Extent1]
10
    //    WHERE ( CAST(PATINDEX([Extent1].[Name], N'%Touring%50%') AS int)) > 0
11
}

Database expression tree to SQL#

SQL generator and SQL command#

The SQL database provider of EF/Core provides a SQL generator to traverse the compiled database query abstract syntactic tree, and generate SQL database specific remote SQL query. EF Core provides SQL generator as Microsoft.EntityFrameworkCore.Query.Sql.IQuerySqlGenerator interface:

1
namespace Microsoft.EntityFrameworkCore.Query.Sql
2
{
3
    public interface IQuerySqlGenerator
4
    {
5
        IRelationalCommand GenerateSql(IReadOnlyDictionary<string, object> parameterValues);
6

7
        // Other members.
8
    }
9
}

It is implemented by Microsoft.EntityFrameworkCore.Query.Sql.Internal.SqlServerQuerySqlGenerator. SQL generator wraps a database expression tree inside, and provides a GenerateSql method, which returns Microsoft.EntityFrameworkCore.Storage.IRelationalCommand to represents generated SQL:

1
namespace Microsoft.EntityFrameworkCore.Storage
2
{
3
    public interface IRelationalCommand
4
    {
5
        string CommandText { get; }
6

7
        IReadOnlyList<IRelationalParameter> Parameters { get; }
8

9
        RelationalDataReader ExecuteReader(
10
            IRelationalConnection connection, IReadOnlyDictionary<string, object> parameterValues);
11

12
        // Other members.
13
    }
14
}

It is generated by Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand in Microsoft.EntityFrameworkCore.Relational package.

Similarly, EF also provides SQL generator with a GenerateSql method:

1
namespace System.Data.Entity.SqlServer.SqlGen
2
{
3
    internal class SqlGenerator : DbExpressionVisitor<ISqlFragment>
4
    {
5
        internal string GenerateSql(DbQueryCommandTree tree, out HashSet<string> paramsToForceNonUnicode);
6

7
        // Other members.
8
    }
9
}

And EF represents the generated SQL with System.Data.Common.DbCommand in ADO.NET:

1
namespace System.Data.Common
2
{
3
    public abstract class DbCommand : Component, IDbCommand, IDisposable
4
    {
5
        public abstract string CommandText { get; set; }
6

7
        public DbParameterCollection Parameters { get; }
8

9
        public DbDataReader ExecuteReader();
10

11
        // Other members.
12
    }
13
}

And EF’s SQL database provider uses ADO.NET type System.Data.SqlClient.SqlCommand, which is derived from DbCommand.

Generate SQL from database expression tree#

The following extension method of DbContext can take database command tree, and generate SQL:

1
public static IRelationalCommand Generate(
2
    this DbContext dbContext,
3
    SelectExpression databaseExpression,
4
    IReadOnlyDictionary<string, object> parameters = null)
5
{
6
    IQuerySqlGeneratorFactory sqlGeneratorFactory = dbContext.GetService<IQuerySqlGeneratorFactory>();
7
    IQuerySqlGenerator sqlGenerator = sqlGeneratorFactory.CreateDefault(databaseExpression);
8
    return sqlGenerator.GenerateSql(parameters ?? new Dictionary<string, object>());
9
}

In EF:
1
public static DbCommand Generate(this DbContext context, DbQueryCommandTree commandTree)
2
{
3
    MetadataWorkspace metadataWorkspace = ((IObjectContextAdapter)context).ObjectContext.MetadataWorkspace;
4
    StoreItemCollection itemCollection = (StoreItemCollection)metadataWorkspace
5
        .GetItemCollection(DataSpace.SSpace);
6
    DbCommandDefinition commandDefinition = SqlProviderServices.Instance
7
        .CreateCommandDefinition(itemCollection.ProviderManifest, commandTree);
8
    return commandDefinition.CreateCommand();
9
}
Inside the last DbCommandDefinition.CreateCommand call, a SqlGenerator instance is constructed with SQL database’s version (detected with ADO.NET API SqlConnection.ServerVersion), and its GenerateSql method is called to generate SQL, then the generated SQL and parameters (provided by DbQueryCommandTree.Parameters) are wrapped into a DbCommand instance.

The above WhereAndSelectDatabaseExpressions and SelectAndFirstDatabaseExpressions method builds database expression trees from scratch. Take them as an example to generate SQL:

1
internal static void WhereAndSelectSql(AdventureWorks adventureWorks)
2
{
3
    SelectExpression databaseExpression = WhereAndSelectDatabaseExpressions(adventureWorks);
4
    IRelationalCommand sql = adventureWorks.Generate(databaseExpression: databaseExpression, parameters: null);
5
    sql.CommandText.WriteLine();
6
    // SELECT [product].[Name]
7
    // FROM [Production].[ProductCategory] AS [product]
8
    // WHERE CAST(LEN([product].[Name]) AS int) > 10
9
}
10

11
internal static void SelectAndFirstSql(AdventureWorks adventureWorks)
12
{
13
    SelectExpression databaseExpression = SelectAndFirstDatabaseExpressions(adventureWorks);
14
    IRelationalCommand sql = adventureWorks.Generate(databaseExpression: databaseExpression, parameters: null);
15
    sql.CommandText.WriteLine();
16
    // SELECT TOP(1) [product].[Name]
17
    // FROM [Production].[Product] AS [product]
18
}

SQL generator traverses the command tree nodes, a specific Visit overloads is called for each supported node type. It generates SELECT clause from DbProjectionExpression node, FROM clause from DbScanExpression node, WHERE clause from DbFilterExpression node, LIKE operator from DbLikeExpression, etc.

In EF, SQL can be generated in similar way:

1
internal static void WhereAndSelectSql(AdventureWorks adventureWorks)
2
{
3
    DbQueryCommandTree databaseExpressionAndParameters = WhereAndSelectDatabaseExpressions(adventureWorks);
4
    DbCommand sql = adventureWorks.Generate(databaseExpressionAndParameters);
5
    sql.CommandText.WriteLine();
6
    // SELECT
7
    //    [Extent1].[Name] AS [Name]
8
    //    FROM [Production].[Product] AS [Extent1]
9
    //    WHERE [Extent1].[Name] LIKE N'M%'
10
}
11

12
internal static void SelectAndFirstSql(AdventureWorks adventureWorks)
13
{
14
    DbQueryCommandTree databaseExpressionAndParameters = SelectAndFirstDatabaseExpressions(adventureWorks);
15
    DbCommand sql = adventureWorks.Generate(databaseExpressionAndParameters);
16
    sql.CommandText.WriteLine();
17
    // SELECT TOP (1)
18
    //    [c].[Name] AS [Name]
19
    //    FROM [Production].[Product] AS [c]
20
}

SQL generator generates TOP expression from DbLimitExpression node, which is an example where SQL database’s version matters. Inside the SqlGenerator.Visit overload for DbLimitExpression, TOP 1 is generated for SQL Server 2000 (8.0), and TOP(1) is generated for later version.

So finally LINQ to Entities queries are translated to remote SQL database queries. The next part discusses the query execution and data loading.

[LINQ via C# series]#

[Entity Framework Core series]#

[Entity Framework series]#

Latest EF Core version of this article: https://weblogs.asp.net/dixin/entity-framework-core-and-linq-to-entities-5-query-translation-implementation#

EF version of this article: https://weblogs.asp.net/dixin/entity-framework-and-linq-to-entities-5-query-translation #

Code to LINQ expression tree#

IQueryable and IQueryProvider#

Queryable methods#

Build LINQ to Entities abstract syntax tree#

.NET expression tree to database expression tree#

Database query abstract syntax tree#

Compile LINQ expressions to database expressions#

Compile LINQ query method calls#

Compile .NET API calls#

Remote API call vs. local API call#

Compile database function call#

Database expression tree to SQL#

SQL generator and SQL command#

Generate SQL from database expression tree#

[LINQ via C# series]#

[Entity Framework Core series]#

[Entity Framework series]#

Latest EF Core version of this article: https://weblogs.asp.net/dixin/entity-framework-core-and-linq-to-entities-5-query-translation-implementation#

EF version of this article: https://weblogs.asp.net/dixin/entity-framework-and-linq-to-entities-5-query-translation#

Code to LINQ expression tree#

IQueryable and IQueryProvider#

Queryable methods#

Build LINQ to Entities abstract syntax tree#

.NET expression tree to database expression tree#

Database query abstract syntax tree#

Compile LINQ expressions to database expressions#

Compile LINQ query method calls#

Compile .NET API calls#

Remote API call vs. local API call#

Compile database function call#

Database expression tree to SQL#

SQL generator and SQL command#

Generate SQL from database expression tree#

EF version of this article: https://weblogs.asp.net/dixin/entity-framework-and-linq-to-entities-5-query-translation #