LEFT OUTER JOIN LINQ
如何在不使用join-on-equals-into
子句的情况下将C#LINQ中的左外连接执行为对象? 有什么方法可以用where
子句做到这一点? 正确的问题:内部连接很容易,我有这样的解决方案
List<JoinPair> innerFinal = (from l in lefts from r in rights where l.Key == r.Key
select new JoinPair { LeftId = l.Id, RightId = r.Id})
但对于左外连接,我需要一个解决方案。 我的是这样的,但它不起作用
List< JoinPair> leftFinal = (from l in lefts from r in rights
select new JoinPair {
LeftId = l.Id,
RightId = ((l.Key==r.Key) ? r.Id : 0
})
JoinPair是一个类:
public class JoinPair { long leftId; long rightId; }
如上所述:
101 LINQ示例 - 左外连接
var q =
from c in categories
join p in products on c.Category equals p.Category into ps
from p in ps.DefaultIfEmpty()
select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };
Necromancing。
如果使用数据库驱动的LINQ提供程序,则可以这样编写可读性更高的左外部联接:
from maintable in Repo.T_Whatever
from xxx in Repo.T_ANY_TABLE.Where(join condition).DefaultIfEmpty()
如果你省略了DefaultIfEmpty()
你将会有一个内部连接。
采取接受的答案:
from c in categories
join p in products on c equals p.Category into ps
from p in ps.DefaultIfEmpty()
这个语法非常令人困惑,当你想要离开连接MULTIPLE表时,它的工作原理并不清楚。
注意
应该注意的是, from alias in Repo.whatever.Where(condition).DefaultIfEmpty()
中的from alias in Repo.whatever.Where(condition).DefaultIfEmpty()
与外应用/左连接侧相同,任何(非延迟)数据库优化器都可以完全翻译进入左连接,只要你不引入每行值(又名实际的外应用)。 不要在Linq-2-Objects中执行此操作(因为在使用Linq-to-Objects时没有DB-优化器)。
详细示例
var query2 = (
from users in Repo.T_User
from mappings in Repo.T_User_Group
.Where(mapping => mapping.USRGRP_USR == users.USR_ID)
.DefaultIfEmpty() // <== makes join left join
from groups in Repo.T_Group
.Where(gruppe => gruppe.GRP_ID == mappings.USRGRP_GRP)
.DefaultIfEmpty() // <== makes join left join
// where users.USR_Name.Contains(keyword)
// || mappings.USRGRP_USR.Equals(666)
// || mappings.USRGRP_USR == 666
// || groups.Name.Contains(keyword)
select new
{
UserId = users.USR_ID
,UserName = users.USR_User
,UserGroupId = groups.ID
,GroupName = groups.Name
}
);
var xy = (query2).ToList();
当与LINQ 2 SQL一起使用时,它会很好地转换成以下非常易读的SQL查询:
SELECT
users.USR_ID AS UserId
,users.USR_User AS UserName
,groups.ID AS UserGroupId
,groups.Name AS GroupName
FROM T_User AS users
LEFT JOIN T_User_Group AS mappings
ON mappings.USRGRP_USR = users.USR_ID
LEFT JOIN T_Group AS groups
ON groups.GRP_ID == mappings.USRGRP_GRP
编辑:
有关更复杂的示例,另请参阅“将SQL Server查询转换为Linq查询”。
另外,如果你在Linq-2-Objects(而不是Linq-2-SQL)中执行它,你应该以传统的方式来执行它(因为LINQ to SQL正确地将它转换为加入操作,强制全面扫描,并没有利用索引搜索,为什么......):
var query2 = (
from users in Repo.T_Benutzer
join mappings in Repo.T_Benutzer_Benutzergruppen on mappings.BEBG_BE equals users.BE_ID into tmpMapp
join groups in Repo.T_Benutzergruppen on groups.ID equals mappings.BEBG_BG into tmpGroups
from mappings in tmpMapp.DefaultIfEmpty()
from groups in tmpGroups.DefaultIfEmpty()
select new
{
UserId = users.BE_ID
,UserName = users.BE_User
,UserGroupId = mappings.BEBG_BG
,GroupName = groups.Name
}
);
使用lambda表达式
db.Categories
.GroupJoin(
db.Products,
Category => Category.CategoryId,
Product => Product.CategoryId,
(x, y) => new { Category = x, Products = y })
.SelectMany(
xy => xy.Products.DefaultIfEmpty(),
(x, y) => new { Category = x.Category, Product = y })
.Select(s => new
{
CategoryName = s.Category.Name,
ProductName = s.Product.Name
})
链接地址: http://www.djcxy.com/p/24915.html
下一篇: What is the difference between Left, Right, Outer and Inner Joins?