Convert SQL query with join to lambda expression

Question

not sure how to convert the following sql into a lambda expression. My database uses referential integrity and table Content related to table Content_Training in a 1 to many relationship (1 content can have many content_trainings)

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

abatishchev · Accepted Answer · 2014-04-09 03:21:44Z

3

Try this query:

var results = (from c in dbcontext.Contents
               join ct in dbcontext.Content_Trainings on c.ContentId equals ct.ContentId into t
               from rt in t.DefaultIfEmpty()
               select new
               {
                   c.ContentId,
                   c.Name,
                   TrainingTypeId = (int?)rt.TrainingTypeId
               }).OrderBy(r => r.TrainingTypeId)
                 .ThenBy(r => r.Name);

edited Apr 9, 2014 at 3:21

abatishchev

101k88 gold badges303 silver badges443 bronze badges

answered Apr 9, 2014 at 3:11

Raja Nadar

9,5042 gold badges36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

abatishchev Over a year ago

I'd use query syntax constantly, without lambdas, i.e. orderby x,y. It'll be more readable.

BeYourOwnGod Over a year ago

Thanks Abatishchev. In the past I have used the "let" keyword instead of the "into t ... t.DefaultIfEmpty()" that you used. What is the difference?

Collectives™ on Stack Overflow

Convert SQL query with join to lambda expression

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related