Linq中的联接条件

本文关键字：条件 Linq | 更新日期: 2023-09-27 18:08:46

我需要将以下SQL转换为LINQ：

Select Person.*, Governorate.OptionName
from Person 
Left Join Option Governorate on Governorate.OptionListName='Governorate' 
and Person.GovernorateId=Governorate.GovernorateId

我做了以下事情：

from person in db.persons
join governorate in db.Options 
on  new { Key1=person.GovernorateId,
         Key2=true} equals
    new { Key1=governorate.OptionValue,
         Key2= governorate.OptionListName equals "Governorate"}
select person, governorate

但这会产生两个错误：
1-用于：Key2= governorate.OptionListName equals "Governorate"为"Governorate"声明的equals和Anonymus类型的语法错误

2-用于：select person, governorate该省不能在这个范围内宣布，因为它将赋予该省不同的含义。

Linq中的联接条件

from person in db.persons
   join governorate in db.Options 
       on  new { Key1=person.GovernorateId, Key2="Governorate"} equals
new { Key1=governorate.OptionValue, Key2= governorate.OptionListName  }
into g
select new {
             person = person, 
             governorates = g
           }

它为您提供了一个对象集合，每个对象都有一个person和一个对应于该人的governorate集合。

如果您希望结果包含person和governorate对：

from person in db.persons
   join governorate in db.Options 
       on  new { Key1=person.GovernorateId, Key2="Governorate"} equals
new { Key1=governorate.OptionValue, Key2= governorate.OptionListName  }
into groups
from g in groups.DefaultIfEmpty()
select new {
             person = person, 
             governorates = g.OptionName
           }

这返回每个对象包含一对CCD_ 7和CCD_。

MSDN 中第二个查询的一些解释

生成两个集合的左外部联接的第一步是使用组联接执行内部联接
第二步是在结果集中包括第一个(左(集合的每个元素，即使该元素在右集合中没有匹配项。这是通过对组join中的每个匹配元素序列调用DefaultIfEmpty来实现的。

from p in db.persons
join h in db.Options on p.GovernorateId equals h.OptionValue && h.OptionListName equals "Governorate" into t
 from rt in t
 select new
 {
       p.person, h.governorate
 }).ToList();

from person in db.persons
join governorate in db.Options 
on   person.GovernorateId equals governorate.OptionValue  
where governorate.OptionListName =="Governorate"       
select person, governorate

或你可以试试这个

from person in db.persons
join governorate in db.Options.Where(x=>x.OptionListName =="Governorate") 
on   person.GovernorateId equals governorate.OptionValue 
select person, governorate

我试过这个，效果很好。

from u in User_Accounts.Where(x=>x.User_Account_Type =="Client")
join a in Clients on u.Client_ID equals a.Client_ID
select u
相关文章：