Entity Framework Core : get data from related tables with include

Question

I am trying to get data from five tables: Category - subCategory - secondSubCategory - type - heating which are all related to main table (property).

Tables (category - subCategory - secondSubCategory) are related like layers (Category => subCategory => secondSubCategory)

I tried to get data with:

public IActionResult getAllProperties()
{
      var properties = db.properties
                         .Include(cat => cat.category)
                         .Include(sub => sub.subCategory)
                         .Include(sec => sec.SecondSubCategory)
                         .Include(e => e.heating)
                         .Include(e => e.type)        
                         .OrderByDescending(x => x.id)
                         .ToList();
      return Ok(properties);
}

but the returned data was with values for type and heating fields but with null values for (categoryId and subCategoryId and secondSubCategoryId) knowing that those fields have values

Property.cs

public class Property
{
    [Key]
    public int id { get; set; }    

    public int typeId { get; set; }
    public type type { get; set; }

    public int heatingId { get; set; }
    public heating heating { get; set; }

    public int? categoryId { get; set; }
    public category category { get; set; }

    public int? subCategoryId { get; set; }
    public subCategory subCategory { get; set; }

    public int? secondSubCategoryId { get; set; }
    public SecondSubCategory SecondSubCategory { get; set; }
}

Response without including category and subCategory and secondSubCategory :

 {
        "id": 14,        
        "typeId": 1,
        "type": {
            "id": 1,
            "typeName": "Flat"
        },
        "heatingId": 4,
        "heating": {
            "id": 4,
            "heatingName": "Conditioning"
        },
        "categoryId": 1,
        "category": null,
        "subCategoryId": 2,
        "subCategory": null,
        "secondSubCategoryId": 3,
        "secondSubCategory": null
    }

Response with including category and subCategory and secondSubCategory :

{
        "id": 14,        
        "typeId": 1,
        "type": {
            "id": 1,
            "typeName": "Flat"
        },
        "heatingId": 4,
        "heating": {
            "id": 4,
            "heatingName": "Conditioning"
        },
        "categoryId": null,
        "category": null,
        "subCategoryId": null,
        "subCategory": null,
        "secondSubCategoryId": null,
        "secondSubCategory": null
}

Category.cs

public class category
  {
    public int id { get; set; }

    public string category_Name { get; set; }

    public IList<subCategory> subCategories { get; set; }
    public Property Property { get; set; }

  }

subCategory.cs:

public class subCategory
    {
    public int id { get; set; }

    public string subCategoryName { get; set; }



    public int CategoryId { get; set; }
    public category category { get; set; }

    public IList<SecondSubCategory> secondSubCategories { get; set; }
    public Property Property { get; set; }
  }

secondSubCategory.cs:

public class SecondSubCategory
  {
    public int id { get; set; }

    public string subCategoryName { get; set; }


    public int subCategoryId { get; set; }
    public subCategory subCategory { get; set; }

    public Property Property { get; set; }
  }

Answer 1

You can't include directly to Property category, subcategory and subsubcategory objects since some of them only inside of each other. So try to do this by good old way:

public IActionResult getAllProperties()
{
 var properties = ( from p in  db.properties
 join  c in  db.category on p.categoryId equals c.Id into cj
 from c in cj.DefaultIfEmpty()
 join  sc in  db.subCategory on on p.subCategoryId equals sc.Id into scj
 from sc in scj.DefaultIfEmpty()
 join  ssc in  db.secondSubCategory on on p.secondSubCategoryId equals ssc.Id into sscj
 from ssc in sscj.DefaultIfEmpty()
join  h in  db.heatings on p.heatingId equals h.Id 
join  t in  db.types on p.typeId equals t.Id 
orderby  p.id descending
select new Property {
id= p.id 
 typeId=p.typeId,
 type=t,
 heatingId = p.heatingId,
 heating=h, 
 categoryId = p.categoryId,
category =c,
subCategoryId= p.subCategoryId,
subCategory=sc,
secondSubCategoryId=p.secondSubCategoryId,
SecondSubCategory=ssc 
}).ToList();

 return Ok(properties);
}

or a little shorter:

var properties = ( from p in  db.properties
 join  c in  db.category on p.categoryId equals c.Id into cj
 from c in cj.DefaultIfEmpty()
 join  sc in  db.subCategory on on p.subCategoryId equals sc.Id into scj
 from sc in scj.DefaultIfEmpty()
 join  ssc in  db.secondSubCategory on on p.secondSubCategoryId equals ssc.Id into sscj
 from ssc in sscj.DefaultIfEmpty()
orderby  p.id descending
select new Property {
id= p.id 
 typeId=p.typeId,
 type=p.type,
 heatingId = p.heatingId,
 heating=p.heading, 
 categoryId = p.categoryId,
category =c,
subCategoryId= p.subCategoryId,
subCategory=sc,
secondSubCategoryId=p.secondSubCategoryId,
SecondSubCategory=ssc 
}).ToList();

Answer 2

As I got from your Entities you must follow the hierarchy principles in the design of your database. This is my recommendation:

public class Property
{
    [Key]
    public int id { get; set; }    

    public int typeId { get; set; }
    public type type { get; set; }

    public int heatingId { get; set; }
    public heating heating { get; set; }

    public int? categoryId { get; set; }
    public category category { get; set; }
}

public class category
  {
    public int id { get; set; }
    public string category_Name { get; set; }

    public IList<subCategory> subCategories { get; set; }
    public Property Property { get; set; }

  }

public class subCategory
  {
    public int id { get; set; }
    public string subCategoryName { get; set; }

    public int CategoryId { get; set; }
    public category category { get; set; }

    public IList<SecondSubCategory> secondSubCategories { get; set; }
  }


public class SecondSubCategory
  {
    public int id { get; set; }
    public string secondCategoryName { get; set; }

    public int subCategoryId { get; set; }
    public subCategory subCategory { get; set; }
  }

And here is how you can retrieve your hierarchy data:

public IActionResult getAllProperties()
{
      var properties = db.properties
                         .Include(cat => cat.category)
                             .ThenInclude(sub => sub.subCategory)
                                .ThenInclude(sec => sec.SecondSubCategory)
                         .Include(e => e.heating)
                         .Include(e => e.type)        
                         .OrderByDescending(x => x.id)
                         .ToList();
      return Ok(properties);
}

Answer 3

I use code first to generate the database with your models, and test with some data. If I don't include category and subCategory and secondSubCategory, the result is the same as yours, but when I include them, there will be an expection:

JsonException: A possible object cycle was detected which is not supported. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32.

Then I use the NewtonsoftJson to handle the ReferenceLoopHandling problem

services.AddControllersWithViews()
    .AddNewtonsoftJson(options =>
     options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore);

After that, I can get the category and subCategory and secondSubCategory from the query.