0

I created a Lambda LEFT join in my .net core 3 (preview 5) razor page application but for some reason I can't get it to work properly.

I have the following issues:

  • Results from T-SQL and the Lambda are different
  • My Where part in Lambda generates an error

I have the following query which I want to recreate in my application using lambda:

SELECT 
   ai.crArtNrLeverancier,
   pli.crartnr
FROM [tblArtikelImport] ai
LEFT JOIN tblPLInkoop pli on pli.crArtNr = ai.crArtNrLeverancier
WHERE pli.crartnr is not null

In T-SQL this results in 22241 records.

My Lambda query to get the same result set:

var data = await _context.TblArtikelImport.GroupJoin(_context.TblPlinkoop,
    ai => ai.CrArtNrLeverancier,
    pli => pli.CrArtNr,
    (ai, pli) => new { ArtikelImport = ai, PLInkoop = pli })
    .Where(c => c.PLInkoop.Any(cc => cc.CrArtNr != null)).ToListAsync();

With the pli anonymous type I cant select a field directly, that's why I tried to use PLInkoop.Any

The WHERE part returns the following error:

InvalidOperationException: Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where ([cc].CrArtNr != null)' could not be translated and will be evaluated locally.'.

Next I tried to remove the Where part entirely in my Lambda statement which results in the following:

var data2 = await _context.TblArtikelImport.GroupJoin(_context.TblPlinkoop,
    ai => ai.CrArtNrLeverancier,
    pli => pli.CrArtNr,
    (ai, pli) => new { ArtikelImport = ai, PLInkoop = pli })
    .ToListAsync();

The above Lambda statement returns 32962 records while the following T-SQL statement (which should be the same), returns 51809 records:

SELECT 
       ai.crArtNrLeverancier,
       pli.crartnr
FROM [tblArtikelImport] ai
LEFT JOIN tblPLInkoop pli on pli.crArtNr = ai.crArtNrLeverancier

I am pretty new to Lambda in general so perhaps it is something obvious. I am using EF Core and generated the EF Core models using EF Core power tools (to reverse engineer the tables).

Nicolas
  • 2,277
  • 5
  • 36
  • 82
  • 1
    Why are you left-joining and then adding validation to not null. Then you can simply use inner join, or am I missing something? – Tomas Chabada Jun 20 '19 at 10:41
  • Good point. The original query was different where I needed to do a LEFT JOIN, but that is no longer the case. – Nicolas Jun 20 '19 at 11:12
  • @TomasChabada with a regular (INNER) JOIN it works.. The expected result is correct and the `WHERE` clause works. Still wondering why it doesn't work with the `GroupJoin` though.. – Nicolas Jun 20 '19 at 11:25
  • Not sure, but when I want to implement `left join` in code, I use `DefaultIfEmpty` method and that is translated to regular left join – Tomas Chabada Jun 20 '19 at 11:38

1 Answers1

2

Gert Arnold has written an excellent answer to a similar problem explaining how the Linq joins work: Linq to Entities join vs groupjoin

It seems like you are missing the flattening part by using .SelectMany() on the Linq query. This is described in the linked answer just above part 2.

FastJack
  • 866
  • 1
  • 10
  • 11