EF Core 2.1 Query - LEFT JOIN

Question

I am trying to select the equivalent of

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

My c# equivalent is

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

This is translating as an initial query for the Units...

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]

and then a subquery for each Unit to determine its GroupId. (Repeat per record)

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]

It's also failing if the Unit isn't linked.

How do I modify my query to return the expected results? I expect all units, with a null in ParentGroupId if not link. Additionally, what's a good resource to read for learning this syntax? Finding a lot of previous version examples that aren't quite valid.

Does https://stackoverflow.com/questions/39919230/asp-net-core-entityframework-core-left-outer-join-in-linq help? — mjwills, Jun 01 '18 at 01:19
@mjwills Thanks for that, the `.Select(d => d.GroupId).SingleOrDefault()` means it isn't throwing an error, but still not working. Need to investigate more. The slightly different syntax is throwing me off. — Hecatonchires, Jun 01 '18 at 01:41

score 1 · Accepted Answer · answered Jul 16 '18 at 23:01

I was eventually able to get this working using a view in the database linked to an EF class. The problems I had with this earlier were due to my leaving off the {get; set;} in my initial class definition.

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

}

Then in the DBContext

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

And finally, loading data and converting to JSON

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

    return new JsonResult(OrgChartNodes);
}

EF Core 2.1 Query - LEFT JOIN

1 Answers1