Solved

Linq Left Join query

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

823 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