具有多个连接和分组依据的 SQL 到 LINQ

Question

我正在努力将以下 SQL 代码转换为 MVC 的 LINQ 查询。它有多个嵌套连接和分组依据。

  SELECT UnitTracts.Id,  

    UnitTracts.UnitId,  

    Leases.Id,  

    Leases.Lessor,  

    Leases.Lessee,  

    Leases.Alias,  

    Leases.LeaseDate,  

    Leases.GrossAcres,  

    IIf([Page] Is Null,[VolumeDocumentNumber],[VolumeDocumentNumber] + '/' + [Page]) AS [Vol/Pg], 

    Leases.Legal,  

    Interests.TractId,  

    Leases.NetAcres,  

    UnitTracts.AcInUnit 

    FROM (UnitTracts INNER JOIN (((WorkingInterestGroups INNER JOIN Interests ON WorkingInterestGroups.Id = Interests.WorkingInterestGroupId)  

    INNER JOIN Tracts ON Interests.TractId = Tracts.Id)  

    INNER JOIN Leases ON WorkingInterestGroups.LeaseId = Leases.Id)  

    ON UnitTracts.TractId = Tracts.Id)  

    LEFT JOIN AdditionalLeaseInfo ON Leases.Id = AdditionalLeaseInfo.LeaseId  

    where unitId = 21 

    GROUP BY UnitTracts.Id,  

    UnitTracts.UnitId,  

    Leases.Id,  

    Leases.Lessor,  

    Leases.Lessee,  

    Leases.Alias,  

    Leases.LeaseDate,  

    Leases.GrossAcres,  

    IIf([Page] Is Null,[VolumeDocumentNumber],[VolumeDocumentNumber] + '/' + [Page]), 

    Leases.Legal,  

    Interests.TractId, 

     Leases.NetAcres,  

    UnitTracts.AcInUnit 

这是我得到的查询，但它返回的记录较少。我试图从 SQL 转换为 LINQ，但没有成功。我现在真的卡住了。

Answer 1

我用类对您的查询进行建模以使语法正确：using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;namespace ConsoleApplication1{&nbsp; &nbsp; class Program&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; static void Main(string[] args)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<WorkingInterestGroups> workingInterestGroups = new List<WorkingInterestGroups>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<UnitTracts> unitTracts = new List<UnitTracts>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<Tracts> tracts = new List<Tracts>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<Leases> leases = new List<Leases>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<AdditionalLeaseInfo> additionalLeaseInfos = new List<AdditionalLeaseInfo>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<Interests> interests = new List<Interests>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var results = (from unitTract in unitTracts&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join&nbsp; tract in tracts on unitTract.TractId equals tract.Id&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join interest in interests on tract.Id equals interest.TractId&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join workingInterestGroup in workingInterestGroups on interest.WorkingInterestGroupId equals workingInterestGroup.Id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join lease in leases on workingInterestGroup.LeaseId equals lease.Id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join additionalLeaseInfo in additionalLeaseInfos on lease.Id equals additionalLeaseInfo.LeaseId&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where unitTract.UnitId == "21"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; select new { unitTract = unitTract, tract = tract, interest = interest,&nbsp; workingInterestGroup = workingInterestGroup,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; lease = lease, additionalLeaseInfo = additionalLeaseInfo}).ToList();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var groups = results.GroupBy(x => new&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.unitTract.Id,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.unitTract.UnitId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.lease.Lessor,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.lease.Lessee,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.lease.Alias,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.lease.LeaseDate,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.lease.GrossAcres,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.lease.Legal,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.interest.TractId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.lease.NetAcres,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x.unitTract.AcInUnit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToList();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; public class WorkingInterestGroups&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string Id { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string LeaseId { get; set; }&nbsp; &nbsp; }&nbsp; &nbsp; public class UnitTracts&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string TractId { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string Id { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string UnitId { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string AcInUnit { get;set;}&nbsp; &nbsp; }&nbsp; &nbsp; public class Tracts&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string Id { get; set; }&nbsp; &nbsp; }&nbsp; &nbsp; public class Leases&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string Id { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string Lessor { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string Lessee { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string Alias { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string LeaseDate { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string GrossAcres { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string Legal { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string NetAcres { get; set; }&nbsp; &nbsp; }&nbsp; &nbsp; public class AdditionalLeaseInfo&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string LeaseId { get; set;}&nbsp; &nbsp; }&nbsp; &nbsp; public class Interests&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string TractId { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public string WorkingInterestGroupId { get; set; }&nbsp; &nbsp; }}