Solved

Linq Left Join query

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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