CODEWITHSUNDEEP
c#linqexcept
JP Hellemons
JP Hellemons

Reputation: 6057

Exclude collection for removal, Linq

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();
}

Upvotes: 0

Views: 365

Answers (1)

Clint
Clint

Reputation: 6220

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();

Upvotes: 1

Related Questions