public async Task IncludeQuery()
{
// Include query creates two database calls
var result0 = await _context.Suppliers
.Include(a => a.Products)
.ToListAsync();
Console.WriteLine("=================================");
// This will generate only one call
var query = from a in _context.Suppliers
join b in _context.Products on a.SupplierId equals b.SupplierId
select new { a, b };
var result1 = await query.ToListAsync();
Console.WriteLine("=================================");
// This is the method call equivalent of the above query syntax.
// Also works the same, the query syntax is much more readable
var query2 = _context.Suppliers
.Join(_context.Products, a => (Int32?)(a.SupplierId),
b => b.SupplierId,
(a, b) => a);
var result2 = await query2.ToListAsync();
Console.WriteLine("=================================");
// This generates a left join SQL statement
var query3 = from a in _context.Suppliers
join b in _context.Products on a.SupplierId equals b.SupplierId
into MatchProducts
from mo in MatchProducts.DefaultIfEmpty()
select MatchProducts;
var result3 = await query3.ToListAsync();
Console.WriteLine("=================================");
// Get all Suppliers with no product
var query4 = from a in _context.Suppliers
join b in _context.Products on a.SupplierId equals b.SupplierId
into MatchProducts
from mo in MatchProducts.DefaultIfEmpty()
where mo == null
select MatchProducts;
var result4 = await query4.ToListAsync();
}
/// <summary>
/// Use to test and make sure that expressions will evaluate on the server rather than on the client
/// </summary>
public async Task TestServerSideEvaluationAsync()
{
var queryV1 = _context.Suppliers
.Include(x => x.Products)
.ThenInclude(y => y.Category)
.Select(z => new { z.CompanyName, z.Products } );
//var result = await queryV1.ToListAsync();
/*
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (120ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [x].[CompanyName], [x].[SupplierID]
FROM [Suppliers] AS [x]
ORDER BY [x].[SupplierID]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (60ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [x.Products].[ProductID], [x.Products].[CategoryID], [x.Products].[Discontinued], [x.Products].[ProductName], [x.Products].[QuantityPerUnit], [x.Products].[ReorderLevel], [x.Products].[SupplierID], [x.Products].[UnitPrice], [x.Products].[UnitsInStock], [x.Products].[UnitsOnOrder], [t].[SupplierID]
FROM [Products] AS [x.Products]
INNER JOIN (
SELECT [x0].[SupplierID]
FROM [Suppliers] AS [x0]
) AS [t] ON [x.Products].[SupplierID] = [t].[SupplierID]
ORDER BY [t].[SupplierID]
*/
var queryV2 = _context.Suppliers
.SelectMany(x => x.Products.Select(p => new { p.ProductId, p.ProductName, p.Category }));
// var resultV2 = await queryV2.ToListAsync();
var queryV3 = from a in _context.Suppliers
join b in _context.Products on a.SupplierId equals b.SupplierId
into MatchProducts
from mo in MatchProducts.DefaultIfEmpty()
join c in _context.Categories on mo.CategoryId equals c.CategoryId
into MatchCategories
from mc in MatchCategories.DefaultIfEmpty()
select new { a.SupplierId, a.CompanyName, mo.ProductId, mo.ProductName, mc.CategoryId, CategoryNameDesc = mc.CategoryName + "\n" + mc.CategoryName };
var resultV3 = await queryV3.ToListAsync();
/*
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (86ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [a].[SupplierID], [a].[CompanyName], [b].[ProductID] AS [ProductId], [b].[ProductName], [c].[CategoryID] AS [CategoryId], [c].[CategoryName]
FROM [Suppliers] AS [a]
LEFT JOIN [Products] AS [b] ON [a].[SupplierID] = [b].[SupplierID]
LEFT JOIN [Categories] AS [c] ON [b].[CategoryID] = [c].[CategoryID]
WHERE [c].[CategoryName] = N'Seafood'
*/
var queryV4 = from a in _context.Suppliers
join b in _context.Products on a.SupplierId equals b.SupplierId
into MatchProducts
from mo in MatchProducts.DefaultIfEmpty().Take(1)
join c in _context.Categories on mo.CategoryId equals c.CategoryId
into MatchCategories
from mc in MatchCategories.DefaultIfEmpty()
select new { a.SupplierId, a.CompanyName, mo.ProductId, mo.ProductName, mc.CategoryId, mc.CategoryName };
// var resultV4 = await queryV4.Where(x => x.CategoryName == "Seafood").ToListAsync();
/*
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (107ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [a].[SupplierID] AS [SupplierId0], [a].[Address], [a].[City], [a].[CompanyName], [a].[ContactName], [a].[ContactTitle], [a].[Country], [a].[Fax], [a].[HomePage], [a].[Phone], [a].[PostalCode], [a].[Region], [b].[ProductID], [b].[CategoryID], [b].[Discontinued], [b].[ProductName], [b].[QuantityPerUnit], [b].[ReorderLevel], [b].[SupplierID], [b].[UnitPrice], [b].[UnitsInStock], [b].[UnitsOnOrder]
FROM [Suppliers] AS [a]
LEFT JOIN [Products] AS [b] ON [a].[SupplierID] = [b].[SupplierID]
ORDER BY [SupplierId0]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (18ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [c].[CategoryID], [c].[CategoryName]
FROM [Categories] AS [c]
*/
}