具有多个连接和分组依据的 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

{

    class Program

    {

        static void Main(string[] args)

        {

            List<WorkingInterestGroups> workingInterestGroups = new List<WorkingInterestGroups>();

            List<UnitTracts> unitTracts = new List<UnitTracts>();

            List<Tracts> tracts = new List<Tracts>();

            List<Leases> leases = new List<Leases>();

            List<AdditionalLeaseInfo> additionalLeaseInfos = new List<AdditionalLeaseInfo>();

            List<Interests> interests = new List<Interests>();

            var results = (from unitTract in unitTracts

                join  tract in tracts on unitTract.TractId equals tract.Id 

                join interest in interests on tract.Id equals interest.TractId

                join workingInterestGroup in workingInterestGroups on interest.WorkingInterestGroupId equals workingInterestGroup.Id

                join lease in leases on workingInterestGroup.LeaseId equals lease.Id

                join additionalLeaseInfo in additionalLeaseInfos on lease.Id equals additionalLeaseInfo.LeaseId

                where unitTract.UnitId == "21"

                select new { unitTract = unitTract, tract = tract, interest = interest,  workingInterestGroup = workingInterestGroup,

                    lease = lease, additionalLeaseInfo = additionalLeaseInfo}).ToList();

            var groups = results.GroupBy(x => new

            {

                x.unitTract.Id,

                x.unitTract.UnitId,

                x.lease.Lessor,

                x.lease.Lessee,

                x.lease.Alias,

                x.lease.LeaseDate,

                x.lease.GrossAcres,

                x.lease.Legal,

                x.interest.TractId,

                x.lease.NetAcres,

                x.unitTract.AcInUnit

            })

                .ToList();

        }

    }

    public class WorkingInterestGroups

    {

        public string Id { get; set; }

        public string LeaseId { get; set; }

    }

    public class UnitTracts

    {

        public string TractId { get; set; }

        public string Id { get; set; }

        public string UnitId { get; set; }

        public string AcInUnit { get;set;}

    }

    public class Tracts

    {

        public string Id { get; set; }

    }

    public class Leases

    {

        public string Id { get; set; }

        public string Lessor { get; set; }

        public string Lessee { get; set; }

        public string Alias { get; set; }

        public string LeaseDate { get; set; }

        public string GrossAcres { get; set; }

        public string Legal { get; set; }

        public string NetAcres { get; set; }

    }

    public class AdditionalLeaseInfo

    {

        public string LeaseId { get; set;}

    }

    public class Interests

    {

        public string TractId { get; set; }

        public string WorkingInterestGroupId { get; set; }

    }

}