Linq query not returning expected results even when using DefaultIfEmpty

Question

I have the following query in one of my Entity Framework Core API controllers:

var plotData = await (from nl in _context.BookList
  join ql in _context.PlotList on nl.PlotId equals ql.PlotId
  join qc in _context.PlotChoices on ql.PlotId equals qc.PlotId
  join nk in _context.BookLinks.DefaultIfEmpty() on qc.ChoiceId equals nk.ChoiceId
  where nl.Id == ID
  select new
  { .. }

I need it to return all rows even if data doesn't exist in the BookLinks table.

However, it's not returning rows if there is no data data in the BookLinks table for that row.

But this SQL query, from which I'm trying to model from, does return data...it returns nulls if there is no data in BookLinks.

select * from BookList bl
left join PlotList pl ON bl.plotId = bl.plotId
left join PlotChoices pc ON pl.plotId = pc.plotId
left join BookLinks bk ON pc.choiceID = bk.choiceID
where nl.caseID = '2abv1'

From what I read online, adding 'DefaultIfEmpty()' to the end of BookLinks should fix that, but it hasn't.

What am I doing wrong?

Thanks!

Is BookLinks the only entity that will not have records or the other entities as well? — Julián, Nov 25 '19 at 21:03
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Nov 25 '19 at 22:04

score 1 · Accepted Answer · answered Nov 26 '19 at 06:00

When using left join , you can try below code sample :

var plotData  = (from nl in _context.BookList
    join ql in _context.PlotList on nl.PlotId equals ql.PlotId
    join qc in _context.PlotChoices on ql.PlotId equals qc.PlotId
    join nk in _context.BookLinks on qc.ChoiceId equals nk.ChoiceId into Details
    from m in Details.DefaultIfEmpty()
    where nl.Id == ID
    select new
    {

    }).ToList();

Linq query not returning expected results even when using DefaultIfEmpty

1 Answers1