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
– Guru Stron, Commented Oct 18, 2020 at 21:33
You have to translate your method, either into an equivalent expression (eg stackoverflow.com/questions/62687811/…) or equivalent sql (eg stackoverflow.com/questions/63992874/…) — Jeremy Lakeman
– Jeremy Lakeman, Commented Oct 18, 2020 at 23:21

Jeremy Lakeman · Accepted Answer · 2020-10-19 00:24:36Z

2

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.

edited Oct 19, 2020 at 0:24

answered Oct 18, 2020 at 23:39

Jeremy Lakeman

11.6k30 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NoStuff Over a year ago

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 ?

Jeremy Lakeman Over a year ago

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.

Collectives™ on Stack Overflow

Linq expression could not be translated Error Entity Framework Core 3

First code executed

Second file code executed

Third code part executed

ComparePrimaryKey function

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

First code executed

Second file code executed

Third code part executed

ComparePrimaryKey function

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

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related