Use Impatient with EF Core for SQL Server to get the most out of LINQ queries.

C#

from o in os.Where(o => o.OrderID < 10400)
join i in (from c in cs
            join a in os.GroupBy(o => o.CustomerID)
                        .Where(g => g.Count() &gt; 5)
                        .Select(g => new { CustomerID = g.Key, LastOrderID = g.Max(o => o.OrderID) })
                on c.CustomerID equals a.CustomerID
            select new { c, a.LastOrderID })
    on o.CustomerID equals i.c.CustomerID
select new { o, i.c, i.c.CustomerID }

EF Core

SELECT [c0].[CustomerID], [c0].[Address], [c0].[City], [c0].[CompanyName], [c0].[ContactName], [c0].[ContactTitle], [c0].[Country], [c0].[Fax], [c0].[Phone], [c0].[PostalCode], [c0].[Region], [t0].[CustomerID], [t0].[LastOrderID]
FROM [Customers] AS [c0]
INNER JOIN (
    SELECT [o1].[CustomerID], MAX([o1].[OrderID]) AS [LastOrderID]
    FROM [Orders] AS [o1]
    GROUP BY [o1].[CustomerID]
    HAVING COUNT(*) > 5
) AS [t0] ON [c0].[CustomerID] = [t0].[CustomerID]

-- MULTIPLE QUERIES, HUGE MANATEES

SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[OrderID] < 10400

Impatient

SELECT [o].[OrderID] AS [o.OrderID], [o].[CustomerID] AS [o.CustomerID], [o].[EmployeeID] AS [o.EmployeeID], [o].[OrderDate] AS [o.OrderDate], [i].[c.CustomerID] AS [c.CustomerID], [i].[c.Address] AS [c.Address], [i].[c.City] AS [c.City], [i].[c.CompanyName] AS [c.CompanyName], [i].[c.ContactName] AS [c.ContactName], [i].[c.ContactTitle] AS [c.ContactTitle], [i].[c.Country] AS [c.Country], [i].[c.Fax] AS [c.Fax], [i].[c.Phone] AS [c.Phone], [i].[c.PostalCode] AS [c.PostalCode], [i].[c.Region] AS [c.Region], [i].[c.CustomerID] AS [CustomerID]
FROM [Orders] AS [o]
INNER JOIN (
    SELECT [c].[CustomerID] AS [c.CustomerID], [c].[Address] AS [c.Address], [c].[City] AS [c.City], [c].[CompanyName] AS [c.CompanyName], [c].[ContactName] AS [c.ContactName], [c].[ContactTitle] AS [c.ContactTitle], [c].[Country] AS [c.Country], [c].[Fax] AS [c.Fax], [c].[Phone] AS [c.Phone], [c].[PostalCode] AS [c.PostalCode], [c].[Region] AS [c.Region], [a].[LastOrderID] AS [LastOrderID]
    FROM [Customers] AS [c]
    INNER JOIN (
        SELECT [g].[Key] AS [CustomerID], (
            SELECT MAX([g_0].[OrderID])
            FROM [Orders] AS [g_0]
            WHERE (([g].[Key] IS NULL AND [g_0].[CustomerID] IS NULL) OR ([g].[Key] = [g_0].[CustomerID]))
        ) AS [LastOrderID]
        FROM (
            SELECT [g_1].[CustomerID] AS [Key]
            FROM [Orders] AS [g_1]
            GROUP BY [g_1].[CustomerID]
        ) AS [g]
        WHERE (
            SELECT COUNT(*)
            FROM [Orders] AS [g_0]
            WHERE (([g].[Key] IS NULL AND [g_0].[CustomerID] IS NULL) OR ([g].[Key] = [g_0].[CustomerID]))
        ) > 5
    ) AS [a] ON [c].[CustomerID] = [a].[CustomerID]
) AS [i] ON [o].[CustomerID] = [i].[c.CustomerID]
WHERE [o].[OrderID] < 10400

Impatient speaks `JSON`, so you can fetch dense result sets in one trip.

C#

from e in employees
select new
{
    e.FirstName,
    e.LastName,
    RecentOrders = 
        (from o in e.Orders
         orderby o.OrderDate descending
         select o).Take(5).ToList(),
    TopCustomers = 
        (from o in e.Orders
         from d in o.OrderDetails
         group d by o.Customer into g
         let total = g.Sum(d => d.UnitPrice * d.Quantity * (decimal)(1 - d.Discount))
         orderby total descending
         select new
         {
             g.Key.ContactName,
             g.Key.CompanyName,
             total
         }).Take(5).ToList()
}

EF Core

SELECT [e].[FirstName], [e].[LastName], [e].[EmployeeID]
FROM [Employees] AS [e]

@_outer_EmployeeID='1'

SELECT TOP(5) [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[EmployeeID] = @_outer_EmployeeID
ORDER BY [o].[OrderDate] DESC

@_outer_EmployeeID2='1'

SELECT [o.Customer0].[CustomerID], [o.Customer0].[Address], [o.Customer0].[City], [o.Customer0].[CompanyName], [o.Customer0].[ContactName], [o.Customer0].[ContactTitle], [o.Customer0].[Country], [o.Customer0].[Fax], [o.Customer0].[Phone], [o.Customer0].[PostalCode], [o.Customer0].[Region], [o.OrderDetails0].[OrderID], [o.OrderDetails0].[ProductID], [o.OrderDetails0].[Discount], [o.OrderDetails0].[Quantity], [o.OrderDetails0].[UnitPrice]
FROM [Orders] AS [o1]
LEFT JOIN [Customers] AS [o.Customer0] ON [o1].[CustomerID] = [o.Customer0].[CustomerID]
INNER JOIN [Order Details] AS [o.OrderDetails0] ON [o1].[OrderID] = [o.OrderDetails0].[OrderID]
WHERE [o1].[EmployeeID] = @_outer_EmployeeID2

@_outer_EmployeeID='2'

SELECT TOP(5) [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[EmployeeID] = @_outer_EmployeeID
ORDER BY [o].[OrderDate] DESC

@_outer_EmployeeID2='2'

SELECT [o.Customer0].[CustomerID], [o.Customer0].[Address], [o.Customer0].[City], [o.Customer0].[CompanyName], [o.Customer0].[ContactName], [o.Customer0].[ContactTitle], [o.Customer0].[Country], [o.Customer0].[Fax], [o.Customer0].[Phone], [o.Customer0].[PostalCode], [o.Customer0].[Region], [o.OrderDetails0].[OrderID], [o.OrderDetails0].[ProductID], [o.OrderDetails0].[Discount], [o.OrderDetails0].[Quantity], [o.OrderDetails0].[UnitPrice]
FROM [Orders] AS [o1]
LEFT JOIN [Customers] AS [o.Customer0] ON [o1].[CustomerID] = [o.Customer0].[CustomerID]
INNER JOIN [Order Details] AS [o.OrderDetails0] ON [o1].[OrderID] = [o.OrderDetails0].[OrderID]
WHERE [o1].[EmployeeID] = @_outer_EmployeeID2

@_outer_EmployeeID='3'

SELECT TOP(5) [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[EmployeeID] = @_outer_EmployeeID
ORDER BY [o].[OrderDate] DESC

@_outer_EmployeeID2='3'

SELECT [o.Customer0].[CustomerID], [o.Customer0].[Address], [o.Customer0].[City], [o.Customer0].[CompanyName], [o.Customer0].[ContactName], [o.Customer0].[ContactTitle], [o.Customer0].[Country], [o.Customer0].[Fax], [o.Customer0].[Phone], [o.Customer0].[PostalCode], [o.Customer0].[Region], [o.OrderDetails0].[OrderID], [o.OrderDetails0].[ProductID], [o.OrderDetails0].[Discount], [o.OrderDetails0].[Quantity], [o.OrderDetails0].[UnitPrice]
FROM [Orders] AS [o1]
LEFT JOIN [Customers] AS [o.Customer0] ON [o1].[CustomerID] = [o.Customer0].[CustomerID]
INNER JOIN [Order Details] AS [o.OrderDetails0] ON [o1].[OrderID] = [o.OrderDetails0].[OrderID]
WHERE [o1].[EmployeeID] = @_outer_EmployeeID2

@_outer_EmployeeID='4'

SELECT TOP(5) [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[EmployeeID] = @_outer_EmployeeID
ORDER BY [o].[OrderDate] DESC

@_outer_EmployeeID2='4'

SELECT [o.Customer0].[CustomerID], [o.Customer0].[Address], [o.Customer0].[City], [o.Customer0].[CompanyName], [o.Customer0].[ContactName], [o.Customer0].[ContactTitle], [o.Customer0].[Country], [o.Customer0].[Fax], [o.Customer0].[Phone], [o.Customer0].[PostalCode], [o.Customer0].[Region], [o.OrderDetails0].[OrderID], [o.OrderDetails0].[ProductID], [o.OrderDetails0].[Discount], [o.OrderDetails0].[Quantity], [o.OrderDetails0].[UnitPrice]
FROM [Orders] AS [o1]
LEFT JOIN [Customers] AS [o.Customer0] ON [o1].[CustomerID] = [o.Customer0].[CustomerID]
INNER JOIN [Order Details] AS [o.OrderDetails0] ON [o1].[OrderID] = [o.OrderDetails0].[OrderID]
WHERE [o1].[EmployeeID] = @_outer_EmployeeID2

@_outer_EmployeeID='5'

SELECT TOP(5) [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[EmployeeID] = @_outer_EmployeeID
ORDER BY [o].[OrderDate] DESC

@_outer_EmployeeID2='5'

SELECT [o.Customer0].[CustomerID], [o.Customer0].[Address], [o.Customer0].[City], [o.Customer0].[CompanyName], [o.Customer0].[ContactName], [o.Customer0].[ContactTitle], [o.Customer0].[Country], [o.Customer0].[Fax], [o.Customer0].[Phone], [o.Customer0].[PostalCode], [o.Customer0].[Region], [o.OrderDetails0].[OrderID], [o.OrderDetails0].[ProductID], [o.OrderDetails0].[Discount], [o.OrderDetails0].[Quantity], [o.OrderDetails0].[UnitPrice]
FROM [Orders] AS [o1]
LEFT JOIN [Customers] AS [o.Customer0] ON [o1].[CustomerID] = [o.Customer0].[CustomerID]
INNER JOIN [Order Details] AS [o.OrderDetails0] ON [o1].[OrderID] = [o.OrderDetails0].[OrderID]
WHERE [o1].[EmployeeID] = @_outer_EmployeeID2

@_outer_EmployeeID='6'

SELECT TOP(5) [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[EmployeeID] = @_outer_EmployeeID
ORDER BY [o].[OrderDate] DESC

@_outer_EmployeeID2='6'

SELECT [o.Customer0].[CustomerID], [o.Customer0].[Address], [o.Customer0].[City], [o.Customer0].[CompanyName], [o.Customer0].[ContactName], [o.Customer0].[ContactTitle], [o.Customer0].[Country], [o.Customer0].[Fax], [o.Customer0].[Phone], [o.Customer0].[PostalCode], [o.Customer0].[Region], [o.OrderDetails0].[OrderID], [o.OrderDetails0].[ProductID], [o.OrderDetails0].[Discount], [o.OrderDetails0].[Quantity], [o.OrderDetails0].[UnitPrice]
FROM [Orders] AS [o1]
LEFT JOIN [Customers] AS [o.Customer0] ON [o1].[CustomerID] = [o.Customer0].[CustomerID]
INNER JOIN [Order Details] AS [o.OrderDetails0] ON [o1].[OrderID] = [o.OrderDetails0].[OrderID]
WHERE [o1].[EmployeeID] = @_outer_EmployeeID2

@_outer_EmployeeID='7'

SELECT TOP(5) [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[EmployeeID] = @_outer_EmployeeID
ORDER BY [o].[OrderDate] DESC

@_outer_EmployeeID2='7'

SELECT [o.Customer0].[CustomerID], [o.Customer0].[Address], [o.Customer0].[City], [o.Customer0].[CompanyName], [o.Customer0].[ContactName], [o.Customer0].[ContactTitle], [o.Customer0].[Country], [o.Customer0].[Fax], [o.Customer0].[Phone], [o.Customer0].[PostalCode], [o.Customer0].[Region], [o.OrderDetails0].[OrderID], [o.OrderDetails0].[ProductID], [o.OrderDetails0].[Discount], [o.OrderDetails0].[Quantity], [o.OrderDetails0].[UnitPrice]
FROM [Orders] AS [o1]
LEFT JOIN [Customers] AS [o.Customer0] ON [o1].[CustomerID] = [o.Customer0].[CustomerID]
INNER JOIN [Order Details] AS [o.OrderDetails0] ON [o1].[OrderID] = [o.OrderDetails0].[OrderID]
WHERE [o1].[EmployeeID] = @_outer_EmployeeID2

@_outer_EmployeeID='8'

SELECT TOP(5) [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[EmployeeID] = @_outer_EmployeeID
ORDER BY [o].[OrderDate] DESC

@_outer_EmployeeID2='8'

SELECT [o.Customer0].[CustomerID], [o.Customer0].[Address], [o.Customer0].[City], [o.Customer0].[CompanyName], [o.Customer0].[ContactName], [o.Customer0].[ContactTitle], [o.Customer0].[Country], [o.Customer0].[Fax], [o.Customer0].[Phone], [o.Customer0].[PostalCode], [o.Customer0].[Region], [o.OrderDetails0].[OrderID], [o.OrderDetails0].[ProductID], [o.OrderDetails0].[Discount], [o.OrderDetails0].[Quantity], [o.OrderDetails0].[UnitPrice]
FROM [Orders] AS [o1]
LEFT JOIN [Customers] AS [o.Customer0] ON [o1].[CustomerID] = [o.Customer0].[CustomerID]
INNER JOIN [Order Details] AS [o.OrderDetails0] ON [o1].[OrderID] = [o.OrderDetails0].[OrderID]
WHERE [o1].[EmployeeID] = @_outer_EmployeeID2

@_outer_EmployeeID='9'

SELECT TOP(5) [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[EmployeeID] = @_outer_EmployeeID
ORDER BY [o].[OrderDate] DESC

@_outer_EmployeeID2='9'

SELECT [o.Customer0].[CustomerID], [o.Customer0].[Address], [o.Customer0].[City], [o.Customer0].[CompanyName], [o.Customer0].[ContactName], [o.Customer0].[ContactTitle], [o.Customer0].[Country], [o.Customer0].[Fax], [o.Customer0].[Phone], [o.Customer0].[PostalCode], [o.Customer0].[Region], [o.OrderDetails0].[OrderID], [o.OrderDetails0].[ProductID], [o.OrderDetails0].[Discount], [o.OrderDetails0].[Quantity], [o.OrderDetails0].[UnitPrice]
FROM [Orders] AS [o1]
LEFT JOIN [Customers] AS [o.Customer0] ON [o1].[CustomerID] = [o.Customer0].[CustomerID]
INNER JOIN [Order Details] AS [o.OrderDetails0] ON [o1].[OrderID] = [o.OrderDetails0].[OrderID]
WHERE [o1].[EmployeeID] = @_outer_EmployeeID2

Impatient

SELECT [e].[FirstName] AS [FirstName], [e].[LastName] AS [LastName], (
    SELECT TOP (5) [o].[OrderID] AS [OrderID], [o].[CustomerID] AS [CustomerID], [o].[EmployeeID] AS [EmployeeID], [o].[OrderDate] AS [OrderDate]
    FROM [Orders] AS [o]
    WHERE [o].[EmployeeID] = [e].[EmployeeID]
    ORDER BY [o].[OrderDate] DESC
    FOR JSON PATH
) AS [RecentOrders], (
    SELECT TOP (5) [c].[ContactName] AS [ContactName], [c].[CompanyName] AS [CompanyName], SUM(([o_0].[UnitPrice] * CAST([o_0].[Quantity] AS decimal(18, 2))) * CAST(1 - [o_0].[Discount] AS decimal(18, 2))) AS [total]
    FROM [Orders] AS [o_1]
    INNER JOIN [Order Details] AS [o_0] ON [o_1].[OrderID] = [o_0].[OrderID]
    LEFT JOIN (
        SELECT [c_0].[CustomerID] AS [CustomerID], [c_0].[Address] AS [Address], [c_0].[City] AS [City], [c_0].[CompanyName] AS [CompanyName], [c_0].[ContactName] AS [ContactName], [c_0].[ContactTitle] AS [ContactTitle], [c_0].[Country] AS [Country], [c_0].[Fax] AS [Fax], [c_0].[Phone] AS [Phone], [c_0].[PostalCode] AS [PostalCode], [c_0].[Region] AS [Region]
        FROM [Customers] AS [c_0]
    ) AS [c] ON [o_1].[CustomerID] = [c].[CustomerID]
    WHERE [o_1].[EmployeeID] = [e].[EmployeeID]
    GROUP BY [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
    ORDER BY SUM(([o_0].[UnitPrice] * CAST([o_0].[Quantity] AS decimal(18, 2))) * CAST(1 - [o_0].[Discount] AS decimal(18, 2))) DESC
    FOR JSON PATH
) AS [TopCustomers]
FROM [Employees] AS [e]

Impatient's got you covered, from `All` to `Zip`.

Translations for most operators

All of the query operators you would expect, as well as...
Select, SelectMany, and Where with index arguments
Concat, Except, Intersect, and Union
SkipWhile and TakeWhile, with or without index arguments
SequenceEqual and Zip
Even more, with fallback to client evaluation when translation is not possible

Support for database-native JSON

Materialize nested collections and complex-type columns within results instead of issuing n+1 queries
Filter on properties of JSON objects
Query against JSON arrays, treating them as if they were any other queryable sequence

Support for most EF Core 2.1 features

_____Async methods
Owned types
Table splitting
Include and ThenInclude
Query filters and IgnoreQueryFilters
Change tracking and AsTracking/AsNoTracking
DbFunction
Entity constructors with parameters
Lazy loading
Value conversions
Compiled queries
Query types
Defining queries

Use Impatient with EF Core for SQL Server to get the most out of LINQ queries.

C#

EF Core

Impatient

Impatient speaks JSON, so you can fetch dense result sets in one trip.

C#

EF Core

Impatient

Impatient's got you covered, from All to Zip.

Translations for most operators

Support for database-native JSON

Support for most EF Core 2.1 features

Impatient speaks `JSON`, so you can fetch dense result sets in one trip.

Impatient's got you covered, from `All` to `Zip`.