Left join condition on right

Question

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

Not clearly. Can you provide some test data and result what you want to achieve? — Hamlet Hakobyan, Jan 08 '13 at 12:53
If this: `// has never been added to a role ie record isn't there roleUserLinks.LinkStatus == null` then why `LEFT JOIN`? — Hamlet Hakobyan, Jan 08 '13 at 13:03

mipe34 · Accepted Answer · 2013-01-08T18:15:10.167

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.

Thanks mipe34. This feels right.. not working yet.. need to step away for an hour to clear head! — Dave Mateer, Jan 08 '13 at 13:20
Ok, let me know on progress. I'm not sure I have understood your bussines needs well so maybe the answer need some correction. — mipe34, Jan 08 '13 at 13:29
Thanks mipe34 - you pointed me down a good path, which am still unsure as to why didn't work. I solved using my answer below. — Dave Mateer, Jan 08 '13 at 17:22
I have read your solution but still do not get it why my solution does not work for you. I have attached code sample on which I tested my solution. From my point of view it works as expected (if I did not misunderstood your business needs - but as I see your code, it should do the same in the end). — mipe34, Jan 08 '13 at 18:17
Thanks mipe34. You've got the business needs fine. My query is part of a chain with some more complexity (I don't see how it could affect this query though). So possibly that is affecting the output. Also possible types (am using longs instead of ints). Also possible EF strangeness and where I put the case1,2,3 conditions. Have found a strategy that is good enough currently, so that is great. Cheers! — Dave Mateer, Jan 09 '13 at 09:48

score 0 · Answer 2 · answered Jan 08 '13 at 17:21

I solved this problem by taking the original query:

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().Include(b => b.RoleUserLinks).ToList();

and added the Include and ToList at the end.

Then:

var list = new List<User>();

    foreach (var user in z)
    {
        bool shouldReturnUser = true;
        //get roleLinks
        foreach (var rul in user.RoleUserLinks)
        {
            if (rul.LinkStatusID == (byte) Enums.LinkStatus.Added)
                shouldReturnUser = false;
        }
        if (shouldReturnUser)
            list.Add(user);
    }

then returning this list.

I understand there is a perf hit by doing this, and processing on the webserver, but IMHO this is very easy to understand, and it works :-)

Left join condition on right

2 Answers2

Linked