?
Solved

Linq Left Join query

Posted on 2008-10-22
2
Medium Priority
?
3,042 Views
Last Modified: 2013-11-11
Hi,
I am having trouble converting the following simple SQL statement into LINQ code (VB):

select * from tblRole r
left outer join tblUserRoles ur
      on r.role_id = ur.role_id_f
      and ur.user_id_f = 2

The attached code snippet shows my progress so far although I just can't get the user_id_f = 2 part to work - or where it is meant to go? I need to get all results from the tblRole table regardless of whether there is a corresponding tblUserRole value

Many thanks
roles = (From role In db.tblRoles _
                 Group Join detail In db.tblUserRoles On _
                 role.Role_ID Equals detail.Role_ID_f _
                 Into children = Group _
                 From child In children.DefaultIfEmpty _
                 Select New With {role.Role_ID, role.Role_desc, child.Allow})

Open in new window

0
Comment
Question by:km176351
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Accepted Solution

by:
novynov earned 1000 total points
ID: 22782624
I believe you want something like the snippet below.

As you already know left outer joins in Linq can be obtained by using DefaultIfEmpty() on the results of a group join.

The compound "on" can be accomplished by introducing an anonymous type in the join. I think that was the only piece you were missing. So my query isn't much different than yours.

I'm a C# guy...I've done 0 VB.NET in practice...but did my best to translate things into valid VB.NET. I may prove a fool to have tackled this.

Not having your db in front of me, this is untested.  I did successfully try a similar query against my own db.

Let me know if this helps or causes grief. Hopefully, it's the former.


var roles = (from role in db.tblRoles _
    group join detail in db.tblUserRoles on new with {key .f1 = role.role_id, .f2 = 2} equals _ 
    new with {key .f1 = detail.Role_ID_F, .f2 = detail.user_id_f } _
    into children = group _
    from child in children.DefaultIfEmpty
    select new with {role.Role_ID, role.Role_desc, child.Allow})

Open in new window

0
 
LVL 1

Author Closing Comment

by:km176351
ID: 31509007
Thankyou very much, your solution worked perfectly!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question