实体框架核心:使用include从相关表中获取数据

Question

我正在尝试从五个表中获取数据：Category-subCategory-second subCategory-type-heating，它们都与主表(属性(相关。

表(类别-子类别-第二子类别(与层(类别=>子类别=<第二子分类(类似

我试着用获取数据

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

但返回的数据具有类型和加热字段的值，但具有(categoryId和subCategoryId以及secondSubCategoryId(的空值，因为知道这些字段具有值

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

响应不包括类别和子类别以及第二子类别：

{
"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
}

包含类别和子类别以及第二子类别的响应：

{
"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
}

类别.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; }
}

子类别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

我首先使用代码用您的模型生成数据库，并用一些数据进行测试。如果我不包括category和subCategory以及secondSubCategory，结果与你的相同，但当我包括它们时，会有一个预期：

JsonException：检测到可能的对象循环，这是不受支持的。这可能是由于周期，或者如果对象深度大于32的最大允许深度。

然后我使用NewtonsoftJson来处理ReferenceLoop处理问题

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

之后，我可以从查询中获得类别和子类别以及secondSubCategory。

Answer 2

正如我从您的实体中了解到的那样，在设计数据库时必须遵循层次结构原则。这是我的建议：

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

以下是如何检索层次结构数据：

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

您不能直接包含到Property类别、子类别和subsubsubcategory对象中，因为其中一些对象只在彼此内部。所以试着用老办法：

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

或者短一点：

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