具有许多表、左外部联接和where子句的LINQ查询
本文关键字:where 子句 查询 LINQ 许多表 外部 | 更新日期: 2023-09-27 18:09:44
我需要将这个SQL查询转换为c#中的LINQ。如果您能提供任何帮助或指导,我将不胜感激。(我使用的是实体框架6(。
SELECT f.FacId
,sli.SitLocIntId
,ei.EnvIntId
,p.PhoId
FROM Fac AS f
join SitLocInt AS sli on f.FacId = sli.FacId
join EnvInt AS ei on sli.EnvIntId = ei.EnvIntId
join EnvIntTyp AS eit on ei.EnvIntTypId = eit.EnvIntTypId
left outer join Aff AS a on ei.EnvIntId = a.EnvIntId
left outer join AffCon AS ac on a.AffId = ac.AffId
left outer join Con AS c on ac.ConId = c.ConId
left outer join Pho AS p on c.ConId = p.ConId
WHERE EnvIntTyp = 'Fleet'
我一直在研究组联接语法,但由于有这么多表、很多左外部联接和where子句,我没有取得多大进展。到目前为止,我有
var testQuery =
from f in _CEMDbContext.setFac
join sli in _CEMDbContext.setSitLocInt on f.FacId equals sli.FacId
join ei in _CEMDbContext.setEnvInt on sli.EnvIntId equals ei.EnvIntId
join eit in _CEMDbContext.setEnvIntTyp on ei.EnvIntTypId equals eit.EnvIntTypId into eiGroup
from item in eiGroup.DefaultIfEmpty().Where(e => e.EnvIntTyp == "Fleet")
select new BusinessParticipant
{
fac = f,
sitLocInt = sli,
envInt = ei,
// pho = p, // not working... Phone number from table Pho.
};
var TestList = testQuery.ToList();
正在获取EnvIntTyp"Fleet"及其所有相关数据,包括Fac、SitLocInt和EnvInt。但当我试图从Pho表中获取可能存在也可能不存在的电话号码时,我遇到了麻烦。有没有一种很好的方法将所有左侧的外部连接连接在一起?谢谢你的建议或指导!
编辑我不仅仅需要在SQL查询中所写的Id,还需要整个对象。另外,我应该提到EnvIntTyp是同名表的一个成员。
编辑2以下是相关实体的(部分(。Fac是Facility的缩写。SitLocInt是SiteLocationInterest的缩写。EnvInt代表EnvironmentalInterest。Aff代表Affiliation。Con代表Contact。Pho代表Phone。
public partial class Facility
{
public Facility()
{
this.SiteLocationInterests = new List<SiteLocationInterest>();
}
public int FacilityId { get; set; }
public string FacilityIdentifier { get; set; }
public string FacilityName { get; set; }
public int SiteTypeId { get; set; }
public string FacilityDescription { get; set; }
[ForeignKey("GeographicFeature")]
public Nullable<int> GeographicFeatureId { get; set; }
[Display(Name = "resAddress", ResourceType = typeof(CEMResource))]
public string AddressLine1 { get; set; }
[Display(Name = "resAddressLine2", ResourceType = typeof(CEMResource))]
public string AddressLine2 { get; set; }
public string City { get; set; }
[Display(Name = "resState", ResourceType = typeof(CEMResource))]
public string StateCode { get; set; }
[Display(Name = "resZip", ResourceType = typeof(CEMResource))]
public string AddressPostalCode { get; set; }
public string CountyName { get; set; }
public virtual GeographicFeature GeographicFeature { get; set; }
public virtual ICollection<SiteLocationInterest> SiteLocationInterests { get; set; }
}
public partial class SiteLocationInterest
{
public int SiteLocationInterestId { get; set; }
[ForeignKey("Facility")]
public Nullable<int> FacilityId { get; set; }
[ForeignKey("EnvironmentalInterest")]
public Nullable<int> EnvironmentalInterestId { get; set; }
public Nullable<int> EventId { get; set; }
[ForeignKey("GeographicFeature")]
public Nullable<int> GeographicFeatureId { get; set; }
public System.DateTime CreateDate { get; set; }
public string CreateBy { get; set; }
public System.DateTime LastUpdateDate { get; set; }
public string LastUpdateBy { get; set; }
public virtual EnvironmentalInterest EnvironmentalInterest { get; set; }
public virtual Facility Facility { get; set; }
public virtual GeographicFeature GeographicFeature { get; set; }
}
public partial class EnvironmentalInterest
{
public EnvironmentalInterest()
{
this.Affiliations = new List<Affiliation>();
this.SiteLocationInterests = new List<SiteLocationInterest>();
}
public int EnvironmentalInterestId { get; set; }
public string EnvironmentalInterestIdentifier { get; set; }
public string EnvironmentalInterestFacilityIdentifier { get; set; }
public string FacilityIdentifier { get; set; }
public string EnvironmentalInterestName { get; set; }
[ForeignKey("EnvironmentalInterestType")]
public int EnvironmentalInterestTypeId { get; set; }
public Nullable<int> EnvironmentalInterestSubTypeId { get; set; }
public string EnvironmentalInterestDescription { get; set; }
public virtual ICollection<Affiliation> Affiliations { get; set; }
public virtual EnvironmentalInterestType EnvironmentalInterestType { get; set; }
public virtual ICollection<SiteLocationInterest> SiteLocationInterests { get; set; }
}
public partial class Affiliation
{
public Affiliation()
{
this.AffiliationContacts = new List<AffiliationContact>();
}
public int AffiliationId { get; set; }
public string AffiliationIdentifier { get; set; }
public string AffiliationName { get; set; }
[ForeignKey("Entity")]
public Nullable<int> EntityId { get; set; }
[ForeignKey("EnvironmentalInterest")]
public Nullable<int> EnvironmentalInterestId { get; set; }
public int AffiliationTypeId { get; set; }
public int StatusTypeId { get; set; }
public virtual EnvironmentalInterest EnvironmentalInterest { get; set; }
public virtual ICollection<AffiliationContact> AffiliationContacts { get; set; }
}
public partial class AffiliationContact
{
public int AffiliationContactId { get; set; }
public int AffiliationId { get; set; }
public int ContactId { get; set; }
public virtual Affiliation Affiliation { get; set; }
public virtual Contact Contact { get; set; }
}
public partial class Contact
{
public Contact()
{
this.AffiliationContacts = new List<AffiliationContact>();
this.Phones = new List<Phone>();
}
public int ContactId { get; set; }
public string ContactIdentifier { get; set; }
public int EntityId { get; set; }
public Nullable<int> MailAddressId { get; set; }
public int ContactTypeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string EmailAddress { get; set; }
public virtual ICollection<AffiliationContact> AffiliationContacts { get; set; }
public virtual Entity Entity { get; set; }
public virtual ICollection<Phone> Phones { get; set; }
}
public partial class Phone
{
public int PhoneId { get; set; }
public int ContactId { get; set; }
public int ContactTypeId { get; set; }
public string PhoneNumber { get; set; }
public string PhoneExtensionNumber { get; set; }
public virtual Contact Contact { get; set; }
public virtual ContactType ContactType { get; set; }
}
让我们将问题简化为更容易理解的内容。这里有一个简单的左连接:
SELECT As.AId, Bs.BId
FROM As
LEFT JOIN Bs ON As.AId = Bs.AId
在LINQ中:
from a in ctx.As
join b in ctx.Bs on a.AId equals b.AId into bgrp // group join -- put matching Bs
// into a group "bgrp".
from b in bgrp.DefaultIfEmpty() // from DefaultIfEmpty() -- if the group
// has any rows, return them. otherwise,
// return a single row with the
// entity's default value (null).
select new
{
a.AId,
b.BId
}
您只需重复即可链接多个左联接:
from a in ctx.As
join b in ctx.Bs on a.AId equals b.AId into bs
from b in bs.DefaultIfEmpty()
join c in ctx.Cs on b.BId equals c.BId into cs
from c in cs.DefaultIfEmpty()
select new
{
a.AId,
b.BId,
c.CId
}
Northwnd db = new Northwnd(@"c:'northwnd.mdf");
IEnumerable<Customer> results = db.ExecuteQuery<Customer>
(@"SELECT c1.custid as CustomerID, c2.custName as ContactName
FROM customer1 as c1, customer2 as c2
WHERE c1.custid = c2.custid"
);
只要表格结果中的列名与实体类的列属性匹配,LINQ to SQL就会从任何SQL查询中创建对象。
您可以通过Linq在C#中执行本机SQL代码,因此实际上根本不需要转换这些代码。查看MSDN的这篇文章:https://msdn.microsoft.com/en-us/library/bb399403(v=vs.110(.aspx
以下是我的操作方法:
var bpQuery =
from f in _CEMDbContext.setFac
join sli in _CEMDbContext.setSitLocInt on f.FacId equals sli.FacId
join ei in _CEMDbContext.setEnvInt on sli.EnvIntId equals ei.EnvIntId into eiGroup
from eig in eiGroup.DefaultIfEmpty().Where(e => e.EnvIntTyp.EnvIntTyp == "Fleet")
join a in _CEMDbContext.setAff on eig.EnvIntId equals a.EnvIntId into aGroup
from ag in aGroup.DefaultIfEmpty()
join ac in _CEMDbContext.setAffCon.DefaultIfEmpty() on ag.AffId equals ac.AffId into acGroup
from acg in acGroup.DefaultIfEmpty()
join c in _CEMDbContext.setCon.DefaultIfEmpty() on acg.ConId equals c.ConId into cGroup
from cg in cGroup.DefaultIfEmpty()
join p in _CEMDbContext.setPho.DefaultIfEmpty() on cg.ContactId equals p.ConId into pGroup
from pg in pGroup.DefaultIfEmpty()
select new BusinessParticipant
{
facility = f,
sitLocInt = sli,
envInt = eig,
pho = pg,
};
BusinessParticipantList = bpQuery.ToList();