C# Left join in LINQ

Question

Not sure how to convert the following sql into a LINQ expression. My db does use referential integrity and table Content is related to table Content_Training in a 1 to many relationship (ex: 1 content can have many content_trainings).

select c.ContentId, c.Name, ct.TrainingTypeId 
from dbo.Content c left join dbo.ContentTraining ct on c.ContentId = ct.ContentId
where c.ExpirationDate is not null
order by ct.TrainingTypeId, c.Name

I have tried this, which seems to work. However, I am not certain about the usage of the "let" keyword.

var data = (from c in context.Contents
let ct = ( from t in context.Content_Training where t.ContentId == c.ContentId
select new { t.TrainingTypeId } ).FirstOrDefault()
where c.ExpirationDate.HasValue
orderby ct.TrainingTypeId, c.Name
select new { c.ContentId, c.Name, ct.TrainingTypeId } ).ToList();

Your title says "left join" but the SQL you show is doing an inner join, so I'm unclear as to which you're trying to accomplish. — Craig W., Apr 09 '14 at 02:54
Craig W: Sorry about that. Yes, I do want a left join. I edited my sql. — Vance Smith, Apr 09 '14 at 03:06
did you just ask the exact same question under another username? http://stackoverflow.com/questions/22951644/c-sharp-left-join-in-a-lambda-expression — Michael Edenfield, Apr 09 '14 at 03:17

score 14 · Accepted Answer · edited May 23 '17 at 10:29

14

For left join, you need to use DefaultIfEmpty()

Your query should be something similar to this:

var query = from c in Content
            join ct in ContentTraining
            on c.ContentId equals ct.ContentId into g
            from ct in g.DefaultIfEmpty()
            where c.ExpirationDate != null
            select new
            {     
                c.ContentId, 
                c.Name, 
                ct.TrainingTypeId 
             }).ToList();

Please refer to Left outer join in linq

and

http://msdn.microsoft.com/en-us/library/bb397895.aspx

edited May 23 '17 at 10:29

Community

1
1

answered Apr 09 '14 at 03:48

SO User

22,482
16
67
111

Thank you Rashmi! Regarding Craig W's response below, does "include" do a left join? – Vance Smith Apr 09 '14 at 05:00
I'm not sure of "Include". Maybe Craig can answer. Did my solution work? I couldn't test it as I don't have VS.Net right now. – SO User Apr 09 '14 at 06:45

score 1 · Answer 2 · answered Apr 09 '14 at 03:01

1

Alternatively, you might consider using lambda instead of expression syntax. I haven't tried this but this should get you what you need (if what you need is a left join).

var foo = context.Contents.Include( "Content_Training" )
    .Where( c => c.ExpirationDate != null )
    .OrderBy( c => c.Content_Training.TrainingTypeId )
    .ThenBy( c => c.Name
    .Select( c => new { c.ContentId, c.Name, c.Content_Training.TrainingTypeId } );

answered Apr 09 '14 at 03:01

Craig W.

17,250
6
47
81

So "include" does a left join? – Vance Smith Apr 09 '14 at 03:07
Also, in this case I am interested in the LINQ version. I am unsure about my use of the "let" keyword and wanted some info and opinions on that. – Vance Smith Apr 09 '14 at 03:09
It's not a left join per se but the results are essentially the same, if there are no Content_Training objects associated with the Content object you will still get the Content object. – Craig W. Apr 09 '14 at 13:53

score 0 · Answer 3 · answered Apr 09 '14 at 02:36

0

in LINQ it would be:

from c in dbo.Content
join ct in dbo.ContentTraining on c.ContentId equals ct.ContentId
where c.ExpirationDate != null
orderby ct.TrainingTypeId, c.Name
select new
{
    ContentID = c.ContentId,
    Name = c.Name,
    TrainingTypeId = ct.TrainingTypeId
}

answered Apr 09 '14 at 02:36

mnsr

12,097
3
51
78

Wouldn't that be an inner join example? – Vance Smith Apr 09 '14 at 02:43
before you edited your question, you had an inner join, hence my answer. – mnsr Apr 09 '14 at 03:40
This is not correct to what the OP is asking. They want a LEFT join in LINQ, not an INNER JOIN. Your statement above is an INNER JOIN. – Morné Dec 09 '21 at 10:58

C# Left join in LINQ

3 Answers3

Linked