Left join not bringing all results when you also select a list of other objects in EFCore 3.1.11

user_15102433 0
Viewed 91

Let me just preface this with acknowledging that this bug no longer occurs with newer versions of EFCore (5+), but unfortunately we cannot update our project right now.

I created a sample code in order to better show how this bug happens. In this sample code I have a DbContext with three tables: Companies, Locations and Employees. A company can have 1 or more employees, and 0 or more locations. I want to create a query of companies and locations that would return like this:

Company   | Location   | Employees
-----------------------------------------------
Company 1 | Location 1 | Employee 1, Employee 2
Company 1 | Location 2 | Employee 1, Employee 2
Company 2 |            | Employee 3, Employee 4

Different locations for the same company should be in separate lines, and if the company doesn't have a location, it should still show up.

I came up with the following query using Linq, and it does what I described above in EFCore 5+, but not in 3.1.11

(from c in ctx.Companies

join _l in ctx.Locations on c.Id equals _l.CompanyId into lGroup
from l in lGroup.DefaultIfEmpty()

where
c.Id == new Guid("FFF461F3-9E38-4DA0-8B31-AC4D0C90A4D0") // Id of Company 1 in the code block above

select new
{
    CompanyName = c.Name,
    LocationName = l == null ? string.Empty : l.Name,
    Employees = c.Employees.Select(e => e.Name)
}).AsNoTracking().ToList();

In EFCore 5+ this returns two items in the resulting list, exactly like I wanted. But in 3.1.11 it returns only one line, with the first location. The second line with the second location is missing.

The thing is, if you change the query to not use the DefaultIfEmpty() (basically changing it from a left join to an inner join) it works exactly as intended, but in that case if you try querying a company with no locations, it won't show up (since it's an inner join with location).

Does anyone know if there's a way I could achieve this in an efficient manner?

Thank you in advance.

EDIT: This is the generated query:

SELECT [c].[Name], CASE
    WHEN [l].[Id] IS NULL THEN N''
    ELSE [l].[Name]
END, [c].[Id], [e].[Name], [e].[Id]
FROM [Companies] AS [c]
LEFT JOIN [Locations] AS [l] ON [c].[Id] = [l].[CompanyId]
LEFT JOIN [Employees] AS [e] ON [c].[Id] = [e].[CompanyId]
WHERE [c].[Id] = 'f2c44d70-d713-42dc-b92a-e93b175fc351'
ORDER BY [c].[Id], [e].[Id]
entity-framework-core .net-core entity-framework linq c#
3 Answers

try this:

var q= ctx.Locations
       .Include(i=>i.Company)
        .Select (i=> new
     {
    CompanyName = i.Company.Name,
    LocationName =i.Name,
    Employees = i.Company.Employees.Select(e => e.Name)
}).AsNoTracking().ToList()
.Union(
    ctx.Companies
       .Where(i=>i.Locations==null)
        .Select (i=> new
     {
    CompanyName = i..Name,
    LocationName =stringEmpty,
    Employees = i.Employees.Select(e => e.Name)
}).AsNoTracking().ToList());
user_113922900

You can add join with Employees and handle the result in memory:

(from c in ctx.Companies

join _l in ctx.Locations on c.Id equals _l.CompanyId into lGroup
from l in lGroup.DefaultIfEmpty()
join _e in ctx.Employees on c.Id equals _e.CompanyId into eGroup
from e in eGroup.DefaultIfEmpty()
where
c.Id == new Guid("FFF461F3-9E38-4DA0-8B31-AC4D0C90A4D0") 

select new
{
    CId = c.Id,
    CompanyName = c.Name,
    LId = l.Id, 
    LocationName = l == null ? string.Empty : l.Name,
    EmployeeName = e.Name
})
.AsNoTracking()
.AsEnumerable()
.GroupBy(r => new {CId, LId})
.Select(g = > new 
{
    CompanyName = g.First().CompanyName ,
    LocationName = g.First().LocationName ,
    Employees = g.Select(e => e.EmployeeName)
})
.ToList();
user_25012790

The workaround for EFC 3.1 is to split the query on two parts: server side (LINQ to Entities) query selecting only the data needed, but with "normal" collection type projection of correlated sub collections, and client side (LINQ to Objects) query doing the desired flattening / multiplication.

Something like this

(from c in ctx.Companies
 select new
 {
     CompanyName = c.Name,
     Locations = c.Locations.Select(e => new { e.Name }), // <-- the data needed
     Employees = c.Employees.Select(e => e.Name),
 })
.AsEnumerable() // <-- switch to client side context
.SelectMany(c => c.Locations.DefaultIfEmpty(), (c, l) => new
{
    c.CompanyName,
    LocationName = l == null ? string.Empty : l.Name,
    c.Employees
}).ToList();

In case you don't have c.Locations collection navigation property (as you should), replace it with its equivalent

ctx.Locations.Where(e => e.CompantyId == c.Id)

which is what EFC does automatically with collection navigation properties inside L2E query.

user_52025630
Related