Is it possible to use IComparable to compare objects in the 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); } } 

For my POCO class in DbContext there is a DbSet .

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 am a little different than 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 read that Entity Framework does not support IComparable ? Anyway, or does anyone know if functionality will be available in EF6?

Answer 1

No, you cannot do this.

EF requires that all operations be performed on a 100% server side. Support for IComparable or IEquatable will require that EF can translate arbitrary IL into SQL, which it still cannot do.

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

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

This, of course, will be rather slow, so I would not recommend it if the table is significant in size.

Answer 2

Unfortunately, no (or maybe the best answer is not easy).

An Entity structure can only use methods that it knows how to translate into SQL. This is something that is unlikely to change in any version of SQL. If EF understood ICompariable what this means, it means that it should be able to convert an arbitrary piece of code into SQL.

However, there are some alternatives to this. You can define a reusable expression that is implemented in linq, and then apply it to your entities in SQL (linq is usually translated into SQL), this will give you similar behavior, but harder to implement. check linqkit if you want to try this http://www.albahari.com/nutshell/linqkit.aspx

Answer 3

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

  x ⇒ x.CustomerID == 2 

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 need to change KeyAttribute to EdmScalarDataAttribute and check if the attribute key = true or not.

Answer 4

You can make your base object as IComparable, than you can compare your loaded objects (with lazy loading, a problem may arise)! Entity structure does not directly support IComparable interface.

Or you can do something like this:

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