Click here to Skip to main content
15,792,382 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Guys,

I am basically trying to write the following SQL query in Linq to Entities using

select * from foo f
left outer join bar b
on b.Id = f.Id and b.Pid = 10 and b.Sid = 20

My linq query is as follows
Dim query = (from f in foo
            group join b in bar on b.Id equals f.Id into g = Group
            from fb in g.where(function(x) x.Pid = 10 and x.Sid = 20).DefaultIfEmpty
            select new with {.Name = f.Name,
                             .Id = (if fb isNot nothing, fb.Id, Nothing)

But the issue is, the variable Id in the select clause always returns nothing. Not able to figure out why, but my only guess is that if one value is null then it nulls everything in the column. When the SQL is executed on the database it produces the following output

Name -- Id
One -- 20
Two -- 20
Three -- Null
Four -- 20

Hope you can help in this regard, many thanks

Hi, try this,

from f in foo
    join b in bar on f equals b.Id into bs
    from b in bs.DefaultIfEmpty()
    where b.Pid = 10 and b.Sid = 20
    select new { Name = f.Name, Id= == null ? "Null" :};

Thanks in advance
Share this answer
spankyleo123 19-Jun-15 7:16am    
Hi thanks for your response.I have attempted your suggestion but unfortunately if the where clause is outside the group it does not fetch any rows in this case.
Have a look here: How to: Combine Data with LINQ by Using Joins (Visual Basic)[^]

I'd do that this way:
Dim query = (From f In foo
            Group Join b In bar.Where(Function(x) x.Pid = 10 And x.Sid=20) On b.Id Equals f.Id Into g = Group
            From fb In g.DefaultIfEmpty()
            Select New With {.Name = f.Name,
                             .Id = (if fb isNot nothing, fb.Id, Nothing)

More at MSDN code: LINQ - Sample Queries[^]
Share this answer
Thanks Guys, unfortunately none of the options worked. So had to write a SP and call it in the code, which solved the issue.

Thanks again.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900