Left/outer join with linq on c# with where condition clause

Question

I have 2 tables that I need to join in a query. The first table is the Entries table which contain certain events such as Dance, Speak, Sing, Play, etc.

Id|Name  
   1|Dance  
   2|Sing  
   3|Speak  
   4|Play  
   5| etc.

The other table contains userEntries which indicates each user's score on each of the events

Id| UserId|EntryId|Score

 1|898128 | 1     |200

 2|827329 | 2     |120

 3|898128 | 2     |100

Now I want a linq query to first of all get all the entries and then get the scores for a given user for the entries retunining null for the entry score whete the user has noscore

Example for user 898128, I want to see something like this

Dance:200,Speak:null,Sing:120 from the result

I have tried the following linq query and I get an empty result

var userScores = 
(from e in db.Entries join se in db.UserEntries
on e.Id equals se.EntryId                                           
into ese from se in 
ese.DefaultIfEmpty()
where se.UserId == "898128"
select new 
{
EntryLabel=e.Label,
EntryValue=se.ValueAmount,
}).ToList();

ViewData["userScores "] = userScores;

I am running on ASP.NET core 2.0, entity framework core on a Windows 10 machine with Visual Studio 2017 15.6.3

I will appreciate any guide to getting the query right to give me an outer join so I can get all the entries for each user even where the user does not have any score.

Please note that this is different from this question errorneously marked by @Mahmoud as its duplicate. The difference lies in the presence of the WHERE condition clause.

Thank you

Answer 1

Try this query. it should fix your issue.

 var userScores =(from e in db.Entries
                 join se in db.UserEntries on e.Id equals se.EntryId into ese
                 from nullse in ese.DefaultIfEmpty()
                 where (nullse==nulll ||(nullse!=null && nullse.UserId == "898128"))
                 select new
                 {
                    EntryLabel = e.Name,
                    EntryValue = nullse != null ? nullse.ValueAmount:"null"
                  }).ToList();