Linq实现t-Sql的各种连接

浏览数：120 / 时间：2015年06月12日

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

Inner Join

Linq：

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

生成的Sql

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

Left Join

Linq:

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

生成的Sql:

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

Cross Join

Linq：

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

生成的Sql:

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

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

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

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

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

Linq实现t-Sql的各种连接