5

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

Josh
  • 1,660
  • 5
  • 33
  • 55
  • Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) – Rickless Mar 24 '18 at 13:50

2 Answers2

4

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();
user9405863
  • 1,506
  • 1
  • 11
  • 16
  • Thank you @Hazarath for your efforts but I'm still getting empty result when I add this line where (nullse!=null && nullse.UserId == "898128") to it. Could this line be causing the system to run an inner join? – Josh Mar 24 '18 at 15:25
  • @Josh : updated answer ..try that and let me know..as we are doing outer join. checking null value for userId comparison. – user9405863 Mar 24 '18 at 15:40
  • it doesn't generate "join with where condition clause" – Mohammad Nikravan Dec 15 '21 at 02:48
4

I have found the answer from this SO question. From there, I realized that the position of the where clause is the problem. See the working code revision below

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

ViewData["userScores "] = userScores;

Thank you @Hazarath for your guide

Josh
  • 1,660
  • 5
  • 33
  • 55