Convert SQL to Linq, with left join

Question

I need to convert this SQL to a Linq Query, but as always I can't figure it out :(

guess I need to look a Little deeper into Linq.

select butik.preferences.pref,isnull(butik.userpreferences.selected,0) as selected 
from butik.preferences
left join butik.userpreferences 
     on butik.preferences.id = butik.userpreferences.pref AND 
     butik.userpreferences.userid = 2

thanks Christian

possible duplicate of [LINQ to SQL - Left Outer Join with multiple join conditions](http://stackoverflow.com/questions/1122942/linq-to-sql-left-outer-join-with-multiple-join-conditions) — Yuck, Dec 21 '12 at 00:05
possible duplicate of http://stackoverflow.com/questions/9171063/convert-sql-to-linq-left-join-with-null — Yuck, Dec 21 '12 at 00:05
There is a MSDN page [101 LINQ Samples](http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b) which can help you out to get better experience with LINQ by checking different examples. — Tom, Dec 21 '12 at 00:13

score 1 · Accepted Answer · answered Dec 21 '12 at 07:02

1

There is no direct translation of a left join in LINQ. But there are plenty ways to archive what you want :)

from item in butik.preferences
select new
{
    Pref = butik.preferences.pref,
    IsSelected = 
        butik.userprefences.Any(up => up.pref == item.id && up.userid == 2)
        ? butik.userpreferences.First(up => up.pref == item.id && up.userid == 2).selected
        : false;
}

answered Dec 21 '12 at 07:02

Jan P.

3,261
19
26

actually my code was out of context so I'm getting an nullexception – Christian M Dec 21 '12 at 21:41

score 0 · Answer 2 · answered Dec 21 '12 at 10:27

Please try with below code snippet.

var Query = (from c in butik.preferences
     join v in butik.userpreferences.Where(v => v.userid == 2).DefaultIfEmpty() on c.id equals v.pref into JoinUserPref
     from v in JoinUserPref.DefaultIfEmpty()
     select new
     {
         pref = c.pref,
         selected = (v.selected == null ? 0 : v.selected)
     });

If above code snippet is not worked then please try with below LINQPad.

http://www.linqpad.net/

Convert SQL to Linq, with left join

2 Answers2