Replicating SQL query (in Linq via EFCore 6 (selecting parent records depending on child hierachy pr-CodePudding

I have a problem converting this SQL for my 4-tier hierachy of entities/navigation properties into Linq (method syntax rather than query syntax):

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

I have tried doing it as:

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

I seed the greatgrandfather table with 10 records, and each greatgrandfather record has 10 grandfathers, and each grandfather has 10 fathers and each father has 10 sons (of which one son per father is called kyle).

Hopefully this seeding scenario makes sense but apologies I dont want to post the SQL seed data as it floods the question.

When I execute the SQL above it works as expected (returns me 10 records with the first 10 sons called Kyle and subsequent family lineage)

However when I run my Linq, it is returning me the familylineage of the first greatgrandfather 10 times but no sons called Kyle so I thik the Where clause (and its positioning/location is wrong)

I also tried to flatten the hierachy first by reversing my linq statments and then move the .Where(), however it failed with a Linq error similar to "linq could not be calculated. rewrite the query else as explcit client with .AsEnumerable(), .ToList() etc)"

Entities (using EFcore naming conventions rather than Fluent API for the context)


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

Simplified SQL Tables

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
)

CodePudding user response：

Put some navigation properties into your entities, that go in the child->parent direction and then do:

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 will sort all the joining out when it sees you accessing this object path in the Select

CodePudding user response：

Finally got this working by moving the .Where() as below:

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,