Left Join

[ghost]

I am not really sure if this is a LINQ or EF Core issue, but not having Left Joins is problematic.

In EF Core 2.2.6, there was a work around by by joining the table into name and then adding from another name in name.DefaultIfEmpty(). This worked.

In EF Core 3.1, this no longer works. If I understand correctly, EF Core tries to translate the LINQ syntax into a query that runs on the server. The query that it generates for the Left Join work around seems very convoluted and results in an error.

Are there plans to support a Left Join or to translate the work around into a left join?

Is there a functioning syntax to do a left join in EF Core 3.1?

Thanks.

Steps to reproduce

Further technical details

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET Core 3.0)
Operating system:
IDE: (e.g. Visual Studio 2019 16.3)

[smitpatel]

@GRS-Leedo - I am not sure what pattern you are exactly referring to which worked in 2.2. Without looking at the LINQ query, it is hard for me to say if that was supported pattern or if it just worked in 2.2 because it evaluated everything on client.

For 3.1 here is detailed documentation on LINQ pattern which translates to Left Join in SQL https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators#left-join

[ghost]

Could you give me a chance to respond before you immediately close this?

As mentioned in my post
"...by by joining the table into name and then adding from another name in name.DefaultIfEmpty()"

and more specifically, from the link,

var query = from b in context.Set<Blog>() join p in context.Set<Post>() on b.BlogId equals p.BlogId into grouping from p in grouping.DefaultIfEmpty() select new { b, p };

Does not work in 3.1. It throws errors. Beyond not working, the translated query is the convoluted combination of outter and inner joins when a simple translation to LEFT JOIN would be so much cleaner.

To be VERY specific, this query ran perfectly in 2.2.6
` PL = await (from lab in _context.tbl_labels
join od in _context.Tbl_OrderDetail on lab.serialID equals od.SerialID
join ord in _context.Tbl_Orders on od.OrderNo equals ord.OrdNo
join un in _context.TblUnits on lab.AptID equals un.AptID
join bld in _context.tbl_Building1 on un.BldgID equals bld.Bldg_ID into b
join wh in _context.Tbl_Warehouses on ord.Warehouse_ID equals wh.Warehouse_ID into w
join pf in _context.TblProjectFinancials on ord.JobNumber equals pf.JobNumber
where lab.scanID == ScanID
from m in w.DefaultIfEmpty()
from n in b.DefaultIfEmpty()
select new ECRFIDPrinterLabel
{
AptNumber = od.AptNo,
BldgNo = n.BuildingNumber,
Channel = pf.ChannelID.ToString(),
CPU = (ord.ShipVia == "CPU") ? "CPU" : "",
Description = od.Description,
Finish = od.Finish,
Item = lab.item,
JobName = ord.PTPO,
JobNumber = ord.JobNumber,
LoadingTicketNumber = Convert.ToInt32(ord.LoadingTicketNumber),
OrderNumber = ord.OrdNo,
ScanID = ScanID,
Ship_Date = Convert.ToDateTime(ord.Ship_Date),
StopNumber = Convert.ToInt32(ord.ShippingStopNum),
Warehouse = m.WarehouseName

                        }).FirstOrDefaultAsync();`

In 3.1, the result is
Processing of the LINQ expression 'DbSet
.Join(
outer: DbSet,
inner: lab => lab.serialID,
outerKeySelector: od => (Nullable)od.SerialID,
innerKeySelector: (lab, od) => new {
lab = lab,
od = od
})
.Join(
outer: DbSet,
inner: <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.od.OrderNo,
outerKeySelector: ord => (Nullable)ord.OrdNo,
innerKeySelector: (<>h__TransparentIdentifier0, ord) => new {
<>h__TransparentIdentifier0 = <>h__TransparentIdentifier0,
ord = ord
})
.Join(
outer: DbSet,
inner: <>h__TransparentIdentifier1 => <>h__TransparentIdentifier1.<>h__TransparentIdentifier0.lab.AptID,
outerKeySelector: un => un.AptID,
innerKeySelector: (<>h__TransparentIdentifier1, un) => new {
<>h__TransparentIdentifier1 = <>h__TransparentIdentifier1,
un = un
})
.GroupJoin(
outer: DbSet,
inner: <>h__TransparentIdentifier2 => <>h__TransparentIdentifier2.un.BldgID,
outerKeySelector: bld => (Nullable)bld.Bldg_ID,
innerKeySelector: (<>h__TransparentIdentifier2, b) => new {
<>h__TransparentIdentifier2 = <>h__TransparentIdentifier2,
b = b
})' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

Again 2.2.6 worked. 3.1 doesn't work.

Thoughts?

[smitpatel]

The last paragraph in the documentation I linked specifically points out

The above pattern creates a complex structure in the expression tree. Because of that, EF Core requires you to flatten out the grouping results of the GroupJoin operator in a step immediately following the operator. Even if the GroupJoin-DefaultIfEmpty-SelectMany is used but in a different pattern, we may not identify it as a Left Join.

In the complex query you have posted above, you are grouping into b & then into w and then another join before you use DefaultIfEmpty to flatten it out. it is not same as the first simple query you posted. The simple query you posted actually works correctly in EF Core 3.1 (It is same as the example shared in documentation.)

If you rewrite your complex query so that after grouping into something next line is flattening it out using DefaultIfEmpty then it would at least work for left join.

[ghost]

As suggested
` PL = await (from lab in _context.tbl_labels
join od in _context.Tbl_OrderDetail on lab.serialID equals od.SerialID
join ord in _context.Tbl_Orders on od.OrderNo equals ord.OrdNo
join un in _context.TblUnits on lab.AptID equals un.AptID
join pf in _context.TblProjectFinancials on ord.JobNumber equals pf.JobNumber
join bld in _context.tbl_Building1 on un.BldgID equals bld.Bldg_ID into b
join wh in _context.Tbl_Warehouses on ord.Warehouse_ID equals wh.Warehouse_ID into w
where lab.scanID == ScanID
from m in w.DefaultIfEmpty()
from n in b.DefaultIfEmpty()
select new ECRFIDPrinterLabel
{
AptNumber = od.AptNo,
BldgNo = n.BuildingNumber,
Channel = pf.ChannelID.ToString(),
CPU = (ord.ShipVia == "CPU") ? "CPU" : "",
Description = od.Description,
Finish = od.Finish,
Item = lab.item,
JobName = ord.PTPO,
JobNumber = ord.JobNumber,
LoadingTicketNumber = Convert.ToInt32(ord.LoadingTicketNumber),
OrderNumber = ord.OrdNo,
ScanID = ScanID,
Ship_Date = Convert.ToDateTime(ord.Ship_Date),
StopNumber = Convert.ToInt32(ord.ShippingStopNum),
Warehouse = m.WarehouseName

                        }).FirstOrDefaultAsync();`

Result
Processing of the LINQ expression 'DbSet
.Join(
outer: DbSet,
inner: lab => lab.serialID,
outerKeySelector: od => (Nullable)od.SerialID,
innerKeySelector: (lab, od) => new {
lab = lab,
od = od
})
.Join(
outer: DbSet,
inner: <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.od.OrderNo,
outerKeySelector: ord => (Nullable)ord.OrdNo,
innerKeySelector: (<>h__TransparentIdentifier0, ord) => new {
<>h__TransparentIdentifier0 = <>h__TransparentIdentifier0,
ord = ord
})
.Join(
outer: DbSet,
inner: <>h__TransparentIdentifier1 => <>h__TransparentIdentifier1.<>h__TransparentIdentifier0.lab.AptID,
outerKeySelector: un => un.AptID,
innerKeySelector: (<>h__TransparentIdentifier1, un) => new {
<>h__TransparentIdentifier1 = <>h__TransparentIdentifier1,
un = un
})
.Join(
outer: DbSet,
inner: <>h__TransparentIdentifier2 => <>h__TransparentIdentifier2.<>h__TransparentIdentifier1.ord.JobNumber,
outerKeySelector: pf => pf.JobNumber,
innerKeySelector: (<>h__TransparentIdentifier2, pf) => new {
<>h__TransparentIdentifier2 = <>h__TransparentIdentifier2,
pf = pf
})
.GroupJoin(
outer: DbSet,
inner: <>h__TransparentIdentifier3 => <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.un.BldgID,
outerKeySelector: bld => (Nullable)bld.Bldg_ID,
innerKeySelector: (<>h__TransparentIdentifier3, b) => new {
<>h__TransparentIdentifier3 = <>h__TransparentIdentifier3,
b = b
})' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

Moving the lines of code made no difference.

Changing the code to only have 1 Left Join
` PL = await (from lab in _context.tbl_labels
join od in _context.Tbl_OrderDetail on lab.serialID equals od.SerialID
join ord in _context.Tbl_Orders on od.OrderNo equals ord.OrdNo
join un in _context.TblUnits on lab.AptID equals un.AptID
join pf in _context.TblProjectFinancials on ord.JobNumber equals pf.JobNumber
join bld in _context.tbl_Building1 on un.BldgID equals bld.Bldg_ID into b
//join wh in _context.Tbl_Warehouses on ord.Warehouse_ID equals wh.Warehouse_ID into w
where lab.scanID == ScanID
//from m in w.DefaultIfEmpty()
from n in b.DefaultIfEmpty()
select new ECRFIDPrinterLabel
{
AptNumber = od.AptNo,
BldgNo = n.BuildingNumber,
Channel = pf.ChannelID.ToString(),
CPU = (ord.ShipVia == "CPU") ? "CPU" : "",
Description = od.Description,
Finish = od.Finish,
Item = lab.item,
JobName = ord.PTPO,
JobNumber = ord.JobNumber,
LoadingTicketNumber = Convert.ToInt32(ord.LoadingTicketNumber),
OrderNumber = ord.OrdNo,
ScanID = ScanID,
Ship_Date = Convert.ToDateTime(ord.Ship_Date),
StopNumber = Convert.ToInt32(ord.ShippingStopNum),
//Warehouse = m.WarehouseName

                        }).FirstOrDefaultAsync();`

Resulted in
Processing of the LINQ expression 'DbSet
.Join(
outer: DbSet,
inner: lab => lab.serialID,
outerKeySelector: od => (Nullable)od.SerialID,
innerKeySelector: (lab, od) => new {
lab = lab,
od = od
})
.Join(
outer: DbSet,
inner: <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.od.OrderNo,
outerKeySelector: ord => (Nullable)ord.OrdNo,
innerKeySelector: (<>h__TransparentIdentifier0, ord) => new {
<>h__TransparentIdentifier0 = <>h__TransparentIdentifier0,
ord = ord
})
.Join(
outer: DbSet,
inner: <>h__TransparentIdentifier1 => <>h__TransparentIdentifier1.<>h__TransparentIdentifier0.lab.AptID,
outerKeySelector: un => un.AptID,
innerKeySelector: (<>h__TransparentIdentifier1, un) => new {
<>h__TransparentIdentifier1 = <>h__TransparentIdentifier1,
un = un
})
.Join(
outer: DbSet,
inner: <>h__TransparentIdentifier2 => <>h__TransparentIdentifier2.<>h__TransparentIdentifier1.ord.JobNumber,
outerKeySelector: pf => pf.JobNumber,
innerKeySelector: (<>h__TransparentIdentifier2, pf) => new {
<>h__TransparentIdentifier2 = <>h__TransparentIdentifier2,
pf = pf
})
.GroupJoin(
outer: DbSet,
inner: <>h__TransparentIdentifier3 => <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.un.BldgID,
outerKeySelector: bld => (Nullable)bld.Bldg_ID,
innerKeySelector: (<>h__TransparentIdentifier3, b) => new {
<>h__TransparentIdentifier3 = <>h__TransparentIdentifier3,
b = b
})' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

No difference
Stack Trace
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at ECScanToPrintApp.Common.RFIDLabelInformation.d__3.MoveNext() in C:\Users\gswanson\source\Workspaces\ECScanToPrintApp\ECScanToPrintApp\Common\RFIDLabelInformation.cs:line 31

Again, this worked in 2.2.6, but does not work in 3.1.

[smitpatel]

join bld in _context.tbl_Building1 on un.BldgID equals bld.Bldg_ID into b
//join wh in _context.Tbl_Warehouses on ord.Warehouse_ID equals wh.Warehouse_ID into w
where lab.scanID == ScanID
//from m in w.DefaultIfEmpty()
from n in b.DefaultIfEmpty()

This is not writing DefaultIfEmpty in clause right after group join. You have a where clause in between. We do not support that pattern. No matter if it worked in 2.2.6.

Write those lines like this as clearly showed in documentation.

join bld in _context.tbl_Building1 on un.BldgID equals bld.Bldg_ID into b
from n in b.DefaultIfEmpty()//join wh in _context.Tbl_Warehouses on ord.Warehouse_ID equals wh.Warehouse_ID into w
//from m in w.DefaultIfEmpty()
where lab.scanID == ScanID

[ghost]

Thank you very much for your help. It is truly appreciated.

As a secondary question, why not simply have a left join rather than having to do pattern matching? It seems like it would be easier.

[smitpatel]

@GRS-Leedo - Queryable operators are defined in BCL, we just merely use it. You can submit a feature request on https://github.com/dotnet/runtime

[ilya-chumakov]

The pattern described in https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators#left-join
shocks me every time I search how to make a simple left join in EF. In addition to is complexity and cumbersomeness, one more drawback occurs: looking on that GroupJoin + SelectMany + DefaultIsEmpty combo (in terms of LINQ chain methods) you can't say what SQL it generates until you've learned the whole pattern by heart. Reading code would be much simpler if we had LeftJoin method.

[StevenRasmussen]

Anyone wanting this MUST go to #361 and click both the up arrow and thumbs up just below the last "comment" in the first post for your "vote" to be counted:

Yes... it looks like you're voting for the comment... which is against the idea... but you're actually voting for the idea. You can read the discussion around that in the comments.