您的位置：首页 > 编程学习 > Web > 正文

LINQ TO SQL 中join

更多时间：2014-11-27 类别：编程学习浏览量：1679

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
    };

标签：LINQ

上一篇：网页颜色的选择

下一篇：如何查看linq生成的sql

您可能感兴趣

LINQ TO SQL 中join

LINQ TO SQL 中join

热门推荐

排行榜