復制SQL查詢（在Linq中通過EFCore6（根據子層次結構屬性選擇父記錄）

我在將我的 4 層物體/導航屬性的 SQL 轉換為 Linq（方法語法而不是查詢語法）時遇到問題：

SELECT TOP 10 GreatGrandfather.Id AS GreatGrandfatherId,
              GreatGrandfather.Name AS GreatGrandfatherName,

              Grandfather.Id AS GrandfatherId,
              Grandfather.Name AS GrandfatherName,

              Father.Id AS FatherId,
              Father.Name AS FatherName,

              Son.Id AS SonId,
              Son.Name AS SonName
FROM GreatGrandfather
INNER JOIN Grandfather ON GreatGrandfather.Id=Grandfather.GreatGrandfatherId
INNER JOIN Father ON Grandfather.Id=Father.GrandfatherId
INNER JOIN Son ON Father.Id=Son.FatherId
WHERE Son.Name LIKE '%Kyle%'
ORDER BY GreatGrandfatherName ASC, GrandfatherName ASC, FatherName ASC, SonName ASC

我試過這樣做：

IQueryable<GreatGrandfather> greatGrandfathers = _context.GreatGrandfathers.Where(ggf => ggf.Grandfathers.Any(gf => gf.Fathers.Any(f => f.Sons.Any(s => s.Name == "kyle")))).Take(10);
                      

//Projection to "flatten" data hierachy into family lineage
IQueryable<FamilyLineage> familyLineages = greatGrandfathers.SelectMany(ggf => ggf.Grandfathers
                                                            .SelectMany(gf => gf.Fathers
                                                            .SelectMany(f => f.Sons
                                                            .Select(s => new FamilyLineage(ggf.Id,
                                                                                           ggf.Name,

                                                                                           gf.Id,
                                                                                           gf.Name,

                                                                                           f.Id,
                                                                                           f.Name,

                                                                                           s.Id,
                                                                                           s.Name)))));

我用 10 條記錄播種了曾祖父表，每個曾祖父記錄有 10 個祖父，每個祖父有 10 個父親，每個父親有 10 個兒子（其中每個父親一個兒子稱為 kyle）。

希望這種播種場景是有意義的，但很抱歉，我不想發布 SQL 種子資料，因為它淹沒了問題。

當我執行上面的 SQL 時，它按預期作業（回傳 10 條記錄，前 10 個兒子稱為 Kyle 和隨后的家族血統）

然而，當我運行我的 Linq 時，它向我回傳了第一個曾祖父的家族血統 10 次，但沒有兒子叫凱爾，所以我認為 Where 子句（它的定位/位置是錯誤的）

我還嘗試首先通過反轉我的 linq 陳述句來展平層次結構，然后移動 .Where()，但是它失敗并出現類似于“無法計算 linq。使用 .AsEnumerable() 將查詢重寫為顯式客戶端”的 Linq 錯誤, .ToList() 等)"

物體（使用 EFcore 命名約定而不是 Fluent API 作為背景關系）

        public class GreatGrandfather
        {
            public int Id { get; set; }
            public string Name { get; set; }
    
            public List<Grandfather> Grandfathers { get; set; }
            
        }
    
        public class Grandfather
        {
            public int Id { get; set; }
            public string Name { get; set; }
    
            public List<Father> Fathers { get; set; }
        }
    
        public class Father
        {
            public int Id { get; set; }
            public string Name { get; set; }
    
            public List<Son> Sons { get; set; }
        }
    
        public class Son
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }

簡化的 SQL 表

CREATE TABLE [dbo].[Father](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [GrandFatherId] [int] NOT NULL
) 

CREATE TABLE [dbo].[Grandfather](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [GreatGrandfatherId] [int] NOT NULL
) 

CREATE TABLE [dbo].[GreatGrandfather](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL
) 

CREATE TABLE [dbo].[Son](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [FatherId] [int] NOT NULL
) 

uj5u.com熱心網友回復：

將一些導航屬性放入您的物體中，child->parent然后按方向執行：

context.Sons.Where(s => s.Name.Contains("Kyle"))
  .Select(s => new FamilyLineage(
    s.Father.Grandfather.GreatGrandfather.Id,
    s.Father.Grandfather.GreatGrandfather.Name,
    s.Father.Grandfather.Id,
    s.Father.Grandfather.Name
                     ...
    s.Id,
    s.Name
  )
).OrderBy(...).Take(...)      

當 EF 看到您在 Select

uj5u.com熱心網友回復：

最后通過移動 .Where() 來完成這項作業，如下所示：

IQueryable<GreatGrandfather> greatGrandfathers = _context.GreatGrandfathers;
                      

//Projection to "flatten" data hierachy into family lineage
IQueryable<FamilyLineage> familyLineages = greatGrandfathers.SelectMany(ggf => ggf.Grandfathers
                                                            .SelectMany(gf => gf.Fathers
                                                            .SelectMany(f => f.Sons
                                                            .Where(s => s.Name == "kyle" /* ggf=="Bob" && gf=="Jimmy" && f=="Another"*/)
                                                            .Select(s => new FamilyLineage(ggf.Id,
                                                                                           ggf.Name,

                                                                                           gf.Id,
                                 

標籤：

上一篇：計算日期范圍的確切周數

下一篇：具有動態指定資料型別的PowerShell中的LINQ