左边连接条件在右边[英] Left join condition on right

本文是小编为大家收集整理的关于左边连接条件在右边的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

使用 Linq 和 EF

一个User可以有多个RoleUserLinks.

如果 User 的 RoleUserLink 表包含 2 条记录 - 一条的 LinkStatusID 为 Deleted,另一条的 LinkStatusID 为 Added,则以下查询返回 User.我不想要这个.

如果有任何关联的 Added 的 LinkStatusID,如何不返回 user,请参阅下面的案例 3

IEnumerable<User> z =
    (from users in listOfUsersForReviewer
     join roleUserLinks in context.RoleUserLinks
        on users.UserID equals roleUserLinks.UserID into roleUserLinksJoin
     // left join
     from roleUserLinks in roleUserLinksJoin.DefaultIfEmpty()
     where
         // case 1 - has never been added to a role ie record isn't there
         roleUserLinks.LinkStatus == null
         // case 2 - has been soft deleted from a role so we want this record
         || roleUserLinks.LinkStatusID == (byte)Enums.LinkStatus.Deleted
     select users).Distinct();

案例 1) 用户没有关联的 RoleUserLink 记录.用户按预期返回

案例 2) 用户有 1 个关联的 RoleUserLink 记录,其中 LinkStatusID 已删除.用户按预期返回

案例 3) 用户有 2 条关联的 RoleUserLink 记录.1 的 LinkStatusID 为已删除.用户不应该被退回

推荐答案

如果我理解的很好应该是:

IEnumerable<User> z =
    (from users in listOfUsersForReviewer
     join roleUserLinks in context.RoleUserLinks
        on users.UserID equals roleUserLinks.UserID into roleUserLinksJoin
     // left join
     from roleUserLinks in roleUserLinksJoin.DefaultIfEmpty()
     where
         (roleUserLinks == null
         || roleUserLinks.LinkStatusID == (byte)Enums.LinkStatus.Deleted)
         && !roleUserLinksJoin.Where(x=> roleUserLinks.LinkStatusID == (byte)Enums.LinkStatus.Added && x.UserID == roleUserLinks.UserID).Any()
     select users).Distinct();

我已经添加了这个子查询:

&& !roleUserLinksJoin.Where(x=> roleUserLinks.LinkStatusID == (byte)Enums.LinkStatus.Added && x.UserID == roleUserLinks.UserID).Any()

它将从结果中删除不需要的行,这些用户在 RoleUserLinks 中使用 LinkStatusId Added 有记录.

我还将这个 roleUserLinks.LinkStatus == null 更改为 roleUserLinks == null 以避免 NullReferenceException 以防 User

没有匹配的 RoleUserLink

测试代码的示例代码

static void Main(string[] args)
{
    var usersList = new List<User>()
                    {
                        new User() {UserID = 1},
                        new User() {UserID = 2},
                        new User() {UserID = 3}
                    };

    var userLinksList = new List<RoleUserLink>()
                        {
                            new RoleUserLink() {UserID = 1, State = "del"},
                            new RoleUserLink() {UserID = 2, State = "add"},
                            new RoleUserLink() {UserID = 2, State = "del"}
                        };

    IEnumerable<User> z = (from users in usersList 
                           join roleUserLinks in userLinksList
                           on users.UserID equals roleUserLinks.UserID into roleUserLinksJoin
                           // left join
                           from roleUserLinks in roleUserLinksJoin.DefaultIfEmpty()
                           where
                            // has never been added to a role ie record isn't there
                                roleUserLinks == null
                            // has been soft deleted from a role so we want this record
                                || roleUserLinks.State == "del"
                            // has been added to role so we don't want this record
                                && !roleUserLinksJoin.Where(x=> x.State == "add" && x.UserID == roleUserLinks.UserID).Any()
                            select users).Distinct();
    var res = z.ToList();

}

public class User
{
    public int UserID { get; set; }
}

public class RoleUserLink
{
    public int UserID { get; set; }
    public string State { get; set; }
} 

它返回 id 为 1 和 3 的用户.正如我所料.UserId:1 仅与状态 delete 链接.UserId:3 没有任何链接.并且 UserId:2 没有返回,因为它还与状态 add 链接.

本文地址:https://www.itbaoku.cn/post/1556913.html

问题描述

Using Linq and EF

One User can have many RoleUserLinks.

If a User's RoleUserLink table contains 2 records - one which has a LinkStatusID of Deleted and one which has a LinkStatusID of Added, the query below returns the User. I don't want this.

How to not return the user if there are any associated LinkStatusID's of Added see case 3 below

IEnumerable<User> z =
    (from users in listOfUsersForReviewer
     join roleUserLinks in context.RoleUserLinks
        on users.UserID equals roleUserLinks.UserID into roleUserLinksJoin
     // left join
     from roleUserLinks in roleUserLinksJoin.DefaultIfEmpty()
     where
         // case 1 - has never been added to a role ie record isn't there
         roleUserLinks.LinkStatus == null
         // case 2 - has been soft deleted from a role so we want this record
         || roleUserLinks.LinkStatusID == (byte)Enums.LinkStatus.Deleted
     select users).Distinct();

case 1) User has no associated RoleUserLink records. user is returned as expected

case 2) User has 1 associated RoleUserLink record with LinkStatusID of Deleted. user is returned as expected

case 3) User has 2 associated RoleUserLink records. 1 has a LinkStatusID of Deleted. user should not be returned

推荐答案

If I understand it well it should be:

IEnumerable<User> z =
    (from users in listOfUsersForReviewer
     join roleUserLinks in context.RoleUserLinks
        on users.UserID equals roleUserLinks.UserID into roleUserLinksJoin
     // left join
     from roleUserLinks in roleUserLinksJoin.DefaultIfEmpty()
     where
         (roleUserLinks == null
         || roleUserLinks.LinkStatusID == (byte)Enums.LinkStatus.Deleted)
         && !roleUserLinksJoin.Where(x=> roleUserLinks.LinkStatusID == (byte)Enums.LinkStatus.Added && x.UserID == roleUserLinks.UserID).Any()
     select users).Distinct();

I have added this subquery:

&& !roleUserLinksJoin.Where(x=> roleUserLinks.LinkStatusID == (byte)Enums.LinkStatus.Added && x.UserID == roleUserLinks.UserID).Any()

It will remove unwanted rows from result with users which has record in RoleUserLinks with LinkStatusId Added.

I have also changed this roleUserLinks.LinkStatus == null to roleUserLinks == null to avoid NullReferenceException in case there will be no matching RoleUserLink for User

Sample code on which I tested my code

static void Main(string[] args)
{
    var usersList = new List<User>()
                    {
                        new User() {UserID = 1},
                        new User() {UserID = 2},
                        new User() {UserID = 3}
                    };

    var userLinksList = new List<RoleUserLink>()
                        {
                            new RoleUserLink() {UserID = 1, State = "del"},
                            new RoleUserLink() {UserID = 2, State = "add"},
                            new RoleUserLink() {UserID = 2, State = "del"}
                        };

    IEnumerable<User> z = (from users in usersList 
                           join roleUserLinks in userLinksList
                           on users.UserID equals roleUserLinks.UserID into roleUserLinksJoin
                           // left join
                           from roleUserLinks in roleUserLinksJoin.DefaultIfEmpty()
                           where
                            // has never been added to a role ie record isn't there
                                roleUserLinks == null
                            // has been soft deleted from a role so we want this record
                                || roleUserLinks.State == "del"
                            // has been added to role so we don't want this record
                                && !roleUserLinksJoin.Where(x=> x.State == "add" && x.UserID == roleUserLinks.UserID).Any()
                            select users).Distinct();
    var res = z.ToList();

}

public class User
{
    public int UserID { get; set; }
}

public class RoleUserLink
{
    public int UserID { get; set; }
    public string State { get; set; }
} 

It returns user with id 1 and 3. As I expected. UserId:1 has only link with status delete. UserId:3 does not have any link. And UserId:2 is not returned because it has also link with status add.