LINQ Join to find items NOT IN a list

Question

How do I use LINQ across Queryable and non-Queryable data?

With the following code I want to end up with a list of unselected users, i.e. everyone in allUsers that is not in selected.

public IQueryable<CompanyUser> FindAllUsersNotAssignedTothisCompany(int companyID)
{
    var allUsers = Membership.GetAllUsers();
    var selected = db.CompanyUsers.Where(c => c.CompanyID == companyID);
    ...?
}

I effectively want a list of User ID's and Names suitable for use in a DropDownList (ASP.NET MVC)

Answer 1

Assuming that both allUsers and selected are of the same type, You can do this using Except

public IQueryable<CompanyUser> FindAllUsersNotAssignedTothisCompany(int companyID)
{
    var allUsers = Membership.GetAllUsers();
    var selected = db.CompanyUsers.Where(c => c.CompanyID == companyID);
    return allUsers.Except(selected);
}

However if db.CompanyUsers already has all the users and all you need is to retrieve the users that do not have the companyID in question just:

return db.CompanyUsers.Where(c => c.CompanyID != companyID);