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