如何将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到实体。

知道如何实现它吗?

如何将SQL JOIN查询转换为c# LINQ to Entity

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())
};