LINQ的左外连接与条件 [英] LINQ Left Outer Join with conditions

问题描述

我有一个从我的SQL数据库即将与实体框架5。

下面的表

我想要做的是选择在哪里tblUserBusiness.BUID等于传入的值或在Users.IsSysAdmin等于真正的所有用户。如果Users.IsSysAdmin等于真就没有与tblUserBusiness记录，因此左外连接。

我开始用下面的LINQ查询正确的过滤，但没有允许外部联接

  businessUsers =（从u在db.Users
                            从BU在db.tblUserBusinesses
                            凡bu.BUID.Equals（打造专业化）或u.IsSysAdmin.Equals（真）
                                选择新用户{.ID = u.ID，
                                                       。名称= u.Name，
                                                       .UserName = u.UserName}）了ToList
 

然后我移动到下面的查询，它允许外部联接，但我不知道如何实施其中bu.BUID.Equals（打造专业化）或u.IsSysAdmin.Equals（真）

  businessUsers =（从u在db.Users
                            集团加入卜内db.tblUserBusinesses
                                关于U等于bu.User
                                进入用户列表=集团
                         选择新用户{.ID = u.ID，
                                                       。名称= u.Name，
                                                       .UserName = u.UserName}）了ToList
 

基本上我是后是等同于以下TSQL的LINQ

  SELECT Users.ID，Users.UserName，Users.Name
    来自用户的LEFT OUTER JOIN tblUserBusiness ON Users.ID = tblUserBusiness.UserID
    WHERE（Users.IsSysAdmin = 1）或（tblUserBusiness.BUID = 5）
 

解决方案

试试这个：

 昏暗打造专业化为整数= ...'打造专业化来获得

昏暗的Q =从u在​​用户
        集团加入府在tblUserBusiness在u.Id等于bu.UserID进组
        从法官在Group.DefaultIfEmpty
        凡u.IsSysAdmin OrElse运算如果（j状态并没有没有，j.BUID =打造专业化，FALSE）
        选择[u]
 

...或...

 暗淡Q =从u在​​用户
        集团加入府在tblUserBusiness在u.Id等于bu.UserID进组
        从法官在Group.Where（功能（X）x.BUID =打造专业化）.DefaultIfEmpty
        凡u.IsSysAdmin OrElse运算Ĵ状态并没有任何
        选择[u]
 

无论是人会给你所需要的。我想：）

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)

解决方案

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

这篇关于LINQ的左外连接与条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！