Is it possible to use IComparable to compare entities in Entity Framework?

Question

I have a POCO class that implements IComparable.

public interface IEntity : IComparable
{
    long Id { get; set; }

    Func<IEntity, bool> CompareFunction { get; }
}

public abstract class BaseEntity : IEntity
{
    public virtual long Id { get; set; }

    public Func<IEntity, bool> CompareFunction
    {
        get
        {
            Func<IEntity, bool> compare = EvaluateEquivalency;
            return compare;
        }
    }

    public static int Compare(BaseEntity left, BaseEntity right)
    {
        if (object.ReferenceEquals(left, right))
        {
            return 0;
        }

        if (object.ReferenceEquals(left, null))
        {
            return -1;
        }

        return left.CompareTo(right);
    }

    public static bool operator ==(BaseEntity left, BaseEntity right)
    {
        if (object.ReferenceEquals(left, null))
        {
            return object.ReferenceEquals(right, null);
        }

        return left.Equals(right);
    }

    public static bool operator !=(BaseEntity left, BaseEntity right)
    {
        return !(left == right);
    }

    public static bool operator <(BaseEntity left, BaseEntity right)
    {
        return Compare(left, right) < 0;
    }

    public static bool operator >(BaseEntity left, BaseEntity right)
    {
        return Compare(left, right) > 0;
    }

    public override bool Equals(object obj)
    {
        IEntity other;

        if (!(obj is IEntity)) return false;

        other = (IEntity)obj;

        if (object.ReferenceEquals(other, null))
        {
            return false;
        }

        return this.CompareTo(other) == 0;
    }

    public override int GetHashCode()
    {
        return base.GetHashCode();
    }

    public virtual int CompareTo(object obj)
    {
        if (obj == null) throw new ArgumentNullException("obj");
        if (!(obj is IEntity)) throw new ArgumentException("obj is not an IEntity");

        if (this.Id == ((IEntity)obj).Id) return 0;

        return -1;
    }

    private bool EvaluateEquivalency(IEntity toCompare)
    {
        return Equals(toCompare);
    }
}

There is DbSet for my POCO class in my DbContext.

However, when I execute BaseRepository.Exists() I get a System.NotSupportedException.

public class BaseRepository<TEntity> : IRepository<TEntity> where TEntity : class, IEntity
{
    ...

    private TEntity Exists(TEntity entity)
    {
        return Context.DbSet<TEntity>.FirstOrDefult(i => i.CompareTo(entity) == 0);
    }

    private TEntity ExistsV2(TEntity entity)
    {
        return Context.DbSet<TEntity>.FirstOrDefult(i => i.CompareFunction(entity) == 0);
    }

    ...
}

The exception stack trace looks like...

System.NotSupportedException was unhandled by user code
  Message=Unable to create a constant value of type '{My POCO Class}'. Only primitive types or enumeration types are supported in this context.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.Objects.ELinq.ExpressionConverter.ConstantTranslator.TypedTranslate(ExpressionConverter parent, ConstantExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.EqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.FirstPredicateTranslatorBase.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.Convert()
       at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)

When I execute BaseRepository.ExistsV2() I get a slightly different System.NotSupportedException.

System.NotSupportedException was unhandled by user code
  HResult=-2146233067
  Message=The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.Objects.ELinq.ExpressionConverter.NotSupportedTranslator.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.FirstPredicateTranslatorBase.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.Convert()
       at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)

I've read the Entity Framework doesn't support IComparable? Is there anyway around this or does anyone know whether the functionality will be available in EF6?

Throwing an `ArgumentNullException` in `CompareTo` violates the Microsoft guidelines for how `CompareTo` should behave: "By definition, any object compares greater than (or follows) `null`" (http://msdn.microsoft.com/en-us/library/system.icomparable.compareto.aspx); instead you should just return `1` (or any number > 0) when `CompareTo(null)` is invoked. — Bradley Grainger, Sep 11 '13 at 04:04
I am also having this issue, I have a a large data set. And using ajax data tables to select pages at a time. I need the orderby to happen sql server side. And i have string columns that have numbers in them, and they don,t order correctly. Neither do IP addresses. Any solutions to this? — Zapnologica, May 27 '16 at 11:08

Cory Nelson · Accepted Answer · 2013-09-09T06:24:07.737

5

No, you can't do this.

EF requires all operations to be able to execute 100% server-side. Supporting IComparable or IEquatable would require EF to be able to translate arbitrary IL into SQL, which it can't yet do.

Otherwise, it would have to pass the entire unfiltered result set to the client. You can achieve exactly this by using AsEnumerable():

Context.DbSet<TEntity>.AsEnumerable().FirstOrDefault(i => i.CompareTo(entity) == 0);

This will of course run quite slow though, so I wouldn't recommend it if the table is of any significant size.

edited Sep 09 '13 at 06:24

answered Sep 09 '13 at 06:16

Cory Nelson

29,236
5
72
110

2

AsEnumerable is bad, it will load all entities in memory. – Akash Kava Sep 09 '13 at 06:30
2

Agreed. He asked about using `IComparable`, and I gave him the only way he's going to be able to use it _and_ a warning about why not to. – Cory Nelson Sep 09 '13 at 06:41
@Akash Kava, `AsEnumerable()` will not materialize the collection. – haim770 Sep 09 '13 at 08:51
Well in that case this answer will raise same exception – Akash Kava Sep 09 '13 at 11:00
1

I need this to execute on the server. Sending the the entire list over the wire the client is not an option. – TheMagnificent11 Sep 10 '13 at 05:16

score 1 · Answer 2 · answered Sep 09 '13 at 06:16

1

Unfortunatly no (or perhaps a better answer is not easily).

Entity framework can only use methods which it knows how to translate to SQL. This is something that isnt likely to change in any version of SQL. If EF were to understand ICompariable what that really means is that it needs to be able to convert an arbitrary piece of code to SQL.

There are however some alternatives to this. You can define a reusable expression which is implemented in linq and then apply this to your entities in SQL (linq is generally translatable to SQL), this will give you similar behavior but is more complicated to implement. check out linqkit if you want to try this http://www.albahari.com/nutshell/linqkit.aspx

answered Sep 09 '13 at 06:16

undefined

33,537
22
129
198

I have updated the question to include what I tried to get this to work. Still getting `System.NotSupportedException`. Any more ideas? – TheMagnificent11 Sep 10 '13 at 06:46
@TheMagnificent11 you cant use IComparable it just wont work as it cant convert to SQL. The best you can do is use a reuable expression – undefined Sep 10 '13 at 06:54
1

@TheMagnificent11 You can't use `Func`, you need `Expression>`. And you need to use it correctly, which is what LINQKit is for. – svick Sep 11 '13 at 01:54

score 1 · Answer 3 · answered Sep 09 '13 at 06:29

No, this is not possible and it will never be possible in future as well because lComparable is part of .Net where else there is no such thing in SQL. Lambda expressions are converted to SQL WHERE clause, they are never executed.

  x ⇒ x.CustomerID == 2

gets converted to

 WHERE CustomerID =2

But for your case you can create a Reflection based method.

private TEntity Exists(TEntity entity)
{
    Type t= typeof(TEntity) ;
    ParameterExpression pe = Expression.Parameter( t ) ;
    PropertyInfo p = t.GetProperties().First(x => x.GetCustomAttributes( true ).OfType<KeyAttribute>().Any()     ) ;
    Expression e = Expression.Property( pe , p);
    e = Expression.Equal( e, p.GetValue(entity, null) );
    var l = Expression.Lambda<Func <TEntity, boot>>(e , pe) ;
    return Context.DbSet<TEntity>.FirstOrDefult(l);
}

Based on your implementation you might have to change KeyAttribute to EdmScalarDataAttribute & check if attribute has key = true or not.

Bassam Alugili · Answer 4 · 2013-09-09T20:23:20.860

0

You can make your base entity as IComparable than you can compare your loaded entities(By lazy loading might be a problem)! Entity framework does not support directly the IComparable interface.

Or you can doing something like that:

public abstract class Entity
{
  public int MyCompare(Entity entity)
  {
   ..
   ..
  }
}

private Entity Exists(Entity entity)
{
    return Context.DbSet<Entity>.ToList().FirstOrDefult(i => i.MyCompare(entity) == 0);
}

edited Sep 09 '13 at 20:23

answered Sep 09 '13 at 07:15

Bassam Alugili

16,345
7
52
70

@AkashKava yes good Point I have Forget the tolist thanks after Tolist() the FirstOrDefult will get FirstOrDefault from IEnumerable Interface and not from Linq To Entities. – Bassam Alugili Sep 09 '13 at 20:25
2

but to ToList() will loads all entities from DB this might be a Problem for performamnce. – Bassam Alugili Sep 09 '13 at 20:26

Is it possible to use IComparable to compare entities in Entity Framework?

4 Answers4

Linked