CODEWITHSUNDEEP
c#linqentity-framework-coreparent-child
user6408649
user6408649

Reputation: 1305

How select hierarchy from child to parent using linq for entities or EF Core 2?

There is a hierarchy of four entities (models). All entities have relationships one to many. It's looks following:

public class Region
    {
        public Guid Id { get; set; }
        public int RegionId { get; set; }
        public virtual ICollection<District> Districts { get; set; }
    }
    public class District
    {
        public Guid Id { get; set; }
        public int DistrictId { get; set; }
        public virtual ICollection<Building> Buildings { get; set; }
        public int RegionId { get; set; }
        public Region Region { get; set; }
    }
    public class Building
    {
        public Guid Id { get; set; }
        public int BuildingId { get; set; }
        public int DistrictId { get; set; }
        public virtual ICollection<Flat> Flats { get; set; }
        public District District { get; set; }
    }
    public class Flat
    {
        public Guid Id { get; set; }
        public int FlatId { get; set; }
        public int BuildingId { get; set; }
        public Building Building { get; set; }
    }

It is hierarchy like: Region->District->Building->Flat.

I want return collection of Flat thats include all related entities from top. I do it folowing:

var flat = _context.Flats
                .Include(t => t.Building)
                .Include(b=>b.Building.District)
                .Include(b => b.Building.District.Region).GetPaged(PageNumber, 20);

But for some reason is return breaking result....

[{"id":"6cbb1431-36d4-490c-a047-c211e4db3bdd","flatId":29570,"roomsCount":1,"totalArea":41.95,"kitchenArea":11.85,"floor":21,"buildingId":130,"price":5725650.00,"building":{"id":"03baca1e-02e9-4ee0-ae47-920e3eb0d5bb","buildingId":130,"name":"NNN","queue":1,"housing":"1","districtId":13003,"flats":[

It breaks for some reason on public virtual ICollection<Flat> Flats { get; set; }. I can't remove it becasue it is needed for FK.

The following query works:

_context.Flat
   .Join(
      _context.Building,
      f => f.BuildingId,
      b => b.BuildingId,
      (f, b) =>
         new
         {
             f = f,
             b = b
         }
   )
   .Join(
      _context.District,
      temp0 => temp0.b.DistrictId,
      d => d.DistrictId,
      (temp0, d) =>
         new
         {
             temp0 = temp0,
             d = d
         }
   )
   .Join(
      _context.Region,
      temp1 => temp1.d.RegionId,
      r => r.RegionId,
      (temp1, r) =>
         new 
         {
             Id = temp1.temp0.f.Id,
             FlatId = temp1.temp0.f.FlatId,
             Floor = temp1.temp0.f.Floor,
             RoomsCount = temp1.temp0.f.RoomsCount,
             TotalArea = temp1.temp0.f.TotalArea,
             KitchenArea = temp1.temp0.f.KitchenArea,
             Price = temp1.temp0.f.Price,
             Building = new
             {
                 Id = temp1.temp0.b.Id,
                 BuildingId = temp1.temp0.b.Id,
                 Name = temp1.temp0.b.Name,
                 Queue = temp1.temp0.b.Queue,
                 Housing = temp1.temp0.b.Housing,
                 District = new
                 {
                     Id = temp1.d.Id,
                     DistrictId = temp1.d.DistrictId,
                     Name = temp1.d.Name,
                     Regions = new
                     {
                         Id = r.Id,
                         RegionId = r.Id,
                         Name = r.Name
                     }
                 }
             }
         }
   ).GetPaged(PageNumber, PageSize)

And it is return what i whant:

{
    "id": "a49a0a48-370b-46da-b6da-ea4040cc1431",
    "flatId": 84095,
    "roomsCount": 3,
    "totalArea": 76.5,
    "kitchenArea": 11.07,
    "floor": 18,
    "buildingId": 0,
    "price": 5567500,
    "building": {
        "id": "037c4074-3ff5-4fbd-92ea-88c8f746b883",
        "buildingId": 200,
        "name": "NNNN",
        "queue": 3,
        "housing": "4",
        "districtId": 0,
        "flats": null,
        "district": {
            "id": "db031e09-1d6c-41f4-9e45-ec6170e3df8b",
            "districtId": 5005,
            "name": "Distr",
            "buildings": null,
            "regionId": 0,
            "region": {
                "id": "5ac5766f-e62e-4da9-86cf-e4a85423503a",
                "regionId": 5001,
                "name": "Region",
                "districts": null
            }
        }
    }
}

But it is looks like cumbersome. How can I do it using EF features? Or maybe somehow to reduce linq? And why include() return breaking result?

P.S. .GetPaged(PageNumber, 20); is extension method for pagination and he is working fine.

Upvotes: 0

Views: 777

Answers (1)

Kim Hoang
Kim Hoang

Reputation: 1368

My suggesstion is below

var flag = (from f in _context.Flags
       join b in _context.Buildings
       on b.BuildingId equals f.BuildingId
       join d in _context.Districts
       on d.DistrictId equals b.DistrictId 
       join r in _context.Regions
       on r.RegionId equals d.RegionId
       select new 
       { ... 
       }).GetPaged(PageNumber, 20);

You can get any property from Building, District and Region to populate to your result object.

Upvotes: 2

Related Questions