1. 一对多
var expr = context.Products
.Where(p => p.Category.CategoryName == "LINQ to SQL" && p.UnitPrice > 10m)
.Select(p => new
{
p.ProductID,
p.ProductName
});
var expr = from p in context.Products
where p.Category.CategoryName == "LINQ to SQL" && p.UnitPrice > 10m
select new
{
p.ProductID,
p.ProductName
};
SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName]
FROM [dbo].[Product] AS [Extent1]
INNER JOIN [dbo].[Category] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
WHERE (N'LINQ to SQL' = [Extent2].[CategoryName]) AND ([Extent1].[UnitPrice] > cast(10 as decimal(18)))
var expr = from p in context.Products
where p.UnitPrice > 10m
join c in context.Categories on p.CategoryID equals c.CategoryID
select new
{
p.ProductID,
p.ProductName,
c.CategoryName
};
SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent2].[CategoryName] AS [CategoryName]
FROM [dbo].[Product] AS [Extent1]
INNER JOIN [dbo].[Category] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
WHERE [Extent1].[UnitPrice] > cast(10 as decimal(18))
var expr = from p in context.Products
join c in context.Categories on p.CategoryID equals c.CategoryID
select new
{
p.ProductID,
p.ProductName,
c.CategoryName
};
SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent2].[CategoryName] AS [CategoryName]
FROM [dbo].[Product] AS [Extent1]
INNER JOIN [dbo].[Category] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
2. 双向关联
var expr = from c in context.Categories
join p in context.Products on c.CategoryID equals p.CategoryID into cp
select new
{
c.CategoryName,
TotalCategoryProducts = cp.Count()
};
SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Product] AS [Extent2]
WHERE [Extent1].[CategoryID] = [Extent2].[CategoryID]) AS [C1]
FROM [dbo].[Category] AS [Extent1]
3. left out join
var expr = from c in context.Categories
join p in context.Products on c.CategoryID equals p.CategoryID into CategoryProducts
from cp in CategoryProducts.DefaultIfEmpty()
select new
{
c.CategoryID,
c.CategoryName,
TotalProducts = CategoryProducts.Count()
};
SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Product] AS [Extent3]
WHERE [Extent1].[CategoryID] = [Extent3].[CategoryID]) AS [C1]
FROM [dbo].[Category] AS [Extent1]
LEFT OUTER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
4. inner join select many
var expr = from c in context.Categories
from p in c.Products
where c.CategoryName == "LINQ to SQL"
select p;
var expr = context.Categories
.Where(c => c.CategoryName == "LINQ to SQL")
.SelectMany(c => c.Products);
SELECT
[Extent2].[ProductID] AS [ProductID],
[Extent2].[CategoryID] AS [CategoryID],
[Extent2].[ProductName] AS [ProductName],
[Extent2].[UnitPrice] AS [UnitPrice],
[Extent2].[UnitsInStock] AS [UnitsInStock],
[Extent2].[Discontinued] AS [Discontinued]
FROM [dbo].[Category] AS [Extent1]
INNER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
WHERE N'LINQ to SQL' = [Extent1].[CategoryName]