LINQ Join to find items NOT Listed

Question

How to use LINQ for Queryable and non-Queryable data?

In the following code, I want to end the list of unselected users, that is, all in allUsers that are not selected.

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

I really want a list of user IDs and names suitable for use in DropDownList (ASP.NET MVC)

Answer 1

Assuming that both allUsersand selectedhave the same type, you can do so 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 to do is get users who do not have the corresponding company ID:

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

Answer 2

:

db.CompanyUsers.Where(c => !allUsers.Any(d=> d.CompanyID == a.companyID))).ToList();