Entity Framework/Core and LINQ to Entities (4) Query Methods (Operators)

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

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

This part discusses how to query SQL database with the defined mapping entities. In EF/Core, LINQ to Entities supports most of the methods provided by Queryable:

Sequence queries: return a new IQueryable source

Filtering (restriction): Where, OfType*
Mapping (projection): Select
Generation: DefaultIfEmpty*
Grouping: GroupBy*
Join: Join, GroupJoin, SelectMany, Select
Concatenation: Concat*
Set: Distinct, GroupBy*, Union*, Intersect*, Except*
Convolution: Zip
Partitioning: Take, Skip, TakeWhile, SkipWhile
Ordering: OrderBy*, ThenBy, OrderByDescending*, ThenByDescending, Reverse
Conversion: Cast, AsQueryable

Value queries: return a single value
- Element: First, FirstOrDefault, Last*, LastOrDefault*, ElementAt, ElementAtOrDefault, Single, SingleOrDefault
- Aggregation: Aggregate, Count, LongCount, Min, Max, Sum, Average*
- Quantifier: All, Any, Contains
- Equality: SequenceEqual

In above list:

The crossed methods are not supported by LINQ to Entities (the list provided by MDSN is not up to date), because they cannot be translated to proper SQL database operations. For example, SQL database has no built-in Zip operation support. Calling these crossed methods throws NotSupportedException at runtime
The underlined methods have some overloads supported by LINQ to Entities, and other overloads not supported:
For GroupBy, Join, GroupJoin, Distinct, Union, Intersect, Except, Contains, the overloads accepting IEqualityComparer parameter are not supported, because apparently IEqualityComparer has no equivalent SQL translation
For OrderBy, ThenBy, OrderByDescending, ThenByDescending, the overloads with IComparer parameter are not supported
For Where, Select, SelectMany, the indexed overloads are not supported
In EF Core, the methods marked with * can execute the query locally in some cases, without being translated to SQL.

EF translate all supported query methods to SQL and executed in database.

For LINQ to Entities, apparently these methods enable fluent method chaining, implement the same LINQ query expression pattern as LINQ to Objects and Parallel LINQ. So in this part, most of the LINQ to Entities queries are demonstrated with query methods.

Sequence queries#

Similar to the other kinds of LINQ, LINQ to Entities implements deferred execution for these query methods returning IQueryable. The SQL query is translated and executed only when trying to pull the result value from IQueryable for the first time.

Filtering (restriction)#

EF/Core translates Where query method call to WHERE clause in SQL, and translates the predicate expression tree (again, not predicate function) to the condition in WHERE clause. The following example queries categories with ProductCategoryID greater than 0:

1
internal static void Where(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> source = adventureWorks.ProductCategories;
4
    IQueryable<ProductCategory> categories = source.Where(category => category.ProductCategoryID > 0); // Define query.
5
    categories.WriteLines(category => category.Name); // Execute query.
6
    // SELECT [category].[ProductCategoryID], [category].[Name]
7
    // FROM [Production].[ProductCategory] AS [category]
8
    // WHERE [category].[ProductCategoryID] > 0
9
}

When WriteLines executes, it pulls the results from the query represented by IQueryable. At this moment, the query is translated to SQL, and executed in database, then SQL execution results are read by EF/Core and yielded.

The C# || operator in the predicate expression tree is translated to SQL OR operator in WHERE clause:

1
internal static void WhereWithOr(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> source = adventureWorks.ProductCategories;
4
    IQueryable<ProductCategory> categories = source.Where(category =>
5
        category.ProductCategoryID < 2 || category.ProductCategoryID > 3); // Define query.
6
    categories.WriteLines(category => category.Name); // Execute query.
7
    // SELECT [category].[ProductCategoryID], [category].[Name]
8
    // FROM [Production].[ProductCategory] AS [category]
9
    // WHERE ([category].[ProductCategoryID] < 2) OR ([category].[ProductCategoryID] > 3)
10
}

Similarly, the C# && operator is translated to SQL AND operator:

1
internal static void WhereWithAnd(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> source = adventureWorks.ProductCategories;
4
    IQueryable<ProductCategory> categories = source.Where(category =>
5
        category.ProductCategoryID > 0 && category.ProductCategoryID < 5); // Define query.
6
    categories.WriteLines(category => category.Name); // Execute query.
7
    // SELECT [category].[ProductCategoryID], [category].[Name]
8
    // FROM [Production].[ProductCategory] AS [category]
9
    // WHERE ([category].[ProductCategoryID] > 0) AND ([category].[ProductCategoryID] < 5)
10
}

Multiple Where calls are also translated to one single WHERE clause with AND:

1
internal static void WhereAndWhere(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> source = adventureWorks.ProductCategories;
4
    IQueryable<ProductCategory> categories = source
5
        .Where(category => category.ProductCategoryID > 0)
6
        .Where(category => category.ProductCategoryID < 5); // Define query.
7
    categories.WriteLines(category => category.Name); // Execute query.
8
    // SELECT [category].[ProductCategoryID], [category].[Name]
9
    // FROM [Production].[ProductCategory] AS [category]
10
    // WHERE ([category].[ProductCategoryID] > 0) AND ([category].[ProductCategoryID] < 5)
11
}

The other filtering method, OfType, can be used for entity types in inheritance hierarchy. And it is equivalent to Where query with is operator. The following examples both query sales transactions from all transactions:

1
internal static void WhereWithIs(AdventureWorks adventureWorks)
2
{
3
    IQueryable<TransactionHistory> source = adventureWorks.Transactions;
4
    IQueryable<TransactionHistory> transactions = source.Where(transaction => transaction is SalesTransactionHistory); // Define query.
5
    transactions.WriteLines(transaction => $"{transaction.GetType().Name} {transaction.TransactionDate} {transaction.ActualCost}"); // Execute query.
6
    // SELECT [transaction].[TransactionID], [transaction].[ActualCost], [transaction].[ProductID], [transaction].[Quantity], [transaction].[TransactionDate], [transaction].[TransactionType]
7
    // FROM [Production].[TransactionHistory] AS [transaction]
8
    // WHERE [transaction].[TransactionType] IN (N'W', N'S', N'P') AND ([transaction].[TransactionType] = N'S')
9
}
10

11
internal static void OfTypeEntity(AdventureWorks adventureWorks)
12
{
13
    IQueryable<TransactionHistory> source = adventureWorks.Transactions;
14
    IQueryable<WorkTransactionHistory> transactions = source.OfType<WorkTransactionHistory>(); // Define query.
15
    transactions.WriteLines(transaction => $"{transaction.GetType().Name} {transaction.TransactionDate} {transaction.ActualCost}"); // Execute query.
16
    // SELECT [t].[TransactionID], [t].[ActualCost], [t].[ProductID], [t].[Quantity], [t].[TransactionDate], [t].[TransactionType]
17
    // FROM [Production].[TransactionHistory] AS [t]
18
    // WHERE [t].[TransactionType] = N'W'
19
}

When primitive type is specified for OfType, it works locally. The following example queries products with ProductSubcategoryID not null:

1
internal static void OfTypePrimitive(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IQueryable<int> products = source.Select(product => product.ProductSubcategoryID).OfType<int>(); // Define query.
5
    products.ToArray().Length.WriteLine(); // Execute query.
6
    // SELECT [p].[ProductSubcategoryID]
7
    // FROM [Production].[Product] AS [p]
8
}

In EF Core, the above query is translated to a basic SELECT statement without filtering. EF Core executes the translated SQL to query the specified nullable int column of all rows to local, then the int results are locally filtered from all the nullable int results.

EF does not support OfType with primitive type. In EF, the above query throws NotSupportedException: ‘System.Int32’ is not a valid metadata type for type filtering operations. Type filtering is only valid on entity types and complex types.

Mapping (projection)#

In above queries, Queryable.Select is not called, and the query results are entities. So in the translated SQL, the SELECT clause queries all the mapped columns in order to construct the result entities. When Select is called, the selector expression tree is translated into SELECT clause. The following example queries persons’ full names by concatenating the first name and last name:

1
internal static void Select(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Person> source = adventureWorks.People;
4
    IQueryable<string> names = source.Select(person =>
5
        person.FirstName + " " + person.LastName); // Define query.
6
    names.WriteLines(); // Execute query.
7
    // SELECT ([person].[FirstName] + N' ') + [person].[LastName]
8
    // FROM [Person].[Person] AS [person]
9
}

In EF/Core, Select also work with anonymous type. For example:

1
internal static void SelectAnonymousType(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    var products = source.Select(product =>
5
        new { Name = product.Name, IsExpensive = product.ListPrice > 1_000 }); // Define query.
6
    products.WriteLines(); // Execute query.
7
    // SELECT [product].[Name], CASE
8
    //    WHEN [product].[ListPrice] > 1000.0
9
    //    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
10
    // END
11
    // FROM [Production].[Product] AS [product]
12
}

In EF Core, Select supports entity type too:

1
internal static void SelectEntity(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IQueryable<Product> products = source
5
        .Where(product => product.ListPrice > 1_000)
6
        .Select(product => new Product()
7
        {
8
            ProductID = product.ProductID,
9
            Name = product.Name
10
        }); // Define query.
11
    products.WriteLines(product => $"{product.ProductID}: {product.Name}"); // Execute query.
12
    // SELECT [product].[ProductID], [product].[Name]
13
    // FROM [Production].[Product] AS [product]
14
    // WHERE [product].[ListPrice] > 1000.0
15
}

In EF, Select does not support entity type. The above query throws NotSupportedException: The entity or complex type ‘Product’ cannot be constructed in a LINQ to Entities query.

Generation#

As fore mentioned, DefaultIfEmpty is the only built-in generation method:

1
internal static void DefaultIfEmptyEntity(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> source = adventureWorks.ProductCategories;
4
    IQueryable<ProductCategory> categories = source
5
        .Where(category => category.ProductCategoryID < 0)
6
        .DefaultIfEmpty(); // Define query.
7
    categories.ForEach( // Execute query.
8
        category => (category == null).WriteLine()); // True
9
    // SELECT [t].[ProductCategoryID], [t].[Name]
10
    // FROM (
11
    //    SELECT NULL AS [empty]
12
    // ) AS [empty]
13
    // LEFT JOIN (
14
    //    SELECT [category].[ProductCategoryID], [category].[Name]
15
    //    FROM [Production].[ProductCategory] AS [category]
16
    //    WHERE [category].[ProductCategoryID] < 0
17
    // ) AS [t] ON 1 = 1
18
}

In the above query, Where method is translated to SQL query with WHERE clause. Since DefaultIfEmpty should yield at least 1 entity, it is translated to LEFT JOIN with a single row table on a condition that always holds, so that the final query result is guaranteed to have at least 1 row. Here Where filters out all entities, in another word, the right table of LEFT JOIN has no rows, so the LEFT JOIN results 1 row, where all columns are NULL, including primary key. Therefore, DefaultIfEmpty yields a null entity. Besides entity type, DefaultIfEmpty works with primitive type in the same way.

The other DefaultIfEmpty overload accepts a specified default value. EF Core does not translate it to SQL, but execute the query logic locally. For example:

1
internal static void DefaultIfEmptyEntity(AdventureWorks adventureWorks)
2
{
3
    ProductCategory @default = new ProductCategory() { Name = nameof(ProductCategory) };
4
    IQueryable<ProductCategory> source = adventureWorks.ProductCategories;
5
    IQueryable<ProductCategory> categories = source
6
        .Where(category => category.ProductCategoryID < 0)
7
        .DefaultIfEmpty(@default); ; // Define query.
8
    categories.WriteLines( // Execute query.
9
        category => category?.Name); // ProductCategory
10
    // SELECT [category].[ProductCategoryID], [category].[Name]
11
    // FROM [Production].[ProductCategory] AS [category]
12
    // WHERE [category].[ProductCategoryID] < 0
13
}

Here the source query for DefaultIfEmpty is translated to SQL and executed, then EF Core reads the results to local, and detect the results locally. If there is no result row, the specified default value is yielded. DefaultIfEmpty works for specified default primitive value locally too.

In EF, DefaultIfEmpty without default value works with entity type and primitive type. In both cases it is translated to a similar left outer join with a single row. Since EF executes query remotely, the overload with specified default value does not work with entity type, and throws NotSupportedException: Unable to create a constant value of type ‘ProductCategory’. Only primitive types or enumeration types are supported in this context. This overload works with specified default primitive value:
1
internal static void DefaultIfEmptyWithDefaultPrimitive(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> source = adventureWorks.ProductCategories;
4
    IQueryable<int> categories = source
5
        .Where(category => category.ProductCategoryID < 0)
6
        .Select(category => category.ProductCategoryID)
7
        .DefaultIfEmpty(-1); // Define query.
8
    categories.WriteLines(); // Execute query.
9
#if EF
10
    // SELECT
11
    //    CASE WHEN ([Project1].[C1] IS NULL) THEN -1 ELSE [Project1].[ProductCategoryID] END AS [C1]
12
    //    FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
13
    //    LEFT OUTER JOIN  (SELECT
14
    //        [Extent1].[ProductCategoryID] AS [ProductCategoryID],
15
    //        cast(1 as tinyint) AS [C1]
16
    //        FROM [Production].[ProductCategory] AS [Extent1]
17
    //        WHERE [Extent1].[ProductCategoryID] < 0 ) AS [Project1] ON 1 = 1
18
#else
19
    // SELECT [category].[ProductCategoryID]
20
    // FROM [Production].[ProductCategory] AS [category]
21
    // WHERE [category].[ProductCategoryID] < 0
22
#endif
23
}
Notice the default value –1 is translated into the remote SQL query. It is the query result if the right table of left outer join is empty. So there is no local query or local detection executed.

Just like in LINQ to Objects, DefaultIfEmpty can also be used to implement outer join, which is discussed later.

Grouping#

EF Core execute grouping locally. For example. The following is a simple example that groups the subcategories by category:

1
internal static void GroupBy(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductSubcategory> source = adventureWorks.ProductSubcategories;
4
    IQueryable<IGrouping<int, string>> groups = source.GroupBy(
5
        keySelector: subcategory => subcategory.ProductCategoryID,
6
        elementSelector: subcategory => subcategory.Name); // Define query.
7
    groups.WriteLines(group => $"{group.Key}: {string.Join(", ", group)}"); // Execute query.
8
    // SELECT [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID]
9
    // FROM [Production].[ProductSubcategory] AS [subcategory]
10
    // ORDER BY [subcategory].[ProductCategoryID]
11
}

EF Core only translates GroupBy an additional ORDER BY clause with the grouping key, so that when reading the SQL execution results to local, the subcategories appears group by group.

EF only supports remote grouping. In EF, the above GroupBy is fully translated to SQL:

1
SELECT
2
    [Project2].[ProductCategoryID] AS [ProductCategoryID],
3
    [Project2].[C1] AS [C1],
4
    [Project2].[Name] AS [Name]
5
    FROM ( SELECT
6
        [Distinct1].[ProductCategoryID] AS [ProductCategoryID],
7
        [Extent2].[Name] AS [Name],
8
        CASE WHEN ([Extent2].[ProductCategoryID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
9
        FROM   (SELECT DISTINCT
10
            [Extent1].[ProductCategoryID] AS [ProductCategoryID]
11
            FROM [Production].[ProductSubcategory] AS [Extent1] ) AS [Distinct1]
12
        LEFT OUTER JOIN [Production].[ProductSubcategory] AS [Extent2] ON [Distinct1].[ProductCategoryID] = [Extent2].[ProductCategoryID]
13
    )  AS [Project2]
14
    ORDER BY [Project2].[ProductCategoryID] ASC, [Project2].[C1] ASC

It is translated to LEFT OUTER JOIN instead of GROUP BY, because above GroupBy returns hierarchical results (a sequence of groups, where each group is a sequence of results). In the translated SQL, the distinct keys are queried with SELECT DISTINCT. Then these keys left outer joins the rows with LEFT OUTER JOIN, and results all available key-row pairs, which are sorted by key with ORDER BY. So eventually EF reads and yields the results group by group.

When GroupBy returns flattened results (sequence of results), it is translated to GROUP BY clause. This can be done with a GroupBy overload accepting a result selector, or equivalently an additional Select query. The following examples call aggregation query method Count to flatten the results, and they have identical translation:

1
internal static void GroupByWithResultSelector(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductSubcategory> source = adventureWorks.ProductSubcategories;
4
    var groups = source.GroupBy(
5
        keySelector: subcategory => subcategory.ProductCategoryID,
6
        elementSelector: subcategory => subcategory.Name,
7
        resultSelector: (key, group) => new { CategoryID = key, SubcategoryCount = group.Count() }); // Define query.
8
    groups.WriteLines(); // Execute query.
9
#if EF
10
    // SELECT
11
    //    [GroupBy1].[K1] AS [ProductCategoryID],
12
    //    [GroupBy1].[A1] AS [C1]
13
    //    FROM ( SELECT
14
    //        [Extent1].[ProductCategoryID] AS [K1],
15
    //        COUNT(1) AS [A1]
16
    //        FROM [Production].[ProductSubcategory] AS [Extent1]
17
    //        GROUP BY [Extent1].[ProductCategoryID]
18
    //    )  AS [GroupBy1]
19
#else
20
    // SELECT [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID]
21
    // FROM [Production].[ProductSubcategory] AS [subcategory]
22
    // ORDER BY [subcategory].[ProductCategoryID]
23
#endif
24
}
25

26
internal static void GroupByAndSelect(AdventureWorks adventureWorks)
27
{
28
    IQueryable<ProductSubcategory> source = adventureWorks.ProductSubcategories;
29
    var groups = source
30
        .GroupBy(
31
            keySelector: subcategory => subcategory.ProductCategoryID,
32
            elementSelector: subcategory => subcategory.Name)
33
        .Select(group => new { CategoryID = group.Key, SubcategoryCount = group.Count() }); // Define query.
34
    groups.WriteLines(); // Execute query.
35
}

SelectMany can flatten hierarchical results too. The following GroupBy example does not have aggregation subquery, so it cannot be translated to GROUP BY. It is translated to INNER JOIN::

1
internal static void GroupByAndSelectMany(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductSubcategory> source = adventureWorks.ProductSubcategories;
4
    IQueryable<ProductSubcategory> distinct = source
5
        .GroupBy(keySelector: subcategory => subcategory.ProductCategoryID)
6
        .SelectMany(group => group); // Define query.
7
    distinct.WriteLines(subcategory => subcategory.Name); // Execute query.
8
#if EF
9
    // SELECT
10
    //    [Extent2].[ProductSubcategoryID] AS [ProductSubcategoryID],
11
    //    [Extent2].[Name] AS [Name],
12
    //    [Extent2].[ProductCategoryID] AS [ProductCategoryID]
13
    //    FROM   (SELECT DISTINCT
14
    //        [Extent1].[ProductCategoryID] AS [ProductCategoryID]
15
    //        FROM [Production].[ProductSubcategory] AS [Extent1] ) AS [Distinct1]
16
    //    INNER JOIN [Production].[ProductSubcategory] AS [Extent2] ON [Distinct1].[ProductCategoryID] = [Extent2].[ProductCategoryID]
17
#else
18
    // SELECT [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID]
19
    // FROM [Production].[ProductSubcategory] AS [subcategory]
20
    // ORDER BY [subcategory].[ProductCategoryID]
21
#endif
22
}

GroupBy’s key selector can return anonymous type to support grouping by multiple keys, still locally in EF Core:

1
internal static void GroupByMultipleKeys(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    var groups = source.GroupBy(
5
        keySelector: product => new { ProductSubcategoryID = product.ProductSubcategoryID, ListPrice = product.ListPrice },
6
        resultSelector: (key, group) => new
7
        {
8
            ProductSubcategoryID = key.ProductSubcategoryID,
9
            ListPrice = key.ListPrice,
10
            Count = group.Count()
11
        }); // Define query.
12
    groups.WriteLines(); // Execute query.
13
    // SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]
14
    // FROM [Production].[Product] AS [product]
15
    // ORDER BY [product].[ProductSubcategoryID], [product].[ListPrice]
16
}

EF properly translates the above query to GROUP BY:

1
SELECT
2
    1 AS [C1],
3
    [GroupBy1].[K2] AS [ProductSubcategoryID],
4
    [GroupBy1].[K1] AS [ListPrice],
5
    [GroupBy1].[A1] AS [C2]
6
    FROM ( SELECT
7
        [Extent1].[ListPrice] AS [K1],
8
        [Extent1].[ProductSubcategoryID] AS [K2],
9
        COUNT(1) AS [A1]
10
        FROM [Production].[Product] AS [Extent1]
11
        GROUP BY [Extent1].[ListPrice], [Extent1].[ProductSubcategoryID]
12
    )  AS [GroupBy1]

Join#

Inner join#

Similar to LINQ to Objects, Join is provided for inner join. The following example simply join the subcategories and categories with foreign key:

1
internal static void InnerJoinWithJoin(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
4
    IQueryable<ProductSubcategory> inner = adventureWorks.ProductSubcategories;
5
    var categorySubcategories = outer.Join(
6
        inner: inner,
7
        outerKeySelector: category => category.ProductCategoryID,
8
        innerKeySelector: subcategory => subcategory.ProductCategoryID,
9
        resultSelector: (category, subcategory) =>
10
            new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.
11
    // var categorySubcategories =
12
    //    from category in outer
13
    //    join subcategory in inner
14
    //    on category.ProductCategoryID equals subcategory.ProductCategoryID
15
    //    select new { Category = category.Name, Subcategory = subcategory.Name };
16
    categorySubcategories.WriteLines(); // Execute query.
17
    // SELECT [category].[Name], [subcategory].[Name]
18
    // FROM [Production].[ProductCategory] AS [category]
19
    // INNER JOIN [Production].[ProductSubcategory] AS [subcategory] ON [category].[ProductCategoryID] = [subcategory].[ProductCategoryID]
20
}

Join’s key selectors can return anonymous type to join with multiple keys:

1
internal static void InnerJoinWithMultipleKeys(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> outer = adventureWorks.Products;
4
    IQueryable<TransactionHistory> inner = adventureWorks.Transactions;
5
    var transactions = outer.Join(
6
        inner: inner,
7
        outerKeySelector: product =>
8
            new { ProductID = product.ProductID, UnitPrice = product.ListPrice },
9
        innerKeySelector: transaction =>
10
            new { ProductID = transaction.ProductID, UnitPrice = transaction.ActualCost / transaction.Quantity },
11
        resultSelector: (product, transaction) =>
12
            new { Name = product.Name, Quantity = transaction.Quantity }); // Define query.
13
    // var transactions =
14
    //    from product in adventureWorks.Products
15
    //    join transaction in adventureWorks.Transactions
16
    //    on new { ProductID = product.ProductID, UnitPrice = product.ListPrice }
17
    //        equals new { ProductID = transaction.ProductID, UnitPrice = transaction.ActualCost / transaction.Quantity }
18
    //    select new { Name = product.Name, Quantity = transaction.Quantity };
19
    transactions.WriteLines(); // Execute query.
20
    // SELECT [product].[Name], [transaction].[Quantity]
21
    // FROM [Production].[Product] AS [product]
22
    // INNER JOIN [Production].[TransactionHistory] AS [transaction] ON ([product].[ProductID] = [transaction].[ProductID]) AND ([product].[ListPrice] = ([transaction].[ActualCost] / [transaction].[Quantity]))
23
}

Just like LINQ to Objects, inner join can be done by SelectMany, Select, and GroupJoin as well. In the following example, Select returns hierarchical data, so an additional SelectMany can flatten the result:

1
internal static void InnerJoinWithSelect(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
4
    IQueryable<ProductSubcategory> inner = adventureWorks.ProductSubcategories;
5
    var categorySubcategories = outer
6
        .Select(category => new
7
        {
8
            Category = category,
9
            Subcategories = inner
10
                .Where(subcategory => category.ProductCategoryID == subcategory.ProductCategoryID)
11
                // LEFT OUTER JOIN if DefaultIfEmpty is called.
12
        })
13
        .SelectMany(
14
            collectionSelector: category => category.Subcategories,
15
            resultSelector: (category, subcategory) =>
16
                new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.
17
    // var categorySubcategories =
18
    //    from category in outer
19
    //    select new
20
    //    {
21
    //        Category = category,
22
    //        Subcategories = from subcategory in inner
23
    //                        where category.ProductCategoryID == subcategory.ProductCategoryID
24
    //                        select subcategory
25
    //    } into category
26
    //    from subcategory in category.Subcategories
27
    //    select new { Category = category.Category.Name, Subcategory = subcategory.Name };
28
    categorySubcategories.WriteLines(); // Execute query.
29
    // SELECT [category].[Name], [subcategory].[Name]
30
    // FROM [Production].[ProductCategory] AS [category]
31
    // CROSS JOIN [Production].[ProductSubcategory] AS [subcategory]
32
    // WHERE [category].[ProductCategoryID] = [subcategory].[ProductCategoryID]
33
}

EF Core translates the above query to CROOS JOIN with WHERE clause, which is equivalent to the previous INNER JOIN query, with the same query plan.

EF still translates above query to INNER JOIN.

The following example implement the same inner join directly with SelectMany. Its SQL translation is the same INNER JOIN as the first Join example:

1
internal static void InnerJoinWithSelectMany(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
4
    IQueryable<ProductSubcategory> inner = adventureWorks.ProductSubcategories;
5
    var categorySubcategories = outer
6
        .SelectMany(
7
            collectionSelector: category => inner
8
                .Where(subcategory => category.ProductCategoryID == subcategory.ProductCategoryID),
9
                // LEFT OUTER JOIN if DefaultIfEmpty is called.
10
            resultSelector: (category, subcategory) =>
11
                new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.
12
    // var categorySubcategories =
13
    //   from category in outer
14
    //   from subcategory in (from subcategory in inner
15
    //                        where category.ProductCategoryID == subcategory.ProductCategoryID
16
    //                        select subcategory)
17
    //   select new { Category = category.Name, Subcategory = subcategory.Name };
18
    // Or equivalently:
19
    // var categorySubcategories =
20
    //    from category in outer
21
    //    from subcategory in inner
22
    //    where category.ProductCategoryID == subcategory.ProductCategoryID
23
    //    select new { Category = category.Name, Subcategory = subcategory.Name };
24
    categorySubcategories.WriteLines(); // Execute query.
25
}

The above Select and SelectMany has a Where subquery to filter the related entities to join with. The Where subquery can be substituted by collection navigation property. After the substitution, the queries are translated to the same INNER JOIN as the first Join example:

1
internal static void InnerJoinWithSelectAndRelationship(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
4
    var categorySubcategories = outer
5
        .Select(category => new { Category = category, Subcategories = category.ProductSubcategories })
6
        .SelectMany(
7
            collectionSelector: category => category.Subcategories,
8
            // LEFT OUTER JOIN if DefaultIfEmpty is missing.
9
            resultSelector: (category, subcategory) =>
10
                new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.
11
    // var categorySubcategories =
12
    //    from category in outer
13
    //    select new { Category = category, Subcategories = category.ProductSubcategories } into category
14
    //    from subcategory in category.Subcategories
15
    //    select new { Category = category.Category.Name, Subcategory = subcategory.Name };
16
    categorySubcategories.WriteLines(); // Execute query.
17
}
18

19
internal static void InnerJoinWithSelectManyAndRelationship(AdventureWorks adventureWorks)
20
{
21
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
22
    var categorySubcategories = outer.SelectMany(
23
        collectionSelector: category => category.ProductSubcategories,
24
        // LEFT OUTER JOIN if DefaultIfEmpty is missing.
25
        resultSelector: (category, subcategory) =>
26
            new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.
27
    // var categorySubcategories =
28
    //    from category in outer
29
    //    from subcategory in category.ProductSubcategories
30
    //    select new { Category = category.Name, Subcategory = subcategory.Name };
31
    categorySubcategories.WriteLines(); // Execute query.
32
}

GroupJoin also returns hierarchical result, so again an additional SelectMany can flatten the result. The following example still has the same INNER JOIN translation as the first Join example:

1
internal static void InnerJoinWithGroupJoinAndSelectMany(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
4
    IQueryable<ProductSubcategory> inner = adventureWorks.ProductSubcategories;
5
    var categorySubcategories = outer
6
        .GroupJoin(
7
            inner: inner,
8
            outerKeySelector: category => category.ProductCategoryID,
9
            innerKeySelector: subcategory => subcategory.ProductCategoryID,
10
            resultSelector: (category, subcategories) =>
11
                new { Category = category, Subcategories = subcategories })
12
        .SelectMany(
13
            collectionSelector: category => category.Subcategories,
14
            // LEFT OUTER JOIN if DefaultIfEmpty is called.
15
            resultSelector: (category, subcategory) =>
16
                new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.
17
    // var categorySubcategories =
18
    //    from category in outer
19
    //    join subcategory in inner
20
    //    on category.ProductCategoryID equals subcategory.ProductCategoryID into subcategories
21
    //    from subcategory in subcategories
22
    //    select new { Category = category.Name, Subcategory = subcategory.Name };
23
    categorySubcategories.WriteLines(); // Execute query.
24
}

Navigation property makes it very easy to join entities with relationship. The following example inner joins 3 entity types, where 2 entity types have many-to-many relationship with a junction entity type:

1
internal static void MultipleInnerJoinsWithRelationship(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    var productPhotos = source.SelectMany(
5
        collectionSelector: product => product.ProductProductPhotos,
6
        resultSelector: (product, productProductPhoto) => new
7
        {
8
            Product = product.Name,
9
            Photo = productProductPhoto.ProductPhoto.LargePhotoFileName
10
        }); // Define query.
11
    // var productPhotos =
12
    //    from product in source
13
    //    from productProductPhoto in product.ProductProductPhotos
14
    //    select new { Product = product.Name, Photo = productProductPhoto.ProductPhoto.LargePhotoFileName };
15
    productPhotos.WriteLines(); // Execute query.
16
    // SELECT [product].[Name], [product.ProductProductPhotos.ProductPhoto].[LargePhotoFileName]
17
    // FROM [Production].[Product] AS [product]
18
    // INNER JOIN [Production].[ProductProductPhoto] AS [product.ProductProductPhotos] ON [product].[ProductID] = [product.ProductProductPhotos].[ProductID]
19
    // INNER JOIN [Production].[ProductPhoto] AS [product.ProductProductPhotos.ProductPhoto] ON [product.ProductProductPhotos].[ProductPhotoID] = [product.ProductProductPhotos.ProductPhoto].[ProductPhotoID]
20
}

Left outer join#

GroupJoin is provided for left outer join. The following example have categories to left outer join subcategories with foreign key, and the results have all categories with or without matching subcategories. It is translated to LEFT JOIN:

1
internal static void LeftOuterJoinWithGroupJoin(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
4
    IQueryable<ProductSubcategory> inner = adventureWorks.ProductSubcategories;
5
    var categorySubcategories = outer
6
        .GroupJoin(
7
            inner: inner,
8
            outerKeySelector: category => category.ProductCategoryID,
9
            innerKeySelector: subcategory => subcategory.ProductCategoryID,
10
            resultSelector: (category, subcategories) =>
11
                new { Category = category, Subcategories = subcategories }); // Define query.
12
    // var categorySubcategories =
13
    //    from category in outer
14
    //    join subcategory in inner
15
    //    on category.ProductCategoryID equals subcategory.ProductCategoryID into subcategories
16
    //    select new { Category = category, Subcategories = subcategories };
17
    categorySubcategories.WriteLines(categorySubcategory =>
18
        $@"{categorySubcategory.Category.Name}: {string.Join(
19
            ", ", categorySubcategory.Subcategories.Select(subcategory => subcategory.Name))}"); // Execute query.
20
    // SELECT [category].[ProductCategoryID], [category].[Name], [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID]
21
    // FROM [Production].[ProductCategory] AS [category]
22
    // LEFT JOIN [Production].[ProductSubcategory] AS [subcategory] ON [category].[ProductCategoryID] = [subcategory].[ProductCategoryID]
23
    // ORDER BY [category].[ProductCategoryID]
24
}

GroupJoin returns hierarchical results. So here the translated SQL also sorts the result by the key, so that EF/Core can read the query results group by group. To have flattened results from GroupJoin, SelectMany can be called. As discussed in the LINQ to Objects chapter, an DefaultIfEmpty subquery is required (It becomes inner join if DefaultIfEmpty is missing). The following example has the same SQL translation as above, it just yields result by result instead of group by group.

1
internal static void LeftOuterJoinWithGroupJoinAndSelectMany(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
4
    IQueryable<ProductSubcategory> inner = adventureWorks.ProductSubcategories;
5
    var categorySubcategories = outer
6
        .GroupJoin(
7
            inner: inner,
8
            outerKeySelector: category => category.ProductCategoryID,
9
            innerKeySelector: subcategory => subcategory.ProductCategoryID,
10
            resultSelector: (category, subcategories) =>
11
                new { Category = category, Subcategories = subcategories }) // Define query.
12
        .SelectMany(
13
            collectionSelector: category => category.Subcategories
14
                .DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.
15
            resultSelector: (category, subcategory) =>
16
                new { Category = category.Category, Subcategory = subcategory }); // Define query.
17
    // var categorySubcategories =
18
    //    from category in outer
19
    //    join subcategory in inner
20
    //    on category.ProductCategoryID equals subcategory.ProductCategoryID into subcategories
21
    //    from subcategory in subcategories.DefaultIfEmpty()
22
    //    select new { Category = category.Name, Subcategory = subcategory.Name };
23
    categorySubcategories.WriteLines(categorySubcategory =>
24
        $"{categorySubcategory.Category.Name} {categorySubcategory.Subcategory?.Name}"); // Execute query.
25
}

Similar to inner join, left outer join can be done with Select and SelectMany too, with a DefaultIfEmpty subquery. The following queries have the same SQL translation:

1
internal static void LeftOuterJoinWithSelect(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
4
    IQueryable<ProductSubcategory> inner = adventureWorks.ProductSubcategories;
5
    var categorySubcategories = outer
6
        .Select(category => new
7
        {
8
            Category = category,
9
            Subcategories = inner
10
                .Where(subcategory => category.ProductCategoryID == subcategory.ProductCategoryID)
11
        })
12
        .SelectMany(
13
            collectionSelector: category => category.Subcategories
14
                .DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.
15
            resultSelector: (category, subcategory) =>
16
                new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.
17
    // var categorySubcategories =
18
    //    from category in outer
19
    //    select new
20
    //    {
21
    //        Category = category,
22
    //        Subcategories = from subcategory in inner
23
    //                        where subcategory.ProductCategoryID == category.ProductCategoryID
24
    //                        select subcategory
25
    //    } into category
26
    //    from subcategory in category.Subcategories.DefaultIfEmpty()
27
    //    select new { Category = category.Category.Name, Subcategory = subcategory.Name };
28
    categorySubcategories.WriteLines(); // Execute query.
29
    // SELECT [category].[Name], [t1].[Name]
30
    // FROM [Production].[ProductCategory] AS [category]
31
    // CROSS APPLY (
32
    //    SELECT [t0].*
33
    //    FROM (
34
    //        SELECT NULL AS [empty]
35
    //    ) AS [empty0]
36
    //    LEFT JOIN (
37
    //        SELECT [subcategory0].*
38
    //        FROM [Production].[ProductSubcategory] AS [subcategory0]
39
    //        WHERE [category].[ProductCategoryID] = [subcategory0].[ProductCategoryID]
40
    //    ) AS [t0] ON 1 = 1
41
    // ) AS [t1]
42
}
43

44
internal static void LeftOuterJoinWithSelectMany(AdventureWorks adventureWorks)
45
{
46
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
47
    IQueryable<ProductSubcategory> inner = adventureWorks.ProductSubcategories;
48
    var categorySubcategories = outer
49
        .SelectMany(
50
            collectionSelector: category => inner
51
                .Where(subcategory => category.ProductCategoryID == subcategory.ProductCategoryID)
52
                .DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.
53
            resultSelector: (category, subcategory) =>
54
                new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.
55
    // var categorySubcategories =
56
    //    from category in outer
57
    //    from subcategory in (from subcategory in inner
58
    //                         where category.ProductCategoryID == subcategory.ProductCategoryID
59
    //                         select subcategory).DefaultIfEmpty()
60
    //    select new { Category = category.Name, Subcategory = subcategory.Name };
61
    categorySubcategories.WriteLines(); // Execute query.
62
}

In EF Core, the above 2 queries are both translated to CROSS APPLY, but this is logically equivalent to LEFT JOIN of the GroupJoin example.

In EF, these translation are the same LEFT OUTER JOIN as previous GroupJoin.

As demonstrated for inner join, in the above Select and SelectMany queries, the Where subquery is equivalent to collection navigation property. EF/Core support collection navigation property for left outer join with Select and SelectMany. The following queries are translated to the same LEFT JOIN query:

1
internal static void LeftOuterJoinWithSelectAndRelationship(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
4
    var categorySubcategories = outer
5
        .Select(category => new { Category = category, Subcategories = category.ProductSubcategories })
6
        .SelectMany(
7
            collectionSelector: category => category.Subcategories
8
                .DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.
9
            resultSelector: (category, subcategory) =>
10
                new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.
11
    // var categorySubcategories =
12
    //    from category in outer
13
    //    select new { Category = category, Subcategories = category.ProductSubcategories } into category
14
    //    from subcategory in category.Subcategories.DefaultIfEmpty()
15
    //    select new { Category = category.Category.Name, Subcategory = subcategory.Name };
16
    categorySubcategories.WriteLines(); // Execute query.
17
    // SELECT [category].[Name] AS [Category], [category.ProductSubcategories].[Name] AS [Subcategory]
18
    // FROM [Production].[ProductCategory] AS [category]
19
    // LEFT JOIN [Production].[ProductSubcategory] AS [category.ProductSubcategories] ON [category].[ProductCategoryID] = [category.ProductSubcategories].[ProductCategoryID]
20
}
21

22
internal static void LeftOuterJoinWithSelectManyAndRelationship(AdventureWorks adventureWorks)
23
{
24
    IQueryable<ProductCategory> outer = adventureWorks.ProductCategories;
25
    var categorySubcategories = outer.SelectMany(
26
        collectionSelector: category => category.ProductSubcategories
27
            .DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.
28
        resultSelector: (category, subcategory) =>
29
            new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.
30
    // var categorySubcategories =
31
    //    from category in outer
32
    //    from subcategory in category.ProductSubcategories.DefaultIfEmpty()
33
    //    select new { Category = category.Name, Subcategory = subcategory.Name };
34
    categorySubcategories.WriteLines(); // Execute query.
35
}

Cross join#

Just like LINQ to Objects, cross join can be done with SelectMany and Join. The following example queries the expensive products (list price greater than 2000) and cheap products (list price less than 100), and then cross join them to get all possible product bundles, where each bundle has one expensive product and one cheap product:

1
internal static void CrossJoinWithSelectMany(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> outer = adventureWorks.Products.Where(product => product.ListPrice > 2000);
4
    IQueryable<Product> inner = adventureWorks.Products.Where(product => product.ListPrice < 100);
5
    var bundles = outer.SelectMany(
6
        collectionSelector: expensiveProduct => inner,
7
        resultSelector: (expensiveProduct, cheapProduct) =>
8
            new { Expensive = expensiveProduct.Name, Cheap = cheapProduct.Name }); // Define query.
9
    // var bundles =
10
    //    from outerProduct in outer
11
    //    from innerProduct in inner
12
    //    select new { Expensive = outerProduct.Name, Cheap = innerProduct.Name };
13
    bundles.WriteLines(); // Execute query.
14
    // SELECT [product].[Name], [product0].[Name]
15
    // FROM [Production].[Product] AS [product]
16
    // CROSS JOIN [Production].[Product] AS [product0]
17
    // WHERE ([product].[ListPrice] > 2000.0) AND ([product0].[ListPrice] < 100.0)
18
}

The following implementation with Join is equivalent, just have the 2 key selectors always return equal values:

1
internal static void CrossJoinWithJoin(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> outer = adventureWorks.Products.Where(product => product.ListPrice > 2000);
4
    IQueryable<Product> inner = adventureWorks.Products.Where(product => product.ListPrice < 100);
5
    var bundles = outer.Join(
6
        inner: inner,
7
        outerKeySelector: product => 1,
8
        innerKeySelector: product => 1,
9
        resultSelector: (outerProduct, innerProduct) =>
10
            new { Expensive = outerProduct.Name, Cheap = innerProduct.Name }); // Define query.
11
    // var bundles =
12
    //    from outerProduct in outer
13
    //    join innerProduct in inner
14
    //    on 1 equals 1
15
    //    select new { Expensive = outerProduct.Name, Cheap = innerProduct.Name };
16
    bundles.WriteLines(); // Execute query.
17
    // SELECT [product].[Name], [t].[Name]
18
    // FROM [Production].[Product] AS [product]
19
    // INNER JOIN (
20
    //    SELECT [product1].*
21
    //    FROM [Production].[Product] AS [product1]
22
    //    WHERE [product1].[ListPrice] < 100.0
23
    // ) AS [t] ON 1 = 1
24
    // WHERE [product].[ListPrice] > 2000.0
25
}

It is translated to INNER JOIN, which is equivalent to previous CROSS JOIN, with the same query plan.

Concatenation#

EF Core does not support Concat for entity.

EF translates Concat to UNION ALL. The following example concatenates the cheap products and the expensive products, and query the products’ names:

1
internal static void ConcatEntity(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> first = adventureWorks.Products.Where(product => product.ListPrice < 100);
4
    IQueryable<Product> second = adventureWorks.Products.Where(product => product.ListPrice > 2000);
5
    IQueryable<string> concat = first
6
        .Concat(second)
7
        .Select(product => product.Name); // Define query.
8
    concat.WriteLines(); // Execute query.
9
#if EF
10
    // SELECT
11
    //    [UnionAll1].[Name] AS [C1]
12
    //    FROM  (SELECT
13
    //        [Extent1].[Name] AS [Name]
14
    //        FROM [Production].[Product] AS [Extent1]
15
    //        WHERE [Extent1].[ListPrice] < cast(100 as decimal(18))
16
    //    UNION ALL
17
    //        SELECT
18
    //        [Extent2].[Name] AS [Name]
19
    //        FROM [Production].[Product] AS [Extent2]
20
    //        WHERE [Extent2].[ListPrice] > cast(2000 as decimal(18))) AS [UnionAll1]
21
#else
22
    // ArgumentException: Expression of type 'System.Collections.Generic.IEnumerable`1[Product]' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable`1[Microsoft.EntityFrameworkCore.Storage.ValueBuffer]' of method 'System.Collections.Generic.IEnumerable`1[Microsoft.EntityFrameworkCore.Storage.ValueBuffer] Concat[ValueBuffer](System.Collections.Generic.IEnumerable`1[Microsoft.EntityFrameworkCore.Storage.ValueBuffer], System.Collections.Generic.IEnumerable`1[Microsoft.EntityFrameworkCore.Storage.ValueBuffer])' Parameter name: arg1
23
#endif
24
}

EF Core supports Concat for primitive type, locally. In the above example, Select is called after Concat. It is logically equivalent to call Select before Concat, which works in EF Core:

1
internal static void ConcatPrimitive(AdventureWorks adventureWorks)
2
{
3
    IQueryable<string> first = adventureWorks.Products
4
        .Where(product => product.ListPrice < 100)
5
        .Select(product => product.Name);
6
    IQueryable<string> second = adventureWorks.Products
7
        .Where(product => product.ListPrice > 2000)
8
        .Select(product => product.Name);
9
    IQueryable<string> concat = first.Concat(second); // Define query.
10
    concat.WriteLines(); // Execute query.
11
    // SELECT [product].[Name]
12
    // FROM [Production].[Product] AS [product]
13
    // WHERE [product].[ListPrice] < 100.0
14

15
    // SELECT [product0].[Name]
16
    // FROM [Production].[Product] AS [product0]
17
    // WHERE [product0].[ListPrice] > 2000.0
18
}

EF Core translates Concat’s 2 data sources to 2 SQL queries, reads the query results to local, and concatenates them locally.

In EF, this above query has identical UNION ALL translation as the first Concat example.

Set#

Distinct works with entity type and primitive type. It is translated to the DISTINCT keyword:

1
internal static void DistinctEntity(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductSubcategory> source = adventureWorks.ProductSubcategories;
4
    IQueryable<ProductCategory> distinct = source
5
        .Select(subcategory => subcategory.ProductCategory)
6
        .Distinct(); // Define query.
7
    distinct.WriteLines(category => $"{category.ProductCategoryID}: {category.Name}"); // Execute query.
8
    // SELECT DISTINCT [subcategory.ProductCategory].[ProductCategoryID], [subcategory.ProductCategory].[Name]
9
    // FROM [Production].[ProductSubcategory] AS [subcategory]
10
    // INNER JOIN [Production].[ProductCategory] AS [subcategory.ProductCategory] ON [subcategory].[ProductCategoryID] = [subcategory.ProductCategory].[ProductCategoryID]
11
}
12

13
internal static void DistinctPrimitive(AdventureWorks adventureWorks)
14
{    IQueryable<ProductSubcategory> source = adventureWorks.ProductSubcategories;
15
    IQueryable<int> distinct = source
16
        .Select(subcategory => subcategory.ProductCategoryID)
17
        .Distinct(); // Define query.
18
    distinct.WriteLines(); // Execute query.
19
    // SELECT DISTINCT [subcategory].[ProductCategoryID]
20
    // FROM [Production].[ProductSubcategory] AS [subcategory]
21
}

GroupBy returns groups with distinct keys, so in theory it can be used to query the same result as Distinct:

1
internal static void DistinctWithGroupBy(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductSubcategory> source = adventureWorks.ProductSubcategories;
4
    IQueryable<int> distinct = source.GroupBy(
5
        keySelector: subcategory => subcategory.ProductCategoryID,
6
        resultSelector: (key, group) => key); // Define query.
7
    distinct.WriteLines(); // Execute query.
8
    // SELECT [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID]
9
    // FROM [Production].[ProductSubcategory] AS [subcategory]
10
    // ORDER BY [subcategory].[ProductCategoryID]
11
}

However, as fore mentioned, in EF Core, GroupBy executes locally. The above example only queries grouping keys, however it reads all rows of the table to local, which can be a performance issue.

EF always execute queries remotely, so above GroupBy is properly translated to SELECT DISATINCT query.

GroupBy can also be used for more complex scenarios. The following example queries the full product entities with distinct list price:

1
internal static void DistinctWithGroupByAndFirstOrDefault(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IQueryable<Product> distinct = source.GroupBy(
5
        keySelector: product => product.ListPrice,
6
        resultSelector: (key, group) => group.FirstOrDefault()); // Define query.
7
    distinct.WriteLines(); // Execute query.
8
    // SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]
9
    // FROM [Production].[Product] AS [product]
10
    // ORDER BY [product].[ListPrice]
11
}

Again, EF Core does not translate grouping to SQL. In this example, only 1 entities for each key is queried, but EF Core reads all rows to local, and execute the grouping logic locally.

EF properly translates the above query to SELECT DISTINCT to query the unique keys, then outer applies each key to one row with OUTER APPLY:

1
SELECT
2
    [Limit1].[ProductID] AS [ProductID],
3
    [Limit1].[Name] AS [Name],
4
    [Limit1].[ListPrice] AS [ListPrice],
5
    [Limit1].[ProductSubcategoryID] AS [ProductSubcategoryID]
6
    FROM   (SELECT DISTINCT
7
        [Extent1].[ListPrice] AS [ListPrice]
8
        FROM [Production].[Product] AS [Extent1] ) AS [Distinct1]
9
    OUTER APPLY  (SELECT TOP (1)
10
        [Extent2].[ProductID] AS [ProductID],
11
        [Extent2].[Name] AS [Name],
12
        [Extent2].[ListPrice] AS [ListPrice],
13
        [Extent2].[ProductSubcategoryID] AS [ProductSubcategoryID]
14
        FROM [Production].[Product] AS [Extent2]
15
        WHERE [Distinct1].[ListPrice] = [Extent2].[ListPrice] ) AS [Limit1]

EF Core supports Union for entity and primitive types locally.

EF translates Union to Union ALL with SELECT DISTINCT, so eventually each result is unique.

1
internal static void UnionEntity(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> first = adventureWorks.Products
4
        .Where(product => product.ListPrice > 100);
5
    IQueryable<Product> second = adventureWorks.Products
6
        .Where(product => product.ProductSubcategoryID == 1);
7
    IQueryable<Product> union = first.Union(second); // Define query.
8
    union.WriteLines(); // Execute query.
9
#if EF
10
    // SELECT
11
    //    [Distinct1].[C1] AS [C1],
12
    //    [Distinct1].[C2] AS [C2],
13
    //    [Distinct1].[C3] AS [C3],
14
    //    [Distinct1].[C4] AS [C4],
15
    //    [Distinct1].[C5] AS [C5]
16
    //    FROM ( SELECT DISTINCT
17
    //        [UnionAll1].[ProductID] AS [C1],
18
    //        [UnionAll1].[Name] AS [C2],
19
    //        [UnionAll1].[ListPrice] AS [C3],
20
    //        [UnionAll1].[ProductSubcategoryID] AS [C4]
21
    //        FROM  (SELECT
22
    //            [Extent1].[ProductID] AS [ProductID],
23
    //            [Extent1].[Name] AS [Name],
24
    //            [Extent1].[ListPrice] AS [ListPrice],
25
    //            [Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID]
26
    //            FROM [Production].[Product] AS [Extent1]
27
    //            WHERE [Extent1].[ListPrice] > cast(100 as decimal(18))
28
    //        UNION ALL
29
    //            SELECT
30
    //            [Extent2].[ProductID] AS [ProductID],
31
    //            [Extent2].[Name] AS [Name],
32
    //            [Extent2].[ListPrice] AS [ListPrice],
33
    //            [Extent2].[ProductSubcategoryID] AS [ProductSubcategoryID]
34
    //            FROM [Production].[Product] AS [Extent2]
35
    //            WHERE 1 = [Extent2].[ProductSubcategoryID]) AS [UnionAll1]
36
    //    )  AS [Distinct1]
37
#else
38
    // SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]
39
    // FROM [Production].[Product] AS [product]
40
    // WHERE [product].[ListPrice] > 100.0
41

42
    // SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]
43
    // FROM [Production].[Product] AS [product]
44
    // [product0].[ProductSubcategoryID] = 1
45
#endif
46
}
47

48
internal static void UnionPrimitive(AdventureWorks adventureWorks)
49
{
50
    var first = adventureWorks.Products
51
        .Where(product => product.ListPrice > 100)
52
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice });
53
    var second = adventureWorks.Products
54
        .Where(product => product.ProductSubcategoryID == 1)
55
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice });
56
    var union = first.Union(second); // Define query.
57
    union.WriteLines(); // Execute query.
58
#if EF
59
    // SELECT
60
    //    [Distinct1].[C1] AS [C1],
61
    //    [Distinct1].[C2] AS [C2],
62
    //    [Distinct1].[C3] AS [C3]
63
    //    FROM ( SELECT DISTINCT
64
    //        [UnionAll1].[C1] AS [C1],
65
    //        [UnionAll1].[Name] AS [C2],
66
    //        [UnionAll1].[ListPrice] AS [C3]
67
    //        FROM  (SELECT
68
    //            1 AS [C1],
69
    //            [Extent1].[Name] AS [Name],
70
    //            [Extent1].[ListPrice] AS [ListPrice]
71
    //            FROM [Production].[Product] AS [Extent1]
72
    //            WHERE [Extent1].[ListPrice] > cast(100 as decimal(18))
73
    //        UNION ALL
74
    //            SELECT
75
    //            1 AS [C1],
76
    //            [Extent2].[Name] AS [Name],
77
    //            [Extent2].[ListPrice] AS [ListPrice]
78
    //            FROM [Production].[Product] AS [Extent2]
79
    //            WHERE 1 = [Extent2].[ProductSubcategoryID]) AS [UnionAll1]
80
    //    )  AS [Distinct1]
81
#else
82
    // SELECT [product].[Name], [product].[ListPrice]
83
    // FROM [Production].[Product] AS [product]
84
    // WHERE [product].[ListPrice] > 100.0
85

86
    // SELECT [product0].[Name], [product0].[ListPrice]
87
    // FROM [Production].[Product] AS [product0]
88
    // WHERE [product0].[ProductSubcategoryID] = 1
89
#endif
90
}

EF Core executes Intersect and Except locally as well.

EF translates Intersect to INTERSECT operator, and translates Except to EXCEPT operator:

1
internal static void IntersectEntity(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> first = adventureWorks.Products
4
        .Where(product => product.ListPrice > 100);
5
    IQueryable<Product> second = adventureWorks.Products
6
        .Where(product => product.ListPrice < 2000);
7
    IQueryable<Product> intersect = first.Intersect(second); // Define query.
8
    intersect.WriteLines(); // Execute query.
9
#if EF
10
    // SELECT
11
    //    [Intersect1].[ProductID] AS [C1],
12
    //    [Intersect1].[Name] AS [C2],
13
    //    [Intersect1].[ListPrice] AS [C3]
14
    //    FROM  (SELECT
15
    //        [Extent1].[ProductID] AS [ProductID],
16
    //        [Extent1].[Name] AS [Name],
17
    //        [Extent1].[ListPrice] AS [ListPrice],
18
    //        [Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID]
19
    //        FROM [Production].[Product] AS [Extent1]
20
    //        WHERE [Extent1].[ListPrice] > cast(100 as decimal(18))
21
    //    INTERSECT
22
    //        SELECT
23
    //        [Extent2].[ProductID] AS [ProductID],
24
    //        [Extent2].[Name] AS [Name],
25
    //        [Extent2].[ListPrice] AS [ListPrice],
26
    //        [Extent2].[ProductSubcategoryID] AS [ProductSubcategoryID]
27
    //        FROM [Production].[Product] AS [Extent2]
28
    //        WHERE [Extent2].[ListPrice] < cast(2000 as decimal(18))) AS [Intersect1]
29
#else
30
    // SELECT [product0].[ProductID], [product0].[ListPrice], [product0].[Name], [product0].[ProductSubcategoryID]
31
    // FROM [Production].[Product] AS [product0]
32
    // WHERE [product0].[ListPrice] < 2000.0
33

34
    // SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]
35
    // FROM [Production].[Product] AS [product]
36
    // WHERE [product].[ListPrice] > 100.0
37
#endif
38
}
39

40
internal static void ExceptPrimitive(AdventureWorks adventureWorks)
41
{
42
    var first = adventureWorks.Products
43
        .Where(product => product.ListPrice > 100)
44
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice });
45
    var second = adventureWorks.Products
46
        .Where(product => product.ListPrice > 2000)
47
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice });
48
    var except = first.Except(second); // Define query.
49
    except.WriteLines(); // Execute query.
50
#if EF
51
    // SELECT
52
    //    [Except1].[C1] AS [C1],
53
    //    [Except1].[Name] AS [C2],
54
    //    [Except1].[ListPrice] AS [C3]
55
    //    FROM  (SELECT
56
    //        1 AS [C1],
57
    //        [Extent1].[Name] AS [Name],
58
    //        [Extent1].[ListPrice] AS [ListPrice]
59
    //        FROM [Production].[Product] AS [Extent1]
60
    //        WHERE [Extent1].[ListPrice] > cast(100 as decimal(18))
61
    //    EXCEPT
62
    //        SELECT
63
    //        1 AS [C1],
64
    //        [Extent2].[Name] AS [Name],
65
    //        [Extent2].[ListPrice] AS [ListPrice]
66
    //        FROM [Production].[Product] AS [Extent2]
67
    //        WHERE [Extent2].[ListPrice] > cast(2000 as decimal(18))) AS [Except1]
68
#else
69
    // SELECT [product0].[Name], [product0].[ListPrice]
70
    // FROM [Production].[Product] AS [product0]
71
    // WHERE [product0].[ListPrice] > 2000.0
72

73
    // SELECT [product].[Name], [product].[ListPrice]
74
    // FROM [Production].[Product] AS [product]
75
    // WHERE [product].[ListPrice] > 100.0
76
#endif
77
}

Partitioning#

Skip is translate to OFFSET filter:

1
internal static void Skip(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IQueryable<string> names = source
5
        .Select(product => product.Name)
6
        .Skip(10); // Define query.
7
    names.WriteLines(); // Execute query.
8
    // exec sp_executesql N'SELECT [product].[Name]
9
    // FROM [Production].[Product] AS [product]
10
    // ORDER BY (SELECT 1)
11
    // OFFSET @__p_0 ROWS',N'@__p_0 int',@__p_0=10
12
}

In SQL, OFFSET is considered to be a part of the ORDER BY clause, so here EF Core generates ORDERBY (SELECT 1) clause.

EF does not automatically generate ORDER BY clause, and the above query throws NotSupportedException: The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’. The following is the equivalent query works in both EF Core and EF:
1
internal static void OrderByAndSkip(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IQueryable<string> names = source
5
        .Select(product => product.Name)
6
        .OrderBy(product => 1)
7
        .Skip(10); // Define query.
8
    names.WriteLines(); // Execute query.
9
}

When Take is called without Skip, it is translate to TOP filter:

1
internal static void Take(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IQueryable<string> products = source
5
        .Take(10)
6
        .Select(product => product.Name); // Define query.
7
    products.WriteLines(); // Execute query.
8
    // exec sp_executesql N'SELECT [t].[Name]
9
    // FROM (
10
    //    SELECT TOP(@__p_0) [p0].*
11
    //    FROM [Production].[Product] AS [p0]
12
    // ) AS [t]',N'@__p_0 int',@__p_0=10
13
}

When Take is called with Skip, they are translated to FETCH and OFFSET filters:

1
internal static void SkipAndTake(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IQueryable<string> products = source
5
        .OrderBy(product => product.Name)
6
        .Skip(20)
7
        .Take(10)
8
        .Select(product => product.Name); // Define query.
9
    products.WriteLines(); // Execute query.
10
    // exec sp_executesql N'SELECT [t].[Name]
11
    // FROM (
12
    //    SELECT [product0].*
13
    //    FROM [Production].[Product] AS [product0]
14
    //    ORDER BY [product0].[Name]
15
    //    OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
16
    // ) AS [t]',N'@__p_0 int,@__p_1 int',@__p_0=20,@__p_1=10
17
}

The above OrderBy is required for Skip and Take only in EF.

Ordering#

OrderBy/OrderByDescending are translated to ORDER BY clause with without/with DESC, for example:

1
internal static void OrderBy(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    var products = source
5
        .OrderBy(product => product.ListPrice)
6
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice }); // Define query.
7
    products.WriteLines(); // Execute query.
8
    // SELECT [product].[Name], [product].[ListPrice]
9
    // FROM [Production].[Product] AS [product]
10
    // ORDER BY [product].[ListPrice]
11
}
12

13
internal static void OrderByDescending(AdventureWorks adventureWorks)
14
{
15
    IQueryable<Product> source = adventureWorks.Products;
16
    var products = source
17
        .OrderByDescending(product => product.ListPrice)
18
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice }); // Define query.
19
    products.WriteLines(); // Execute query.
20
    // SELECT [product].[Name], [product].[ListPrice]
21
    // FROM [Production].[Product] AS [product]
22
    // ORDER BY [product].[ListPrice] DESC
23
}

To sort with multiple keys, call OrderBy/OrderByDescending and ThenBy/ThenByDescending:

1
internal static void OrderByAndThenBy(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    var products = source
5
        .OrderBy(product => product.ListPrice)
6
        .ThenBy(product => product.Name)
7
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice }); // Define query.
8
    products.WriteLines(); // Execute query.
9
    // SELECT [product].[Name], [product].[ListPrice]
10
    // FROM [Production].[Product] AS [product]
11
    // ORDER BY [product].[ListPrice], [product].[Name]
12
}

In EF Core, when the key selector returns anonymous type to sort by multiple keys, the sorting is executed locally:

1
internal static void OrderByMultipleKeys(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    var products = source
5
        .OrderBy(product => new { ListPrice = product.ListPrice, Name = product.Name })
6
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice }); // Define query.
7
    products.WriteLines(); // Execute query.
8
    // SELECT [product].[Name], [product].[ListPrice]
9
    // FROM [Production].[Product] AS [product]
10
    // ORDER BY (SELECT 1)
11
}

In EF, similar to GroupBy/Join/GroupJoin, the ordering query methods’ key selector returning anonymous type is properly translated. The above query is translated to:

1
SELECT
2
    [Project1].[C1] AS [C1],
3
    [Project1].[Name] AS [Name],
4
    [Project1].[ListPrice] AS [ListPrice]
5
    FROM ( SELECT
6
        [Extent1].[Name] AS [Name],
7
        [Extent1].[ListPrice] AS [ListPrice],
8
        1 AS [C1]
9
        FROM [Production].[Product] AS [Extent1]
10
    )  AS [Project1]
11
    ORDER BY [Project1].[ListPrice] ASC, [Project1].[Name] ASC

Multiple OrderBy/OrderByDescending calls are translated to SQL reversely. The following example sort all products by list price, then sort all products again by subcategory, which is equivalent to sort all products by subcategory first, then sort products in the same subcategory by list price:

1
internal static void OrderByAndOrderBy(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    var products = source
5
        .OrderBy(product => product.ListPrice)
6
        .OrderBy(product => product.ProductSubcategoryID)
7
        .Select(product => new
8
        {
9
            Name = product.Name,
10
            ListPrice = product.ListPrice,
11
            Subcategory = product.ProductSubcategoryID
12
        }); // Define query.
13
    products.WriteLines(); // Execute query.
14
    // SELECT [product].[Name], [product].[ListPrice], [product].[ProductSubcategoryID]
15
    // FROM [Production].[Product] AS [product]
16
    // ORDER BY [product].[ProductSubcategoryID], [product].[ListPrice]
17
}

In the above example, EF only translates the last OrderBy call, and ignore the others.

Conversion#

Cast can work with entity type. The following example casts base entity to derived entity:

1
internal static void CastEntity(AdventureWorks adventureWorks)
2
{
3
    IQueryable<TransactionHistory> source = adventureWorks.Transactions;
4
    IQueryable<TransactionHistory> transactions = source
5
        .Where(product => product.ActualCost > 500)
6
        .Cast<SalesTransactionHistory>(); // Define query.
7
    transactions.WriteLines(transaction =>
8
        $"{transaction.GetType().Name}: {transaction.TransactionDate}"); // Execute query.
9
    // SELECT [product].[TransactionID], [product].[ActualCost], [product].[ProductID], [product].[Quantity], [product].[TransactionDate], [product].[TransactionType]
10
    // FROM [Production].[TransactionHistory] AS [product]
11
    // WHERE [product].[TransactionType] IN (N'W', N'S', N'P') AND ([product].[ActualCost] > 500.0)
12
}

EF Core does not support Cast for primitive type.

EF does not support casting entity type. The above query throws NotSupportedException: Unable to cast the type ‘TransactionHistory’ to type ‘SalesTransactionHistory’. LINQ to Entities only supports casting EDM primitive or enumeration types. EF supports casting primitive type. The following example casts decimal to string, which is translated to CAST function call, casting money to (nvarchar(MAX)):
1
internal static void CastPrimitive(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IQueryable<string> listPrices = source
5
        .Select(product => product.ListPrice)
6
        .Cast<string>(); // Define query.
7
    listPrices.WriteLines(); // Execute query.
8
#if EF
9
    // SELECT
10
    //     CAST( [Extent1].[ListPrice] AS nvarchar(max)) AS [C1]
11
    //    FROM [Production].[Product] AS [Extent1]
12
#else
13
    // InvalidOperationException: No coercion operator is defined between types 'System.Decimal' and 'System.String'.
14
#endif
15
}

Queryable has a new query method, AsQueryable, which accepts IEnumerable and returns IQueryable. Remember Enumerable.AsEnumerable can convert more derived sequence (like List, IQueryable, etc.) to IEnumerable. So the Queryable.AsQueryable/Eumerable.AsEnumerable methods look familiar to the ParallelEnumerable.AsParallel/ParallelEnumerable.AsSequential methods, which convert between sequential and parallel local queries at any point. However, AsQueryable/AsEnumerable usually do not convert freely between local and remote queries. The following is the implementation of AsEnumerable and AsQueryable:

1
namespace System.Linq
2
{
3
    public static class Enumerable
4
    {
5
        public static IEnumerable<TSource> AsEnumerable<TSource>(this IEnumerable<TSource> source) => source;
6
    }
7

8
    public static class Queryable
9
    {
10
        public static IQueryable<TElement> AsQueryable<TElement>(this IEnumerable<TElement> source) =>
11
            source as IQueryable<TElement> ?? new EnumerableQuery<TElement>(source);
12
    }
13
}

AsQueryable accepts an IEnumerable source. If the source is indeed an IQueryable source, then do nothing and just return it; if not, wrap the source into an System.Linq.EnumerableQuery instance, and return it. EnumerableQuery is a special implementation of IQueryable. If an IQueryable query is an EnumerableQuery instance, when this query is executed, it internally calls System.Linq.EnumerableRewriter to translate itself to local query, then execute the translated query locally. For example, AdventureWorks.Products return IQueryable, which is actually a DbSet instance, so calling AsQueryable with AdventureWorks.Products does nothing and returns the DbSet instance itself, which can have its following query method calls to be translated to SQL by EF Core. In contrast, calling AsQueryable with a T[] array returns an EnumerableQuery wrapper, which is a local mocking of remote query and can have its following query methods to be translated to local queries, As a result, AsEnumerable can always convert a remote LINQ to Entities query to local LINQ to Objects query, but AsQueryable cannot always convert arbitrary local LINQ to Objects query to a remote LINQ to Entities query (and logically, an arbitrary local .NET data source cannot be converted to a remote data source like SQL database). For example:

1
internal static void AsEnumerableAsQueryable(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    var remoteAndLocal = source // DbSet<T>.
5
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice }) // Return EntityQueryable<T>.
6
        .AsEnumerable() // Do nothing. Directly return the EntityQueryable<T> source.
7
        .Where(product => product.ListPrice > 0) // Enumerable.Where. Return a generator wrapping the EntityQueryable<T> source.
8
        .AsQueryable() // Return an EnumerableQuery<T> instance wrapping the source generator.
9
        .OrderBy(product => product.Name); // Queryable.OrderBy. Return EnumerableQuery<T>.
10
    remoteAndLocal.WriteLines();
11
    // SELECT [product].[Name], [product].[ListPrice]
12
    // FROM [Production].[Product] AS [product]
13

14
    var remote = source // DbSet<T>.
15
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice }) // Return EntityQueryable<T>.
16
        .AsEnumerable() // Do nothing. Directly return the EntityQueryable<T> source.
17
        .AsQueryable() // Do nothing. Directly return the EntityQueryable<T> source.
18
        .Where(product => product.ListPrice > 0) // Still LINQ to Entities. Return EntityQueryable<T>.
19
        .OrderBy(product => product.Name); // Still LINQ to Entities. Return EntityQueryable<T>.
20
    remote.WriteLines();
21
    // SELECT [product].[Name], [product].[ListPrice]
22
    // FROM [Production].[Product] AS [product]
23
    // WHERE [product].[ListPrice] > 0.0
24
    // ORDER BY [product].[Name]
25
}

In the first query, the LINQ to Entities source is chained with Select, then AsEnumerable returns IEnumerable, so the following Where is Enumerable.Where, and it returns a generator. Then AsQueryable detects if the generator is IQueryable. Since the generator is not IQueryable, AsQueryable returns a EnumerableQuery wrapper, which can have the following OrderBy translated to local query. So in this entire query chaining, only Select, which is before AsEnumerable, can be translated to SQL and executed remotely, all the other query methods are executed locally.

The source is a DbSet instance, which implements IQueryable and represents the LINQ to Entities data source - rows in remote SQL database table.
Queryable.Select is called on DbSet source, in this case it returns a Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable instance in EF Core (System.Data.Entity.DbQuery instance in EF), which implements IQueryable and represents LINQ to Entities query.
Enumerable.AsEnumerable does nothing and directly returns its source, the EntityQueryable (DbQuery for EF) instance
Enumerable.Where is called, since AsEnumerable returns IEnumerable type. Where returns a generator wrapping its source, the EntityQueryable (DbQuery for EF) instance.
Queryable.AsQueryable is called. Its source, the generator from Where, implements IEnumerable, not IQueryable, so AsQueryable return an EnumerableQuery instance wrapping the generator. As fore mentioned, EnumerableQuery has nothing to do with database.
Queryable.OrderBy is called with EnumerableQuery instance, in this case it returns another EnumerableQuery instance, which has nothing to do with database either.

So the first query is a hybrid query. When it is executed, only Select is remote LINQ to Entities query and is translated to SQL. After AsEnumerable, Where goes local, then AsQueryable cannot convert back to remote LINQ to Entities query anymore. So, Where and OrderBy are both local queries, and not translated to SQL.

The second query is a special case, where AsEnumerable is chained with AsQueryable right away. In this case, AsEnumerable and AsQueryable both do nothing at all. The following Where and OrderBy are both LINQ to Entities queries, and translated to SQL along with Select.

In EF, AsEnumerable can be useful for special case. As fore mentioned, in EF, Select does not support entity type. With AsEnumerable, this can be done with LINQ to Objects:

1
internal static void SelectLocalEntity(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    IEnumerable<Product> products = source
5
        .Where(product => product.ListPrice > 1_000) // LINQ to Entities.
6
        .AsEnumerable() // Do nothing.
7
        .Select(product => new Product()
8
        {
9
            ProductID = product.ProductID,
10
            Name = product.Name
11
        }); // LINQ to Objects: Enumerable.Select>. Return a generator.
12
    products.WriteLines(product => $"{product.ProductID}: {product.Name}"); // Execute query.
13
    // SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]
14
    // FROM [Production].[Product] AS [product]
15
    // WHERE [product].[ListPrice] > 1000.0
16
}

Value query#

Query methods in this category accepts an IQueryable source and returns a single value. When they are called at the end of a LINQ to Entities query, they executes the query immediately.

In EF, value queries can be used in the subqueries of the above sequence queries. For example, as demonstrated above, the aggregation subquery of GroupBy flattens hierarchical data, which is translated to SQL aggregation function with GROUP BY.

Element#

First and FirstOrDefault execute the LINQ to Entities queries immediately. They are translated to TOP(1) filter in the SELECT clause. If a predicate is provided, the predicate is translated to WHERE clause. For example:

1
internal static void First(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    string first = source
5
        .Select(product => product.Name)
6
        .First() // Execute query.
7
        .WriteLine();
8
    // SELECT TOP(1) [product].[Name]
9
    // FROM [Production].[Product] AS [product]
10
}
11

12
internal static void FirstOrDefault(AdventureWorks adventureWorks)
13
{
14
    IQueryable<Product> source = adventureWorks.Products;
15
    var firstOrDefault = source
16
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice })
17
        .FirstOrDefault(product => product.ListPrice > 5000); // Execute query.
18
    firstOrDefault?.Name.WriteLine();
19
    // SELECT TOP(1) [product].[Name], [product].[ListPrice]
20
    // FROM [Production].[Product] AS [product]
21
    // WHERE [product].[ListPrice] > 5000.0
22
}

As discussed in LINQ to Objects, Single and SingleOrDefault are more strict. They are translated to TOP(2) filter, so that, if there are 0 or more than 1 results, InvalidOperationException is thrown. Similar to First and FirstOrDefault, if a predicate is provided, it is translated to WHERE clause:

1
internal static void Single(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    var single = source
5
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice })
6
        .Single(product => product.ListPrice < 50); // Execute query.
7
    $"{single.Name}: {single.ListPrice}".WriteLine();
8
    // SELECT TOP(2) [product].[Name], [product].[ListPrice]
9
    // FROM [Production].[Product] AS [product]
10
    // WHERE [product].[ListPrice] < 50.0
11
}
12

13
internal static void SingleOrDefault(AdventureWorks adventureWorks)
14
{
15
    IQueryable<Product> source = adventureWorks.Products;
16
    var singleOrDefault = source
17
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice })
18
        .SingleOrDefault(product => product.ListPrice < 1); // Execute query.
19
    singleOrDefault?.Name.WriteLine();
20
    // SELECT TOP(2) [product].[Name], [product].[ListPrice]
21
    // FROM [Production].[Product] AS [product]
22
    // WHERE [product].[ListPrice] < 1.0
23
}

EF Core supports Last and LastOrDefault, locally. Again, if a predicate is provided, it is translated to WHERE clause:

1
internal static void Last(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    Product last = source.Last(); // Execute query.
5
    // SELECT [p].[ProductID], [p].[ListPrice], [p].[Name], [p].[ProductSubcategoryID]
6
    // FROM [Production].[Product] AS [p]
7
    $"{last.Name}: {last.ListPrice}".WriteLine();
8
}
9

10
internal static void LastOrDefault(AdventureWorks adventureWorks)
11
{
12
    IQueryable<Product> source = adventureWorks.Products;
13
    var lastOrDefault = source
14
        .Select(product => new { Name = product.Name, ListPrice = product.ListPrice })
15
        .LastOrDefault(product => product.ListPrice <= 0); // Execute query.
16
    // SELECT [product].[Name], [product].[ListPrice]
17
    // FROM [Production].[Product] AS [product]
18
    // WHERE [product].[ListPrice] <= 0.0
19
    (lastOrDefault == null).WriteLine(); // True
20
}

The above examples can read many results from remote database to locally, and try to query the last result locally, which can be a performance issue.

EF does not support Last or LastOrDefault, and the above queries throw NotSupportedException.

Aggregation#

Count/LongCount are translated to SQL aggregate functions COUNT/COUNT_BIG. if a is provided, it is translated to WHERE clause. The following examples query the System.Int32 count of categories, and the System.Int64 count of the products with list price greater than 0:

1
internal static void Count(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductCategory> source = adventureWorks.ProductCategories;
4
    int count = source.Count().WriteLine(); // Execute query.
5
    // SELECT COUNT(*)
6
    // FROM [Production].[ProductCategory] AS [p]
7
}
8

9
internal static void LongCount(AdventureWorks adventureWorks)
10
{
11
    IQueryable<Product> source = adventureWorks.Products;
12
    long longCount = source.LongCount(product => product.ListPrice > 0).WriteLine(); // Execute query.
13
    // SELECT COUNT_BIG(*)
14
    // FROM [Production].[Product] AS [product]
15
    // WHERE [product].[ListPrice] > 0.0
16
}

Max/Min/Sum are translated to MAX/MIN/SUM functions. The following examples query the latest ModifiedDate of photos, the lowest list price of products, and the total cost of transactions:

1
internal static void Max(AdventureWorks adventureWorks)
2
{
3
    IQueryable<ProductPhoto> source = adventureWorks.ProductPhotos;
4
    DateTime max = source.Select(photo => photo.ModifiedDate).Max().WriteLine(); // Execute query.
5
    // SELECT MAX([photo].[ModifiedDate])
6
    // FROM [Production].[ProductPhoto] AS [photo]
7
}
8

9
internal static void Min(AdventureWorks adventureWorks)
10
{
11
    IQueryable<Product> source = adventureWorks.Products;
12
    decimal min = source.Min(product => product.ListPrice).WriteLine(); // Execute query.
13
    // SELECT MIN([product].[ListPrice])
14
    // FROM [Production].[Product] AS [product]
15
}
16

17
internal static void Sum(AdventureWorks adventureWorks)
18
{
19
    IQueryable<TransactionHistory> source = adventureWorks.Transactions;
20
    decimal sum = source.Sum(transaction => transaction.ActualCost).WriteLine(); // Execute query.
21
    // SELECT SUM([transaction].[ActualCost])
22
    // FROM [Production].[TransactionHistory] AS [transaction]
23
    // WHERE ([transaction].[TransactionType] = N'W') OR (([transaction].[TransactionType] = N'S') OR ([transaction].[TransactionType] = N'P'))
24
}

EF Core support Average locally.

1
internal static void Average(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    decimal average = source.Select(product => product.ListPrice).Average().WriteLine(); // Execute query.
5
    // SELECT [product].[ListPrice]
6
    // FROM [Production].[Product] AS [product]
7
}

EF translates Average to AVG function. In EF, the above query is translated to:

1
SELECT
2
    [GroupBy1].[A1] AS [C1]
3
    FROM ( SELECT
4
        AVG([Extent1].[ListPrice]) AS [A1]
5
        FROM [Production].[Product] AS [Extent1]
6
    )  AS [GroupBy1]

Quantifier#

EF Core supports Contains for entity type, locally.

1
internal static void ContainsEntity(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    Product single = source.Single(product => product.ListPrice == 20.24M); // Execute query.
5
    // SELECT TOP(2) [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]
6
    // FROM [Production].[Product] AS [product]
7
    // WHERE [product].[ListPrice] = 20.24
8
    bool contains = source
9
        .Where(product => product.ProductSubcategoryID == 7)
10
        .Contains(single).WriteLine(); // Execute query.
11
    // SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]
12
    // FROM [Production].[Product] AS [product]
13
    // WHERE [product].[ProductSubcategoryID] = 7
14
}

EF does not support Contains for entity type. The above query throws NotSupportedException: Unable to create a constant value of type ‘Product’. Only primitive types or enumeration types are supported in this context.

EF/Core both support Contains for primitive types. In this case, Contains is translated to EXISTS predicate:

1
internal static void ContainsPrimitive(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    bool contains = source
5
        .Select(product => product.ListPrice).Contains(100)
6
        .WriteLine(); // Execute query.
7
    // SELECT CASE
8
    //    WHEN EXISTS (
9
    //        SELECT 1
10
    //        FROM [Production].[Product] AS [product]
11
    //        WHERE [product].[ListPrice] = 100.0)
12
    //    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
13
    // END
14
}

Any is also translated to EXISTS. If predicate is provided, it is translated to WHERE clause:

1
internal static void Any(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    bool any = source.Any().WriteLine(); // Execute query.
5
    // SELECT CASE
6
    //    WHEN EXISTS (
7
    //        SELECT 1
8
    //        FROM [Production].[Product] AS [p])
9
    //    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
10
    // END
11
}
12

13
internal static void AnyWithPredicate(AdventureWorks adventureWorks)
14
{
15
    IQueryable<Product> source = adventureWorks.Products;
16
    bool any = source.Any(product => product.ListPrice > 10).WriteLine(); // Execute query.
17
    // SELECT CASE
18
    //    WHEN EXISTS (
19
    //        SELECT 1
20
    //        FROM [Production].[Product] AS [product]
21
    //        WHERE [product].[ListPrice] > 10.0)
22
    //    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
23
    // END
24
}

All is translated to NOT EXISTS, with the predicate translated to reverted condition in WHERE clause:

1
internal static void AllWithPredicate(AdventureWorks adventureWorks)
2
{
3
    IQueryable<Product> source = adventureWorks.Products;
4
    bool all = source.All(product => product.ListPrice > 10).WriteLine(); // Execute query.
5
    // SELECT CASE
6
    //    WHEN NOT EXISTS (
7
    //        SELECT 1
8
    //        FROM [Production].[Product] AS [product]
9
    //        WHERE [product].[ListPrice] <= 10.0)
10
    //    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
11
    // END
12
}

[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-4-query-methods #

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

Sequence queries#

Filtering (restriction)#

Mapping (projection)#

Generation#

Grouping#

Join#

Inner join#

Left outer join#

Cross join#

Concatenation#

Set#

Partitioning#

Ordering#

Conversion#

Value query#

Element#

Aggregation#

Quantifier#

[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-4-query-methods#

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

Sequence queries#

Filtering (restriction)#

Mapping (projection)#

Generation#

Grouping#

Join#

Inner join#

Left outer join#

Cross join#

Concatenation#

Set#

Partitioning#

Ordering#

Conversion#

Value query#

Element#

Aggregation#

Quantifier#

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

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