C# LEFT JOIN 在 LINQ 中加入实体？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19356439/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LEFT JOIN in LINQ to entities?
提问by Stefan Steiger
I'm trying out LINQ to entities.
我正在尝试使用 LINQ to entity。
I have a problem with the following: I want it to do this:
我有以下问题：我希望它这样做：
SELECT
T_Benutzer.BE_User
,T_Benutzer_Benutzergruppen.BEBG_BE
FROM T_Benutzer
LEFT JOIN T_Benutzer_Benutzergruppen
ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID
the closest thing I've come to is this:
我最接近的是这个：
var lol = (
from u in Repo.T_Benutzer
//where u.BE_ID == 1
from o in Repo.T_Benutzer_Benutzergruppen.DefaultIfEmpty()
// on u.BE_ID equals o.BEBG_BE
where (u.BE_ID == o.BEBG_BE || o.BEBG_BE == null)
//join bg in Repo.T_Benutzergruppen.DefaultIfEmpty()
// on o.BEBG_BG equals bg.ID
//where bg.ID == 899
orderby
u.BE_Name ascending
//, bg.Name descending
//select u
select new
{
u.BE_User
,o.BEBG_BG
//, bg.Name
}
).ToList();
But this generates the same results as an inner join, and not a left join.
Moreover, it creates this completely crazy SQL:
但这会产生与内部连接相同的结果，而不是左连接。
此外，它创建了这个完全疯狂的 SQL：
SELECT
[Extent1].[BE_ID] AS [BE_ID]
,[Extent1].[BE_User] AS [BE_User]
,[Join1].[BEBG_BG] AS [BEBG_BG]
FROM [dbo].[T_Benutzer] AS [Extent1]
CROSS JOIN
(
SELECT
[Extent2].[BEBG_BE] AS [BEBG_BE]
,[Extent2].[BEBG_BG] AS [BEBG_BG]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN [dbo].[T_Benutzer_Benutzergruppen] AS [Extent2]
ON 1 = 1
) AS [Join1]
WHERE [Extent1].[BE_ID] = [Join1].[BEBG_BE]
OR [Join1].[BEBG_BE] IS NULL
ORDER BY [Extent1].[BE_Name] ASC
How can I do a left join in LINQ-2-entities in a way where another person can still understand what's being done in that code ?
我怎样才能在 LINQ-2 实体中进行左连接，让另一个人仍然可以理解该代码中正在执行的操作？
and most-preferably where the generated SQL looks like:
最好是生成的 SQL 如下所示：
SELECT
T_Benutzer.BE_User
,T_Benutzer_Benutzergruppen.BEBG_BE
FROM T_Benutzer
LEFT JOIN T_Benutzer_Benutzergruppen
ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID
采纳答案by Stefan Steiger
Ah, got it myselfs.
The quirks and quarks of LINQ-2-entities.
This looks most understandable:
啊，自己弄的。
LINQ-2 实体的怪癖和夸克。
这看起来最容易理解：
var query2 = (
from users in Repo.T_Benutzer
from mappings in Repo.T_Benutzer_Benutzergruppen
.Where(mapping => mapping.BEBG_BE == users.BE_ID).DefaultIfEmpty()
from groups in Repo.T_Benutzergruppen
.Where(gruppe => gruppe.ID == mappings.BEBG_BG).DefaultIfEmpty()
//where users.BE_Name.Contains(keyword)
// //|| mappings.BEBG_BE.Equals(666)
//|| mappings.BEBG_BE == 666
//|| groups.Name.Contains(keyword)
select new
{
UserId = users.BE_ID
,UserName = users.BE_User
,UserGroupId = mappings.BEBG_BG
,GroupName = groups.Name
}
);
var xy = (query2).ToList();
Remove the .DefaultIfEmpty(), and you get an inner join.
That was what I was looking for.
删除.DefaultIfEmpty()，您将获得一个内部连接。
这就是我要找的。
回答by Giannis Paraskevopoulos
You can read an article i have written for joins in LINQ here
您可以在此处阅读我为 LINQ 中的连接编写的文章
var query =
from u in Repo.T_Benutzer
join bg in Repo.T_Benutzer_Benutzergruppen
on u.BE_ID equals bg.BEBG_BE
into temp
from j in temp.DefaultIfEmpty()
select new
{
BE_User = u.BE_User,
BEBG_BG = (int?)j.BEBG_BG// == null ? -1 : j.BEBG_BG
//, bg.Name
}
The following is the equivalent using extension methods:
以下是使用扩展方法的等效项：
var query =
Repo.T_Benutzer
.GroupJoin
(
Repo.T_Benutzer_Benutzergruppen,
x=>x.BE_ID,
x=>x.BEBG_BE,
(o,i)=>new {o,i}
)
.SelectMany
(
x => x.i.DefaultIfEmpty(),
(o,i) => new
{
BE_User = o.o.BE_User,
BEBG_BG = (int?)i.BEBG_BG
}
);
回答by atik sarker
You can use this not only in entities but also store procedure or other data source:
您不仅可以在实体中使用它，还可以在存储过程或其他数据源中使用它：
var customer = (from cus in _billingCommonservice.BillingUnit.CustomerRepository.GetAll()
join man in _billingCommonservice.BillingUnit.FunctionRepository.ManagersCustomerValue()
on cus.CustomerID equals man.CustomerID
// start left join
into a
from b in a.DefaultIfEmpty(new DJBL_uspGetAllManagerCustomer_Result() )
select new { cus.MobileNo1,b.ActiveStatus });
回答by Alejandro Gori
May be I come later to answer but right now I'm facing with this... if helps there are one more solution (the way i solved it).
可能是我稍后来回答，但现在我正面临这个问题......如果有帮助，还有一个解决方案（我解决它的方式）。
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
}
);
By the way, I tried using the Stefan Steiger code which also helps but it was slower as hell.
顺便说一句，我尝试使用 Stefan Steiger 代码，这也有帮助，但速度慢得要命。
回答by mahdi moghimi
Easy way is to use Let keyword. This works for me.
简单的方法是使用 Let 关键字。这对我有用。
from AItem in Db.A
Let BItem = Db.B.Where(x => x.id == AItem.id ).FirstOrDefault()
Where SomeCondition
Select new YourViewModel
{
X1 = AItem.a,
X2 = AItem.b,
X3 = BItem.c
}
This is a simulation of Left Join. If each item in B table not match to A item , BItem return null
这是Left Join的模拟。如果 B 表中的每个项目都与 A 项目不匹配，则 BItem 返回 null
