Click here to Skip to main content
15,867,308 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 vb.net.

SQL
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
SQL
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)
                             }).ToList


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
Posted

Hi, try this,

SQL
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= f.id == null ? "Null" : f.id};



Thanks in advance
 
Share this answer
 
Comments
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:
SQL
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)
                             }).ToList()


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