The number of differences with linq for entities and custom IEqualityComparer

Question

My custom mapper doesn't seem to work. I want to count various objects, but each time I get a count of 1. Despite the fact that looking at the database, it is clear that there is more than 1 instance of the request with different values ​​of "TimeOfAction".

class TimeComparer : IEqualityComparer<Action> { public bool Equals(Action a, Action b) { if (a.TimeOfAction == b.TimeOfAction) return true; else return false; } public int GetHashCode(Action obj) { return obj.ToString().ToLower().GetHashCode(); } } 

Think it might be the GetHashCode method, since I am not too familiar with how it should work. Here is the linq query. I converted to AsEnumerable since Linq to Entities does not support a separate method.

 DBEntities db = new DBEntities(); IEnumerable<Action> query = from action in db.Action.AsEnumerable() where action.TimeOfAction > new DateTime(2010, 11, 1, 0, 0, 0) where action.TimeOfAction < new DateTime(2011, 2, 7, 0, 0, 0) where action.EntityName == "seant" select action; var count = query. Distinct(new TimeComparer()).Count(); 

Answer 1

Equals and GetHashCode methods use completely different approaches. In particular, equal objects may have different hash codes, assuming that Action.ToString uses fields other than TimeOfAction. They must be aligned, or you will have absolutely no chance of getting reasonable results. It is normal for unequal objects to have the same hash code (although this will hinder performance), but equal objects must indicate the same hash code.

Please note that using a custom comparator will result in the distinguishing part being executed in the process, and not in the database. This may not be a problem, you just need to understand it. EDIT: I have not noticed that there is an overload of Queryable.Distinct that accepts IEqualityComparer<T> . I assume that you can provide custom string mappings and several other well-known comparators ... and not just arbitrary code. If this works, it will be done anyway. I would not be surprised if it just exploded, though.

EDIT: As Mark says, you can use Select(x => x.TimeOfAction).Distinct().Count() for this in the database. You also need to remove the AsEnumerable call. I guess this is because something else is not working. You can try the following:

 DBEntities db = new DBEntities(); IQueryable<DateTime> query = from action in db.Action where action.TimeOfAction > new DateTime(2010, 11, 1, 0, 0, 0) where action.TimeOfAction < new DateTime(2011, 2, 7, 0, 0, 0) where action.EntityName == "seant" select action.TimeOfAction; var count = query.Distinct().Count(); 

Of course, if you needed a query for something else, you would also need to keep the original version:

 DBEntities db = new DBEntities(); IQueryable<Action> query = from action in db.Action where action.TimeOfAction > new DateTime(2010, 11, 1, 0, 0, 0) where action.TimeOfAction < new DateTime(2011, 2, 7, 0, 0, 0) where action.EntityName == "seant" select action; var count = query.Select(x => x.TimeOfAction).Distinct().Count(); // Use query here as well to get at full action details 

Note that when you re-query the query, the second database query will be executed. You will need to look at what is happening in terms of transactions, if you need the account to be consistent with what the second request is doing ... or pull out all the details from the database (using the ToList call), and then do the Separate part of the process.

---

Back to custom comparison mappers ...

Assuming TimeOfAction is a DateTime or some other type that has a reasonable hash code, you can change your class to:

 class TimeComparer : IEqualityComparer<Action> { public bool Equals(Action a, Action b) { return a.TimeOfAction == b.TimeOfAction; } public int GetHashCode(Action obj) { return obj.TimeOfAction.GetHashCode(); } } 

Note that I also simplified your Equals method - anytime you find yourself:

 if (condition) { return true; } else { return false; } 

you can simplify it:

 return condition; 

Answer 2

First, note that this will not run on the db server, therefore it is not related to EF.

I suspect this is partially because your GetHashCode does not agree with Equals ; you should ideally have something like:

 public int GetHashCode(Action obj) { return obj.TimeOfAction.GetHashCode(); } 

since this is your Equals .

Also, note that the entire query can be rewritten (and will probably work on the db server if you use the IQueryable<Action> query (and remove AsEnumerable() ) rather than the IEnumerable<Action> query ) with:

 var count = query.Select(x => x.TimeOfAction).Distinct().Count();