2012-11-19 23:00 imyang 阅读(1874) 评论(0) 编辑 收藏
在stackoverflow上看到关于这个问题的讨论,打算记录下来。
关于全连接(Full Outer Join),Linq并不支持,所以就需要使用其它方法来实现,就像不支持全连接的数据库一样,先使用内连接找出公共的部分,然后分别找出左连接和右连接的部分,把这三部分的结果UNION一下,即可得到全连接的效果。
看问题:
ID FirstName -- --------- 1 John 2 Sue ID LastName -- -------- 1 Doe 3 Smith |
ID FirstName LastName -- --------- -------- 1 John Doe 2 Sue 3 Smith |
实现方法一:
先找出左连接的结果,再找出右连接的结果,然后把这两个结果UNION即可,UNION时会自动把重复的数据过滤掉。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | var firstNames = new [] { new { ID = 1, Name = "John" }, new { ID = 2, Name = "Sue" }, }; var lastNames = new [] { new { ID = 1, Name = "Doe" }, new { ID = 3, Name = "Smith" }, }; var leftOuterJoin = from first in firstNames join last in lastNames on first.ID equals last.ID into temp from last in temp.DefaultIfEmpty( new { first.ID, Name = default ( string ) }) select new { first.ID, FirstName = first.Name, LastName = last.Name, }; var rightOuterJoin = from last in lastNames join first in firstNames on last.ID equals first.ID into temp from first in temp.DefaultIfEmpty( new { last.ID, Name = default ( string ) }) select new { last.ID, FirstName = first.Name, LastName = last.Name, }; var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin); |
方法二:
得到左连接的数据,再从右连接的数据中把左连接的数据排除掉,两者的数据Concat一下即可
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | var firstNames = new [] { new { ID = 1, Name = "John" }, new { ID = 2, Name = "Sue" }, }; var lastNames = new [] { new { ID = 1, Name = "Doe" }, new { ID = 3, Name = "Smith" }, }; var leftData = ( from first in firstNames join last in lastNames on first.ID equals last.ID into temp from last in temp.DefaultIfEmpty( new { first.ID, Name = default ( string ) }) select new { first.ID, FirstName = first.Name, LastName = last.Name, }); var rightRemainingData = ( from r in lastNames where !( from a in leftData select a.ID).Contains(r.ID) select new { r.ID, FirstName = default ( string ), LastName = r.Name }); var fullOuterjoinData = leftData.Concat(rightRemainingData); |
以上两种方法,其实是同一种方式来实现全连接的,只是第二次拿出的数据有所不同。
以上,希望对大家有所帮助。