LINQ Left Outer Join with conditions

Question

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.

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)

Answer 1

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 :)