Left Join issue

[igor-tkachev]

The following code is not working:

from p in db.Parent
join c in db.Child on p.Value1 equals c.ParentID into g
from c in g.DefaultIfEmpty()
where g == null
select p.ParentID;

LinqToDB.LinqToDBException : The LINQ expression 'g' could not be converted to SQL.

Worked in 5.4.1.

[sdanyliv]

@igor-tkachev, which SQL do you expect from where g == null. Looks like a bug in your query.

[igor-tkachev]

It is not a bug, it is a bloody enterprise which already works for at least 10 years. 5.4.1 generates the same code as for where c == null.

[sdanyliv]

@igor-tkachev, translation of GroupJoin handling is completely rewritten and we need special complex "crutches" to make it work. We do not store information about LEFT JOIN or any join in this step, it is just GroupJoin result, which has no direct SQL tarnsaltion.

How about

1. Roslyn analyzer to find such problems in existing code
2. You can register IQueryExpressionInterceptor and correct expression tree for such "bloody" projects. Pattern is comlex, but doeable. The same query in Method syntax:

var q = db.Parent
	.GroupJoin(db.Child, p => p.Value1, c => c.ParentID, (p, g) => new { p, g })
	.SelectMany(t => t.g.DefaultIfEmpty(), (t, c) => new { t, c })
	.Where(t => t.t.g == null)
	.Select(t => t.t.p.ParentID);

[viceroypenguin]

@sdanyliv I can see a value in this type of query. It's basically a left anti-join: give me all items on the left that have no matches on the right.

Proper SQL would be:

select ParentId
from Parent p 
  left join Child c
   on p.Value1 = c.ParentId
where c.ParentId is null;

alternatively:

select ParentId
from Parent p
where not exists (
  select *
  from Child c
  where c.ParentId = p.Value1);

[viceroypenguin]

@igor-tkachev An alternative query that would be more explicitly correct that should work:

from p in db.Parent
where !db.Child.Any(c => c.ParentID == p.Value1)
select p.ParentID;

[sdanyliv]

@viceroypenguin, it's not about writing the ideal query, but rather about handling problematic customer queries without needing to rewrite them or at least don't allow to compile.

[viceroypenguin]

@sdanyliv my point is, I don't agree that writing where g == null is a bug; it's a valid query. We may choose not translate it, but it's not that ridiculous of a query to write.

[sdanyliv]

I don't agree that writing where g == null is a bug.

Modern compilers will warn about comparing a non-nullable g to null. What does it even mean for the collection to be null if GroupJoin doesn't permit that scenario?

Yes, I can rewrite g == null as !g.Any() to remove the LEFT JOIN, but I prefer not to open Pandora's box here - the behavior should remain explicitly defined.

[viceroypenguin]

What does it even mean for the collection to be null if GroupJoin doesn't permit that scenario?

Ah, good point. Then yes, I agree.

[igor-tkachev]

@sdanyliv I understand that this is kind of crazy code. If we write analyzer to show this code, it would be OK. But even if you generate an appropriate exception with explanation, it will be very helpful as well.

[sdanyliv]

Modified the exception message as follows. To avoid introducing specialized logic during predicate generation, the message is kept general:

LinqToDB.LinqToDBException : The LINQ expression 'g' could not be converted to SQL.
Additional details: 'Cannot use the collection from a GroupJoin as an expression. This typically occurs when attempting a LEFT JOIN and choosing the wrong property for comparison.'

[MaceWindu]

/azp run test-all

[azure-pipelines[bot]]

Azure Pipelines successfully started running 1 pipeline(s).

[MaceWindu]

/azp run test-all

[azure-pipelines[bot]]

Azure Pipelines successfully started running 1 pipeline(s).

[MaceWindu]

/azp run test-all

[azure-pipelines[bot]]

Azure Pipelines successfully started running 1 pipeline(s).