Solved

Linq Left Join query

Posted on 2008-10-22
2
3,024 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 250 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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