Linq实现t-Sql的各种连接

 在ORM框架大行其道的今天，对于.net行业的人，想要学好EF，那Linq的学习在势在必行啊。今天总结下平时比较常用的表连接的用法。

 Inner Join

     Linq： 

  var list = (from c in customerDb.Order
                            join o in customerDb.OrderItem on c.OrderId equals o.OrderItemId
                            select new { c = c.OrderId }).FirstOrDefault();

      生成的Sql

SELECT TOP (1) 
[Extent1].[OrderId] AS [OrderId]
FROM  [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[OrderItems] AS [Extent2] ON [Extent1].[OrderId] = [Extent2].[OrderItemId]

Left Join

      Linq:

     var list = (from c in customerDb.Order
                            join o in customerDb.OrderItem on c.OrderId equals o.OrderItemId
                            into Grp
                            from grp in Grp.DefaultIfEmpty()
                            select new { c = c.OrderId, grp = grp.OrderItemId }).FirstOrDefault();

      生成的Sql:

SELECT TOP (1) 
[Extent1].[OrderId] AS [OrderId], 
[Extent2].[OrderItemId] AS [OrderItemId]
FROM  [dbo].[Orders] AS [Extent1]
LEFT OUTER JOIN [dbo].[OrderItems] AS [Extent2] ON [Extent1].[OrderId] = [Extent2].[OrderItemId]

 Cross Join

        Linq：   

    var list = (from c in customerDb.Order
                            from o in customerDb.OrderItem 
                            select new { c = c.OrderId,o=o.OrderItemId }).FirstOrDefault();

        生成的Sql:

SELECT TOP (1) 
[Extent1].[OrderId] AS [OrderId], 
[Extent2].[OrderItemId] AS [OrderItemId]
FROM  [dbo].[Orders] AS [Extent1]
CROSS JOIN [dbo].[OrderItems] AS [Extent2]

  总结：右连接其实就是换下两个表的顺序位置，

          全连接就是把左外连接的结果拼接起来去重就行。

          用顺ef后，可以加速开发效率，期待那一天早日到来，加油啊！！！