Linq expression could not be translated Error Entity Framework Core 3

Question

I am on a project and got some problem with data accessing. I use EF core and I have these stack trace.

First code executed

public static void Main(){
 EntityServiceBase<Product> manager = new EntityServiceBase(new EFProductDal());//efproductdal for to access database
 Product result = manager.GetByPrimaryKey(5);
}

Second file code executed

At the code IEntity is an empty interface to determine database objects
IEntityRepostiory has EF codes to access database which is shown in third code part at the belong
PrimaryKeyComparable has compare func to check if primary key of an object is equal to given key

using System;
using System.Collections.Generic;
using ECommercial.Core.DataAccess;
using ECommercial.Core.Entities;

namespace ECommercial.Core.Business
{
    public abstract class EntityServiceBase<TEntity> : IService<TEntity> 
        where TEntity :class,IEntity, new() // IENTITY IS AN EMPTY INTERFACE TO JUST DETERMINE DATABASE ENTITIES
    {
        private IEntityRepository<TEntity> _entityRepository;

        public EntityServiceBase(IEntityRepository<TEntity> entityRepository)
        {
            _entityRepository = entityRepository;
        }

        public TEntity GetByPrimaryKey(Object key)
        {
            PrimaryKeyComparable primaryKeyComparable = new PrimaryKeyComparable();
            return _entityRepository.Get(o=>primaryKeyComparable.comparePrimaryKey<TEntity>(o,key));
        }
    }
}

Third code part executed

At the code context is EF dbcontext
filter is given filter which is o=>primaryKeyComparable.comparePrimaryKey(o,key) the second code file. The error occurs at .SingleOrDefault(filter) part. If I delete it function works well.

public TEntity Get(Expression<Func<TEntity, bool>> filter)
        {
            using (var context=new TContext()){
                TEntity result = context.Set<TEntity>().SingleOrDefault(filter);
                return result;
            }
        }

ComparePrimaryKey function

public bool comparePrimaryKey<TEntity>(TEntity entity,Object value)
            where TEntity:class,IEntity,new()
        {
            Type entityType = typeof(TEntity);
            FieldInfo[] fields = entityType.GetFields();
            FieldInfo found =null;
            foreach(var field in fields){
                Attribute attribute=field.GetCustomAttribute(typeof(PrimaryKeyFieldAttribute));
                if(attribute!=null){
                    found=field;
                    break;
                }
            }
            if(found!=null){
                Type foundDeclaringType= found.DeclaringType;
                Type valueDeclaringType = value.GetType().DeclaringType;
                if((foundDeclaringType.IsSubclassOf(valueDeclaringType)) ||valueDeclaringType.IsSubclassOf(foundDeclaringType) || valueDeclaringType==foundDeclaringType)
                {
                    return entity.Equals(value);
                }
                throw new InvalidCastException("Given entity's primary key must have same or child-base related declaration type with key object compared");
            }
            throw new CustomAttributeFormatException("The Entity Has Any field has PrimaryKeyFieldAttribute attribute. Must be PrimaryKeyAttribute on primary key field.");
        }

The Error Message (I think english parts is enough to understand)

ERROR OCCURS AT THE THIRD FILE AT .SingleOrDefault(filter) PART

Exception has occurred: CLR/System.InvalidOperationException
'System.InvalidOperationException' türünde özel durum Microsoft.EntityFrameworkCore.dll öğesinde oluştu, fakat kullanıcı kodunda işlenmedi: 'The LINQ expression 'DbSet<Product>
    .Where(p => new PrimaryKeyComparable().comparePrimaryKey<Product>(
        entity: p, 
        value: __key_0))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'
   konum Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   konum Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   konum Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   konum System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   konum System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   konum Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   konum Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   konum System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   konum System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   konum Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   konum Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   konum Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   konum Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   konum Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   konum Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   konum Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   konum Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   konum System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
   konum ECommercial.Core.DataAccess.EntitiyFramework.EFIEntityRepositoryBase`2.Get(Expression`1 filter) C:\Users\nihaSWin\Desktop\ECommercial\ECommercial.Core\DataAccess\EntitiyFramework\EFIEntityRepositoryBase.cs içinde: 36. satır
   konum ECommercial.Core.Business.EntityServiceBase`1.GetByPrimaryKey(Object key) C:\Users\nihaSWin\Desktop\ECommercial\ECommercial.Core\Business\EntityServiceBase.cs içinde: 26. satır
   konum ECommercial.MVC.Startup..ctor(IConfiguration configuration) C:\Users\nihaSWin\Desktop\ECommercial\ECommercial.MVC\Startup.cs içinde: 19. satır

EF analyzes expression trees passed to `IQueryable` and tries to translate your code to SQL, it does not know how to translate your `comparePrimaryKey` method call into SQL. — Guru Stron, Oct 18 '20 at 21:33
You have to translate your method, either into an equivalent expression (eg https://stackoverflow.com/questions/62687811/how-can-i-convert-a-custom-function-to-a-sql-expression-for-entity-framework-cor) or equivalent sql (eg https://stackoverflow.com/questions/63992874/ef-core-group-by-month-and-year-with-utc-dates) — Jeremy Lakeman, Oct 18 '20 at 23:21

Jeremy Lakeman · Accepted Answer · 2020-10-19T00:24:36.743

So it looks like you're trying to use reflection to determine the primary key and query based on that. You can't use a method like that directly in an expression and expect EF to untangle what it does and turn it into sql.

However, you could use reflection to generate an expression and use that directly instead.

public Expression<Func<TEntity, bool>> comparePrimaryKey<TEntity>(object value)
{
    var parm = Expression.Parameter(typeof(TEntity), "e");
    var objType = value.GetType();
    return Expression.Lambda<Func<TEntity, bool>>(
        typeof(TEntity)
            .GetFields()
            .Where(f => f.GetCustomAttribute(typeof(PrimaryKeyFieldAttribute)) != null)
            .Select(f => (Expression)Expression.Equal(
                Expression.MakeMemberAccess(parm, f),
                Expression.Constant(
                    (objType == f.FieldType)
                        ? value
                        : objType.GetField(f.Name).GetValue(value),
                    f.FieldType)
            ))
            .Aggregate((l, r) => Expression.AndAlso(l, r)),
        parm);
}

//...

return _entityRepository.Get(primaryKeyComparable.comparePrimaryKey<TEntity>(key));

Though I would recommend using dbContext.Model metadata to discover the primary key, instead of depending on reflection and attribute conventions.

Thanks a lot. I am beginner yet. So I do not now all concepts of EF. I will make a search about metada of model. About function, it worked. As I understand, Translation process is just about to make whole parameter a method has return value an expression is it right ? — NoStuff, Oct 19 '20 at 05:18
The idea is to manually create the expression tree. eg comparePrimaryKey should return something equivalent to `(e) => e.Id == 1` or `(e) => e.Pk1 == 1 && e.Pk2 == 2` depending on types & value argument. — Jeremy Lakeman, Oct 19 '20 at 05:22