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

问题描述

使用Linq和EF

一个User可以有多个RoleUserLinks.

如果User的RoleUserLink表包含2个记录-一个记录的LinkStatusID Deleted和一个记录的LinkStatusID Added，则下面的查询返回User .我不要这个.

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.

如果存在与Added相关的LinkStatusID的任何关联，如何不返回user，请参见下面的情况3

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();

情况1)用户没有关联的RoleUserLink记录.用户已按预期返回

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

情况2)用户具有1个关联的RoleUserLink记录，其中LinkStatusID为已删除.用户已按预期返回

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

情况3)用户具有2个关联的RoleUserLink记录. 1的LinkStatusID为已删除.用户不应该被退回

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();

我添加了此子查询:

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

对于在RoleUserLinks中具有LinkStatusId Added记录的用户，它将从结果中删除不需要的行.

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

我也将此roleUserLinks.LinkStatus == null更改为roleUserLinks == null以避免NullReferenceException，以防RoleUserLink找不到匹配的RoleUserLink

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

测试代码的示例代码

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的链接.

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.

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