How to write left-join with a case in Linq (C# MVC)

Question

How do I write the following query in Linq:

SELECT s.displayname AS Skill, 
       CASE 
         WHEN us.skillid IS NULL THEN '0' 
         ELSE '1' 
       END           AS HasSkill 
FROM   skills s 
       LEFT JOIN userskills us 
              ON s.id = us.skillid

I've been at it for a couple of hours and I genuinely cannot get my head around the structure of these queries. They seem overwhelming and too complicated for what the plain SQL query otherwise seems to be doing for me.

var skills = _context.Skills.ToList();
var userSkills = _context.UserSkills.ToList();

var result = skills.GroupJoin(userSkill, skill => skill.ID, skill => userSkill , (userSkill, skill) => new {
                Key = userSkill,
                Skills = skill
            });

https://stackoverflow.com/questions/936028/linq-case-statement — Robert Harvey, Apr 17 '19 at 16:05
https://stackoverflow.com/questions/3404975/left-outer-join-in-linq — Robert Harvey, Apr 17 '19 at 16:05
https://stackoverflow.com/questions/25254939/how-to-write-linq-left-join-with-multiple-fields-where-one-is-case-insensitive — Vikas Gupta, Apr 17 '19 at 16:06
For left joins you need to use DefaultIfEmpty d `var result = ( from e in entries from r in results.Where(x => x.PropertyId == e.PropertyId) .Where(x => e.ValueAsString.Equals(x.Value, StringComparison.InvariantCultureIgnoreCase)) .DefaultIfEmpty() select new { Result = r, Entry = e } ) .ToList();` — Vikas Gupta, Apr 17 '19 at 16:07
@RobertHarvey I have already seen those questions. Thank you for your assistance. — Beederab, Apr 17 '19 at 16:25
if you add a navigation property, you can use method syntax and use .Include(), which will automatically translate into a left outer join. — DevilSuichiro, Apr 17 '19 at 18:40

D-Shih · Accepted Answer · 2019-04-17T16:15:37.160

2

You can try to use linq with DefaultIfEmpty() make LEFT JOIN

then use select with the ternary operator to make CASE WHEN

from s in skills
join u in userSkills 
on new s.id equals u.skillid 
into temp
from u in temp.DefaultIfEmpty()
select new { 
    Skill=s.displayname,
    HasSkill= us.skillid == NULL ? "0" : "1"
}

edited Apr 17 '19 at 16:15

answered Apr 17 '19 at 16:09

D-Shih

44,943
6
31
51

Is that `DefaultIfEmpty()` what 'turns it into' a LEFT join rather than an inner join? – Beederab Apr 17 '19 at 16:24
Hang on, what's `us` - is that from line 2? That's supposed to be "join us" I'm guessing. – Beederab Apr 17 '19 at 16:27

How to write left-join with a case in Linq (C# MVC)

1 Answers1