Solved

Linq Left Join query

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 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