c# - EF Core 2.1 查询 - 左连接

Question

我正在尝试选择等同于

SELECT u.NodeId, 
       u.Name, 
       u.TierId, 
       u.OrgCode, 
       o.OrgName, 
       gtu.GroupId as ParentGroupId
FROM Unit u
LEFT JOIN GroupToUnit gtu ON u.NodeId = gtu.NodeId
JOIN Organisation o ON u.OrgCode = o.OrgCode

我的 C# 等效项是

IList<OrgChartNode> unitNodes = _db.Units
                                    .Where(u => u.OrgCode.Equals(OrgCode))
                                    .Select(u => new OrgChartNode
                                    {
                                         Id = u.NodeId,
                                         Name = u.Name,
                                         TierId = 0,
                                         ParentGroupId = u.GroupLinks.First().GroupId,
                                         OrgName = u.Organisation.Name,
                                         OrgCode = u.OrgCode,
                                         ContactName = null,
                                         ContactEmail = null,
                                         ContactPhone = null,
                                         ContactId = null,
                                    })
                                    .OrderBy(u => u.Name)
                                    .AsNoTracking()
                                    .ToList();

这被翻译成对单位的初始查询...

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (5ms) [Parameters=[@__OrgCode_0='?' (Size = 5)], CommandType='Text', CommandTimeout='30']
SELECT [u].[NodeId] AS [Id0], [u].[ID] AS [Name0], [u.Organisation].[Name] AS [OrgName], [u].[OrgCode]
FROM [Report_Unit] AS [u]
INNER JOIN [Report_Organisation] AS [u.Organisation] ON [u].[OrgCode] = [u.Organisation].[OrgCode]
WHERE [u].[OrgCode] = @__OrgCode_0
ORDER BY [Name0]

然后是每个 Unit 的子查询以确定其 GroupId。 (每条记录重复)

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (1ms) [Parameters=[@_outer_NodeId='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [r1].[GroupId]
FROM [Report_Link_Group_to_Unit] AS [r1]
WHERE @_outer_NodeId = [r1].[NodeId]

如果单元未链接，它也会失败。

如何修改查询以返回预期结果？我希望所有单位，如果没有链接，ParentGroupId 中的值为 null。此外，学习此语法的好资源是什么？找到很多不太有效的以前版本的例子。

Answer 1

我最终能够使用数据库中链接到 EF 类的 View 来实现这一点。我之前遇到的问题是由于我离开了 {get;在我的初始类定义中设置；。

public class OrgChartNode
{
    [Required]
    public int Id { get; set; }

    [MaxLength(100)]
    public string Name { get; set; }

    [Range(0, 6)]
    public int TierId { get; set; }

    public int? ParentGroupId { get; set; }

    [MaxLength(50)]
    public string OrgName { get; set; }

    [MaxLength(5)]
    public string OrgCode { get; set; }

    [MaxLength(100)]
    public string ContactName { get; set; }

    [MaxLength(255)]
    public string ContactEmail { get; set; }

    [MaxLength(12)]
    public string ContactPhone { get; set; }

    public int? ContactId { get; set; }

    public int? GroupTypeId { get; set; }

}

然后在DBContext中

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    ...
    builder
        .Query<OrgChartNode>()
        .ToView("vwAllOrgchartWithUnits");
}

最后，加载数据并转换为 JSON

public JsonResult OnGet(string OrgCode)
{
    OrgChartNodes = _db.OrgChartNodes
                    .Where(g => g.OrgCode.Equals(OrgCode))
                    .ToList();

    return new JsonResult(OrgChartNodes);
}