如何将SQL JOIN查询转换为c# LINQ to Entity
本文关键字:LINQ to Entity 转换 查询 SQL JOIN | 更新日期: 2023-09-27 18:12:03
我有这个内部连接查询:
Select Clients.ClientName,
InspectionAuthorities.Description,
COUNT(DISTINCT ObjectTypes.Description) AS NumberOfObjectTypes
From InspectionArchives
--Customer--
Inner Join Clients On InspectionArchives.CustomerID = Clients.Id
--Inspection Authority--
Inner Join InspectionAuthorityInspectionArchives On InspectionArchives.Id = InspectionAuthorityInspectionArchives.InspectionArchive_Id
Inner Join InspectionAuthorities On InspectionAuthorityInspectionArchives.InspectionAuthority_Id = InspectionAuthorities.Id
--Object Types--
Inner Join ObjectTypeInspectionArchives On InspectionArchives.Id = ObjectTypeInspectionArchives.InspectionArchive_Id
Inner Join ObjectTypes On ObjectTypeInspectionArchives.ObjectType_Id = ObjectTypes.Id
Where Clients.Id = 1 or Clients.Id = 2
Group by Clients.ClientName, InspectionAuthorities.Description;
我是LINQ世界的新手,需要将以上SQL查询转换为c# LINQ到实体。
知道如何实现它吗?
from IA in contextObj.InspectionArchives
join cl in contextObj.Clients on IA.CustomerID equals Cl.Id
join IaIa in contextObj.InspectionAuthorityInspectionArchives On IA.Id equals IaIa .InspectionArchive_Id
join IAuth in contectObj.InspectionAuthorities on IaIa.InspectionAuthority_Id equals IAuth.Id
join OTIA in contextObj.ObjectTypeInspectionArchives on IA.Id equals OTIA.InspectionArchive_Id
join ObjT in contextObj.ObjectTypes on OTIA.ObjectType_Id equals ObjT.Id
where (Cl.Id = 1 || Cl.Id = 2)
group new {cl, IAuth} by new {Cl.ClientName, IAuth.Description} into grp
select new
{
clientName = grp.key.ClientName,
Desc = grp.key.Description,
NumberOfObjectTypes = grp.Count(g=>g.ObjT.Description.Distinct())
};