Entity Framework left outer join not working

Question

I have an Entity Framework model with the following classes (I have simplified the classes for simpler viewing):

 PuzzleItem
 -PuzzleId (int, primary key)
 -Title

 PuzzleProgress
 -ProgressId (int, primary key)
 -PuzzleId (FK)
 -UserId (FK)

In PuzzleItem I have a number of levels. The PuzzleProgress keeps track of which level the user is at by inserting a record of that level when the user completes a previous level. For a start a new user will have one entry in PuzzleProgress with PuzzleId = 1.

In my code, I am using the following statement to perform a left outer join, so that I will get a list of all puzzles, and indicating to me which puzzle has not been solved. I made reference to this post from StackOverflow.

Here is my code:

var result = from pzs in e.PuzzleItems
                         join prg in e.PuzzleProgresses on pzs equals prg.PuzzleItem
                         into pzs_prg_tbl
                         from pzs_prg in pzs_prg_tbl.DefaultIfEmpty()
                         where pzs_prg.UserId == userId
                         select new SimplePuzzleItem()
                         {
                             PuzzleId = pzs_prg.PuzzleId,
                             PuzzleName = pzs_prg.PuzzleItem.Title,
                             IsUnlocked = (pzs_prg == null?false:true)
                         };

After running the code, only the first level of this new user is returned (while the PuzzleItem table has 3 records).

I've tried playing around with the code but the one I pasted above is the nearest I can get to, can anybody point me in the correct direction? Thanks!

score 4 · Accepted Answer · edited May 23 '17 at 12:26

It's a bit hard to say exactly without seeing more of the code, but where pzs_prg.UserId == userId is probably negating the left outer join.

What I mean is, if you're intending PuzzleItems LEFT JOIN PuzzleProgress then you want all PuzzleItems even when there is no PuzzleProgress. But the where pzs_prg.UserId == userId means PuzzleProgress cannot be null, because it must have a UserId (of value userId). So, you effectively have an inner join.

Personally, I don't like the "correct" way of doing joins (left or inner) in linq, so this is how I would correct the linq statement:

        var result = from pz in db.PuzzleItems
                     from pg in db.PuzzleProgresses
                                  .Where(pg => pg.PuzzleId == pz.PuzzleId)
                                  .Where(pg => pg.UserId == userId)
                                  .DefaultIfEmpty()
                     select new
                                {
                                    PuzzleId = pz.PuzzleId,
                                    PuzzleName = pz.Title,
                                    IsUnlocked = (pg != null)
                                };

This reads a lot more like SQL joins, which I learned a long time ago so it fits my thinking.

If you want to re-factor the join type syntax, look at this 'LINQ Joining in c# with multiple conditions'

Thanks, the other suggestion worked as well, but I'd prefer your approach as it is more readable to me! — VT Chiew, Mar 26 '13 at 07:16

score 1 · Answer 2 · answered Mar 25 '13 at 17:37

I think the where clause is filtering out the records. You need to include the where clause in your left join. Like so:

var result = from pzs in e.PuzzleItems
             join prg in e.PuzzleProgresses on new { pzs.PuzzleId, UserId = userId } equals new { prg.PuzzleId, prg.UserId }
             into pzs_prg_tbl
             from pzs_prg in pzs_prg_tbl.DefaultIfEmpty()
             select new SimplePuzzleItem()
             {
               PuzzleId = pzs_prg.PuzzleId,
               PuzzleName = pzs_prg.PuzzleItem.Title,
               IsUnlocked = (pzs_prg == null?false:true)
             };

Entity Framework left outer join not working

2 Answers2