Load collection except another collection using linq

Question

I have this search method:

public List<Employeees> AutoSuggestEmployeee(string keyword,
    long employeeeTypeId, int count)
{
    return context.Employeees.Where(
        x => x.EmployeeeName.Contains(keyword)
        && x.EmployeeeTypeId == employeeeTypeId)
    .Take(count).ToList();
}

I have another collection of Employeees, say "BadEmployeees", what I want is using the same previous method to return all Employeees except "BadEmployeees".

I tried to write it like this:

return context.Employeees.Where(
        x => x.EmployeeeName.Contains(keyword)
        && x.EmployeeeTypeId == employeeeTypeId)
    .Except(BadEmployeees).Take(count).ToList();

But it is giving an exception that Except can just work with data types such as Int, Guid,...

score 7 · Answer 1 · answered Aug 17 '11 at 11:18

7

The Except method does a comparison, so it has to know how to compare the objects. For simple types there are standard comparisons, but for complex types you need to supply an equality comparer that compares the relevant data in the object.

Example:

class EmployeeComparer : IEqualityComparer<Employeees> {

  public bool Equals(Employeees x, Employeees y) {
    return x.Id == y.Id;
  }

  public int GetHashCode(Employeees employee) {
    return employee.Id.GetHashCode();
  }

}

Usage:

return
  context.Employeees
  .Where(x => x.EmployeeeName.Contains(keyword) && x.EmployeeeTypeId == employeeeTypeId)
  .Except(BadEmployeees, new EmployeeComparer())
  .Take(count)
  .ToList();

answered Aug 17 '11 at 11:18

Guffa

687,336
108
737
1,005

i used your class and used it like this: context.Employees.Where(x => x.EmployeeName.Contains(keyword) && x.EmployeeTypeId == EmployeeTypeId).Except(Employees,new EmployeeComparer()).Take(count).ToList(); but I got this error: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[ManUp.Data.Employee] Except[Employee](System.Linq.IQueryable`1[ManUp.Data.Employee], System.Collections.Generic.IEnumerable`1[Data.Employee], System.Collections.Generic.IEqualityComparer`1[Data.Employee])' method, and this method cannot be translated into a store expression. – Amr Elgarhy Aug 17 '11 at 22:35
Amr ElGarhy: I see. You would need a `.AsEnumerable()` before that to pull the result from the database. If you want to do the work in the database, you should do it with a join instead. – Guffa Aug 17 '11 at 23:30
yes, at first I wanted to do with join but I am not an expert in linq so couldn't write it, can you give an example for this join in my case? – Amr Elgarhy Aug 18 '11 at 01:40

score 6 · Accepted Answer · answered Aug 17 '11 at 11:12

If you're happy to retrieve all the data and then perform the "except", that's relatively easy:

return context.Employees
              .Where(x => x.EmployeeName.Contains(keyword)
                          && x.EmployeeTypeId == employeeeTypeId)
              // Limit the data *somewhat*
              .Take(count + BadEmployees.Count)
              // Do the rest of the query in-process
              .AsEnumerable()
              .Except(BadEmployees)
              .Take(count)
              .ToList();

Alternatively:

// I'm making some assumptions about property names here...
var badEmployeeIds = badEmployees.Select(x => x.EmployeeId)
                                 .ToList();

return context.Employees
              .Where(x => x.EmployeeName.Contains(keyword)
                          && x.EmployeeTypeId == employeeeTypeId)
                          && !badEmployeeIds.Contains(x.EmployeeId))
              .Take(count)
              .ToList();

Load collection except another collection using linq

2 Answers2

Linked