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

    标签: