14

Good Morning!

Given:

public class FooClass
{
    public void FooMethod()
    {
        using (var myEntity = new MyEntity)
        {
            var result = myEntity.MyDomainEntity.Where(myDomainEntity => myDomainEntity.MySpecialID > default(int)).Distinct(new FooComparer);
        }
    }

}

public class FooComparer : IEqualityComparer<MyEntity.MyDomainEntity>
{
    public bool Equals(MyEntity.MyDomainEntity x, MyEntity.MyDomainEntity y)
    {
        return x.MySpecialID == y.MySpecialID;
    }

    public int GetHashCode(MyEntity.MyDomainEntity obj)
    {
        return obj.MySpecialID.GetHashCode();
    }
}

This will compile, but on runtime I will get an Linq to Entity could not translate Comparer-Exception.
Any suggestions?

1 Answers1

32

If you're providing your own comparisons, you'll need to execute the Distinct call in .NET code. To make sure that happens, use AsEnumerable to turn IQueryable<T> into IEnumerable<T>:

var result = myEntity.MyDomainEntity
        .Where(myDomainEntity => myDomainEntity.MySpecialID > default(int))
        .AsEnumerable()
        .Distinct(new FooComparer());

Of course at that point you'll be pulling more data across from the database. An alternative is to group the data instead:

var result = from entity in myEntity.MyDomainEntity
             where entity.MySpecialID > 0
             group entity by entity.MySpecialID into groups
             select groups.FirstOrDefault();

That will get you the first entity encountered with each ID (assuming my query-fu isn't failing me). That's basically what Distinct does anyway, but it's all at the database.

(Note to future readers: calling First() makes more sense than FirstOrDefault(), but apparently that doesn't work.)

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Is there any chance to do this not in the .NET-Layer? Somehow tell the EF-call to do this in SQL? –  Jun 18 '09 at 06:02
  • See my edit - use grouping and you'll get the desired behaviour. It would be nice to have "DistinctBy" in the framework (and handled by EF etc) but I think the grouped version will do what you want. – Jon Skeet Jun 18 '09 at 06:10
  • Thank you! This looks very plausible to me, as you are doing the group on an IQueryable. I will try this later on! PS.: Yes, you got the Distinct-Condition correct :) –  Jun 18 '09 at 06:14
  • 2
    System.NotSupportedException: The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead. –  Jun 18 '09 at 20:13
  • Rats. Did you try using FirstOrDefault? If that works, I'll fix my answer :) – Jon Skeet Jun 18 '09 at 21:00
  • Now even IQueryable has extension method with comparer, Then why are they providing this method if it does not work ??? – hajirazin Jun 26 '14 at 05:16
  • @hajirazin: Can you give an example? I suspect you're just seeing the overloads inherited from `IEnumerable`. – Jon Skeet Jun 26 '14 at 05:43
  • In System.Linq.Queryable there is a method called public static IQueryable Distinct(this IQueryable source, IEqualityComparer comparer) – hajirazin Jun 26 '14 at 07:39
  • @hajirazin: That's very interesting. I strongly suspect that most implementations will "know" about particular equality comparers - so you can use it to for (say) string equality comparisons - but it won't support general purpose equality comparisons, as it wouldn't be able to translate them. – Jon Skeet Jun 26 '14 at 08:04