LINQ TO SQL 中join
LINQ TO SQL 中join一、LINQ to SQL中join的特点
1、包含join和on关键字，如果只有join没有on,会报语法错误。
2、外键关联时，用的是关键字equals,而不能像SQL一样用等号。
3、必须显示调用要显示的字段。即要出现select 字段，否则会报错。
二、LINQ to SQL中join实例
1、用join关联两张表
var q =
from c in db.Customers
join o in db.Orders on c.CustomerID
equals o.CustomerID into orders
select new
{
c.ContactName,
OrderCount = orders.Count()
};
生成SQL语句为：
SELECT [t0].[ContactName], (
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t1]
WHERE [t0].[CustomerID] = [t1].[CustomerID]
) AS [OrderCount]
FROM [dbo].[Customers] AS [t0]
实例2
var p = from u in entities.Customers
where u.City=="London"
join n in entities.Orders on u.CustomerID
equals n.CustomerID
into order //具有相同的customerid的orders为一组到order中
select new
{
u.CustomerID,
order,
Count=order.Count()
};
对这种方法的说明：如果我们要使用这种方法来遍历所有的记录，可以这样写：
foreach (var m in p)
{
foreach (var mm in m.order)
Console.WriteLine(m.CustomerID + "" +
mm.OrderID + " " + mm.Customers.City);
}
2、左外部联接(Left Outer Join)：通过使用DefaultIfEmpty() 获取左外部联接。
下例中，在雇员没有订单时，DefaultIfEmpty()方法返回null：
var q =
from e in db.Employees
join o in db.Orders on e equals o.Employee into ords
from o in ords.DefaultIfEmpty()
select new
{
e.FirstName,
e.LastName,
Order = o
};
3、多表联接
var q =
from c in db.Customers
join o in db.Orders on c.CustomerID
equals o.CustomerID into ords
join e in db.Employees on c.City
equals e.City into emps
select new
{
c.ContactName,
ords = ords.Count(),
emps = emps.Count()
};
生成的SQL语句
SELECT [t0].[ContactName], (
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t1]
WHERE [t0].[CustomerID] = [t1].[CustomerID]
) AS [ords], (
SELECT COUNT(*)
FROM [dbo].[Employees] AS [t2]
WHERE [t0].[City] = [t2].[City]
) AS [emps]
FROM [dbo].[Customers] AS [t0]
4、join组合键过滤
var q =
from o in db.Orders
from p in db.Products
join d in db.OrderDetails
on new
{
o.OrderID,
p.ProductID
} equals
new
{
d.OrderID,
d.ProductID
}
into details
from d in details
select new
{
o.OrderID,
p.ProductID,
d.UnitPrice
};
5、join中显示如何构造一侧可为 null 而另一侧不可为 null 的联接
var q =
from o in db.Orders
join e in db.Employees
on o.EmployeeID equals
(int?)e.EmployeeID into emps
from e in emps
select new
{
o.OrderID,
e.FirstName
};