如何将LINQ查询结果转换为包含关联实体的完整WorkItem复杂模型

你有两种常用实现方式，可根据你的业务场景选择：

方案1：定义专属DTO（推荐） #

先定义和返回字段匹配的强类型数据传输对象，再修改LINQ的select语句返回该类型实例即可，这也是分层架构中最常用的做法，避免直接暴露数据库实体。

1. 定义DTO类：

public class WorkItemDto
{
    public int WorkItemID { get; set; }
    public int PriorityFlag { get; set; }
    public int PriorityIndex { get; set; }
    public DateTime? CompletionDateTime { get; set; }
    public string LocationName { get; set; }
    public string PropertyFullName { get; set; }
    public string AssignedTo1 { get; set; }
    public string AssignedTo2 { get; set; }
    public string AssignedTo3 { get; set; }
    public string CategoryName { get; set; }
    public string SubCategoryName { get; set; }
    public string PriorityDesc { get; set; }
}

2. 修改查询的select部分：
   把原来的select new { ... }替换为：

select new WorkItemDto
{
    WorkItemID = wi.WorkItemID, 
    PriorityFlag = wi.PriorityFlag,
    PriorityIndex = wi.PriorityIndex,
    CompletionDateTime = wi.CompletionDateTime,
    LocationName = l.LocationName,
    PropertyFullName = p.PropertyFullName,
    AssignedTo1 = wi.AssignedTo1,
    AssignedTo2 = wi.AssignedTo2,
    AssignedTo3 = wi.AssignedTo3,
    CategoryName = c.CategoryName,
    SubCategoryName = sc.SubCategoryName,
    PriorityDesc = pl.ValueTitle 
}

修改后query的返回类型就是IQueryable<WorkItemDto>，后续可通过ToList()等方法直接获取强类型集合。

方案2：直接填充带导航属性的WorkItem实体 #

如果你的业务要求必须返回原生的WorkItem数据库实体，且需要填充所有关联的外键导航属性，可直接在select中构造实体并给导航属性赋值：

select new WorkItem
{
    WorkItemID = wi.WorkItemID, 
    PriorityFlag = wi.PriorityFlag,
    PriorityIndex = wi.PriorityIndex,
    CompletionDateTime = wi.CompletionDateTime,
    AssignedTo1 = wi.AssignedTo1,
    AssignedTo2 = wi.AssignedTo2,
    AssignedTo3 = wi.AssignedTo3,
    // 填充关联导航属性
    MaintenanceItem = new MaintenanceItem
    {
        MaintenanceItemID = mi.MaintenanceItemID,
        Category = new Category { CategoryName = c.CategoryName },
        SubCategory = new SubCategory { SubCategoryName = sc.SubCategoryName },
        Property = new Property
        {
            PropertyFullName = p.PropertyFullName,
            Location = new Location { LocationName = l.LocationName }
        }
    },
    // 若WorkItem已扩展PriorityDesc字段直接赋值即可
    PriorityDesc = pl.ValueTitle
}

优化提示 #

如果你使用的是EF/EF Core，且实体的导航关系已正确配置，可不用手写这么多join语句，直接用Include+ThenInclude自动加载关联属性，代码可读性更高：

var query = context.WorkItems
    .Include(wi => wi.MaintenanceItem)
        .ThenInclude(mi => mi.Property)
            .ThenInclude(p => p.Location)
    .Include(wi => wi.MaintenanceItem)
        .ThenInclude(mi => mi.Category)
    .Include(wi => wi.MaintenanceItem)
        .ThenInclude(mi => mi.SubCategory)
    .Join(context.PickLists,
        wi => new { Id = wi.PriorityFlag, V = "Priority" },
        pl => new { Id = pl.ValueKey, V = pl.ValueType },
        (wi, pl) => new { wi, pl })
    .Where(x => x.wi.AssignedTo1 == username)
    .OrderByDescending(x => x.wi.PriorityFlag)
    .ThenBy(x => x.wi.PriorityIndex)
    .ThenBy(x => x.wi.WorkItemID)
    .Select(x => new WorkItemDto
    {
        WorkItemID = x.wi.WorkItemID,
        PriorityFlag = x.wi.PriorityFlag,
        PriorityIndex = x.wi.PriorityIndex,
        CompletionDateTime = x.wi.CompletionDateTime,
        LocationName = x.wi.MaintenanceItem.Property.Location.LocationName,
        PropertyFullName = x.wi.MaintenanceItem.Property.PropertyFullName,
        AssignedTo1 = x.wi.AssignedTo1,
        AssignedTo2 = x.wi.AssignedTo2,
        AssignedTo3 = x.wi.AssignedTo3,
        CategoryName = x.wi.MaintenanceItem.Category.CategoryName,
        SubCategoryName = x.wi.MaintenanceItem.SubCategory.SubCategoryName,
        PriorityDesc = x.pl.ValueTitle
    });

内容的提问来源于stack exchange，提问作者chilluk