CODEWITHSUNDEEP
c#.netlinq
CodeFunny
CodeFunny

Reputation: 55

Get List of Records that do not exist in another list - Linq

I want to return a list of users to disable if they do not exist in another list.

Here is what I am currently trying but it is not working:

var disableUserList = dbUserList.Where(ds => dsUserList.Any(db => db.GlobalIdentity != ds.GlobalIdentity)).ToList();

Upvotes: 1

Views: 10297

Answers (5)

Igor
Igor

Reputation: 62213

Replace Any with All.

var disableUserList = dbUserList.Where(ds => dsUserList.All(db => db.GlobalIdentity != ds.GlobalIdentity)).ToList();

Upvotes: 1

neer
neer

Reputation: 4082

If table is one to one relationship then this is better query

var disableUserList = (from ds in dbUserLists
                       join db in dsUserLists on ds.GlobalIdentity  equals db.GlobalIdentity into tmp from db in tmp.DefaultIfEmpty()
                        where
                           db.GlobalIdentity == null
                       select ds).ToList()

If not

var disableUserList = (from ds in dbUserLists                      
                   where
                        !db.dbUserLists.Any(db => db.GlobalIdentity == ds.GlobalIdentity)
                   select ds).ToList()

or

var disableUserList = dbUserList.Where(ds => !dsUserList.Any(db => db.GlobalIdentity == ds.GlobalIdentity)).ToList();

Upvotes: 1

Ryan Intravia
Ryan Intravia

Reputation: 420

As DmitryG pointed out, Except works great for this situation. Here's a quick example where out of users 'A', 'B', 'C', only 'A' is enabled so 'B' and 'C' are written to the console:

public static void Main(string[] args)
    {
        var activeList = new List<string> { "A" };
        var userList = new List<string> {"A", "B", "C"};

        var removalList = userList.Except(activeList);

        foreach (var item in removalList)
        {
            Console.WriteLine(item);
        }

        Console.ReadKey();    
    }

Upvotes: 5

Cam Bruce
Cam Bruce

Reputation: 5689

You can do a left join in Linq with DefaultIfEmpty and filter on non joined entries (null records).

var query = from u in dbUserList
            join g in dsUserList on u.GlobalIdentity equals g.GlobalIdentity
            into joinedUsers
            from ju in joinedUsers.DefaultIfEmpty()
            where ju == null
            select u;

Upvotes: 2

Nate
Nate

Reputation: 660

I think you need to negate your logic. Your current logic will always return true, because the dsUserList most likely has an entry where the GlobalIdentity does not match your dbUserList entry. So, if there are any in the dsUserList where there's a match, exclude them:

var disableUserList = dbUserList.Where(ds => !dsUserList.Any(db => db.GlobalIdentity == ds.GlobalIdentity)).ToList();

Upvotes: 8

Related Questions