c# - LINQ MVC View 模型 : Multiple joins to the same table with optional field

Question

给定以下数据库结构

类别

身份证类别名称ResID父类别(可选)

资源

身份证文本朗

并给定一个 ViewModel

public class CategoryViewModel
{
    public int ID { get; set; }
    public int CategoryNameResID { get; set; }
    public string CategoryName { get; set; }

    public int ParentCategory { get; set; }
    public string ParentCategoryName { get; set; }
}

我想获取包含 ParentCategoryName 的所有类别的列表

到目前为止我所做的是:

var categories = (from cat in db.Categories
                  join res in db.Resources on cat.CategoryNameResID equal res.ID
                  select new CategoryViewModel{
ID = cat.ID,
CategoryNameResID = cat.CategoryNameResID,
CategoryName = res.Text,
ParentCategory = cat.ParentCategory,
ParentCategoryName = (from p in db.Resources
where p.ID == cat.ParentCategory
select p.Text)
}).ToList();

我不知道如何在不必再次迭代的情况下获取 ParentCategoryName，这绝对是错误的。

Answer 1

试试这个:

(from cat in cats
join res in resources on cat.ResId equals res.Id let categoryName = res.Text
join cat1 in cats on cat.ParentId equals cat1.Id into parentJoin
from pj in parentJoin.DefaultIfEmpty() let parentCatResId =pj==null?0: pj.ResId
join res1 in resources on parentCatResId equals res1.Id into resJoin
from res2 in resJoin.DefaultIfEmpty() let parentName = (res2==null?string.Empty:res2.Text)
    select new CategoryVM
    {
        Id = cat.Id,
        ResId = cat.ResId,
        CatName = categoryName,
        ParentId = cat.ParentId,
        ParentName = parentName
    }).ToList();