Converting a SQL join into LINQ with EF with filtering

Question

I'm learning Entity Framework and attempting to convert an existing SQL query to a LINQ query, but struggling to convert it.

SELECT taskItems.Description,taskItemResponses.IsCompleted,TaskItemResponses.userId,TaskItemResponses.Notes 
FROM TaskLists
LEFT JOIN TaskItems ON TaskLists.TaskListId = TaskItems.TaskListId
LEFT JOIN TaskItemResponses ON TaskItemResponses.TaskItemId = TaskItems.TaskItemId 
AND TaskItemResponses.UserId = '1'

This works fine for me, it brings back the following data, always showing the list of Tasks, and if a user has responded to any of them, if they've completed it and what notes they've added.

Description  IsCompleted   userId  Notes
Task A       NULL          NULL    NULL
Task B       NULL          NULL    NULL
Task C       NULL          NULL    NULL
Task D       1             1       I've done this now.
Task E       NULL          NULL    NULL

But when I'm trying to convert this to a LINQ query within C# I can't figure out the syntax, so far i've got

var query = from t in DbContext.TaskList
join ti in DbContext.TaskItem on t.TaskListId equals ti.TaskListId
join tr in DbContext.TaskItemResponse on ti.TaskItemId equals tr.TaskItemId into trj
from x in trj.DefaultIfEmpty()
where x.UserId == userId
select t;

But this isn't filtering for a particular UserId, and instead returns

Description  IsCompleted   userId  Notes
Task A       0             2       Great
Task B       1             2       Okay
Task C       1             3       Nope
Task D       1             1       I've done this now.
Task E       0             5       Ok.

Using INTO https://msdn.microsoft.com/en-us/library/bb311045.aspx — norbert, Jun 29 '16 at 09:51
Sorry, I didnt see the `into` in your code. My Bad. Another point, your original sql had 2 left joins, your EF has an inner join and a left join - could this be the issue? — Jamiec, Jun 29 '16 at 09:53

score 2 · Answer 1 · answered Jun 29 '16 at 11:17

See that. May be you need ome minor changes.

var query = from t in DbContext.TaskList
    join ti in DbContext.TaskItem on t.TaskListId equals ti.TaskListId
    join tr in DbContext.TaskItemResponse on ti.TaskItemId equals tr.TaskItemId
    where(tr.UserId == '1')
    select new tempObjList
    {
        Description = taskItems.Description,
        IsCompleted = taskItemResponses.IsCompleted,
        userId = TaskItemResponses.userId,
        Notes = TaskItemResponses.Notes
    };

make "tempObjList" model class.

score 1 · Accepted Answer · answered Jun 29 '16 at 10:14

The correct way to convert SQL LEFT JOIN with right side filter to LINQ is to apply the right side filter before the join operator.

Here is the LINQ equivalent of your SQL query (of course you can correct the field names/types if needed):

var query = 
    from t in DbContext.TaskList
    join ti in DbContext.TaskItem on t.TaskListId equals ti.TaskListId
    into tij from ti in tij.DefaultIfEmpty() // left join
    join tr in DbContext.TaskItemResponse.Where(x => x.UserId == userId) // filter
        on ti.TaskItemId equals tr.TaskItemId
    into trj from tr in trj.DefaultIfEmpty() // left join
    select new
    {
        ti.Description,
        IsCompleted = (bool?)tr.IsCompleted,
        userId = (int?)tr.userId,
        tr.Notes 
    };

score 0 · Answer 3 · answered Jun 29 '16 at 09:57

0

In your EF code, the first join is an INNER join and your second one is a LEFT OUTER join

answered Jun 29 '16 at 09:57

Alrighty then

127
1
11

score 0 · Answer 4 · edited May 23 '17 at 12:00

0

Have a look at these:

Hope these could save you some time.

edited May 23 '17 at 12:00

Community

1
1

answered Jun 29 '16 at 11:18

etrupja

2,710
6
22
37

Converting a SQL join into LINQ with EF with filtering

4 Answers4