CODEWITHSUNDEEP
.netvb.netlinqlinq-to-entities
Phil Murray
Phil Murray

Reputation: 6554

LINQ Left Outer Join with conditions

I have the below tables that are coming from my SQL Database with Entity Framework 5.

What I want to do is select all users where tblUserBusiness.BUID equals a passed in value OR where the Users.IsSysAdmin equals True. If the Users.IsSysAdmin equals True there will be no relating tblUserBusiness records hence the Left Outer Join.

enter image description here

I started with the below LINQ query that filtered correctly but did not allow for the Outer Join

        businessUsers = (From u In db.Users
                            From bu In db.tblUserBusinesses
                            Where bu.BUID.Equals(buID) Or u.IsSysAdmin.Equals(True)
                                Select New Users With {.ID = u.ID,
                                                       .Name = u.Name,
                                                       .UserName = u.UserName}).ToList

Then I moved onto the below query which allows for the Outer Join but I have no idea how to implement the Where bu.BUID.Equals(buID) Or u.IsSysAdmin.Equals(True)

        businessUsers = (From u In db.Users
                            Group Join bu In db.tblUserBusinesses
                                On u Equals bu.User
                                Into userList = Group
                         Select New Users With {.ID = u.ID,
                                                       .Name = u.Name,
                                                       .UserName = u.UserName}).ToList

Basically what I am after is the LINQ equivalent to the below TSQL

SELECT Users.ID, Users.UserName, Users.Name 
    FROM Users LEFT OUTER JOIN tblUserBusiness ON Users.ID = tblUserBusiness.UserID
    WHERE (Users.IsSysAdmin = 1) OR (tblUserBusiness.BUID = 5)

Upvotes: 2

Views: 1710

Answers (3)

Sheikh M. Haris
Sheikh M. Haris

Reputation: 968

+1 for @ajakblackgoat answer. Thanks bro, it helped me! However I am a C# developer so posting a C# version for other developers similar to me, if they land on this page :) 

from u in Users
join bu in tblUserBusiness on u.Id equals bu.UserID into group
from j in group.DefaultIfEmpty()
where (u.IsSysAdmin || (j != null ? j.BUID == buID : false))

tried to post in comments but full code wasn't showing properly

Upvotes: 0

ajakblackgoat
ajakblackgoat

Reputation: 2149

Try this:

Dim buID As Integer = ... ' BUID to get

Dim q = From u In Users
        Group Join bu In tblUserBusiness On u.Id Equals bu.UserID Into Group
        From j In Group.DefaultIfEmpty
        Where u.IsSysAdmin OrElse If(j IsNot Nothing, j.BUID = buID, False)
        Select u

... or...

Dim q = From u In Users
        Group Join bu In tblUserBusiness On u.Id Equals bu.UserID Into Group
        From j In Group.Where(Function(x) x.BUID = buID).DefaultIfEmpty
        Where u.IsSysAdmin OrElse j IsNot Nothing
        Select u

Either one will give you what you need. I think :)

Upvotes: 2

Victor Zakharov
Victor Zakharov

Reputation: 26424

You need to be using DefaultIfEmpty, so it should be 

Group Join bu In db.tblUserBusinesses.DefaultIfEmpty

Here is a reduced test case (requires a brand new Console Application):

Module Module1
  Class Person
    Public Property Id As String
    Public Property FirstName As String
  End Class

  Class Address
    Public Property Id As String
    Public Property StreetName As String
  End Class

  Sub Main()
    Dim personList As New List(Of Person)
    With personList
      .Add(New Person With {.Id = "1", .FirstName = "John"})
      .Add(New Person With {.Id = "2", .FirstName = "Peter"})
      .Add(New Person With {.Id = "3", .FirstName = "Victor"})
    End With

    Dim addressList As New List(Of Address)
    With addressList
      .Add(New Address With {.Id = "1", .StreetName = "Baker Street"})
      .Add(New Address With {.Id = "2", .StreetName = "Broadway"})
      .Add(New Address With {.Id = "4", .StreetName = "Hwy 999"})
    End With

    Dim v = From p In personList
            Group Join a In addressList.DefaultIfEmpty
            On a.Id Equals p.Id Into Group
            Select PersonId = p.Id,
                   PersonName = p.FirstName,
                   StreetName = Group.FirstOrDefault
  End Sub
End Module

While this may not be the ideal design for a Person-to-Address relationship, it proves that Outer Join can work in VB.NET using DefaultIfEmpty and FirstOrDefault (which is optional, helps organize your data in a nice fashion).

Upvotes: 0

Related Questions