.net linq to entities "not in" single query

Question

Hello All,

Iam using linq to entities and I want to get all Users that haven't signed up for the class. This is what I did.

var classUsers = from cu in myEntities.ClassUsers
                         where cu.Class.ClassId == classId
                         select new
                         {
                             FirstName = cu.UserInfo.FirstName,
                             Id = cu.UserInfo.Id,
                             LastName = cu.UserInfo.LastName,
                             Select = new Boolean()
                         };

        var allUsers = from u in myEntities.UserInfo
                       select new
                       {
                           FirstName = u.FirstName,
                           Id = u.Id,
                           LastName = u.LastName,
                           Select = new Boolean()
                       };

        var availableUsers = allUsers.Except(classUsers).OrderBy(a=>a.FirstName);

Is there a way I can merge the first two queries into one?

Answer 1

I didn't test it but this could be a lightweight solution.

myEntities.UserInfo.Except(myEntities.ClassUsers.Where(cu=> cu.Class.ClassID = classID)
.Select(cu=>cu.UserInfo)).Select(u=>Id = u.Id, FirstName = u.FirstName, LastName =   
                                 u.LastName, Select = new Boolean())