Exclude collection for removal, Linq

Question

I am trying to get the except method to work like in this answer: https://stackoverflow.com/a/3944821/169714

1st try:

var housesToRemove = db.Houses.Except(houses).ToList();

2nd try:

var housesToRemove = db.Houses.Where(h => 
     !houses.Any(h2 => h2.Code.Equals(h.Code) && h2.OperatorId == h.OperatorId)).ToList();

My Houses class has override Equals:

public override bool Equals(object obj)
{
    var item = obj as House;

    if (item == null)
        return false;

    return this.Code == item.Code && this.OperatorId == item.OperatorId;
}

public override int GetHashCode()
{
    return this.ID.GetHashCode();
}

both options throw:

System.NotSupportedException: 'Unable to create a constant value of type 'House'. Only primitive types or enumeration types are supported in this context.'

Part of the code where I use it:

using (var db = new HouseContext())
{
    var housesToRemove = db.Houses.Where(h => !houses.Any(h2 => h2.Code.Equals(h.Code) && h2.OperatorId == h.OperatorId)).ToList();

    foreach (var htr in housesToRemove)
    {
        Console.WriteLine(htr); // I have also overwritten ToString() from House
    }
    housesToRemove.ForEach(x => x.IsListed = false);
    db.SaveChanges();
}

score 1 · Accepted Answer · answered Jun 15 '17 at 12:03

The question you've linked is using Linq to Entities and you're using Linq to SQL which means your Except query is being sent off to the database. The database has no conception of what it is you're actually trying to do here.

I would recommend following the answer here: https://stackoverflow.com/a/18944508/639771 for more information on why this is failing, as well as how to work around it.

tl;dr

You're trying to compare in-memory objects to database objects, as the SQL engine doesn't give a damn about your Equals override, all it sees is an in-memory House object and a call to the database that it somehow has to be able to do the comparison via. You could instead lift the IDs of the houses you don't want and do something like:

var ids = houses.Select(h => h.Id).ToArray();
var filtered = db.Houses.Where(h => !ids.Contains(h.Id)).ToList();

Thanks, I thought that Linq (db.Houses) had an internal list of everything in memory. — JP Hellemons, Jun 15 '17 at 12:10
@JPHellemons Nah, it's actually way crazier than that. It's all based on `IQueryable` which takes the LINQ expressions and translates them into SQL statements and then fires that off to the database. It's how you can do a search in a table of millions of rows without having to load all that data first. — Clint, Jun 15 '17 at 13:02

Exclude collection for removal, Linq

1 Answers1