Error converting parameter in Linq query Entity Framework Core

Question

For SEO, I delete Turkish characters and spaces.

But I can't format the LINQ parameter, it gives the error.

The LINQ expression 'DbSet .Where(p => p.ProductNameTR .trCharToEnChar() == __productName_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.

public List<TEntity> GetAll(Expression<Func<TEntity, bool>> filter = null)
{
    TContext context = new TContext();
    return filter == null ?
        context.Set<TEntity>().ToList() :
        context.Set<TEntity>().Where(filter).ToList();
}

public static string trCharToEnChar(this string str)
{
    var newStr = "";
    for (int i = 0; i < str.Length; i++)
    {
        newStr = str.Replace(" ", "_");
        newStr = newStr.Replace("ı", "i");
        newStr = newStr.Replace("ö", "o");
        newStr = newStr.Replace("ü", "u");
        newStr = newStr.Replace("ğ", "g");
        newStr = newStr.Replace("ş", "s");
        newStr = newStr.Replace("ç", "c");
        newStr = newStr.Replace("ü", "u");
    }

    return newStr;
}

public List<Products> GetProductsByProductNameTextConvert(string productName)
{
    return _productDal.GetAll(p => p.ProductNameTR.trCharToEnChar() == productName);
}

Better would be to have a persisted computed column of ProductNameTR with the characters replaced. Than you dont have to do it in your query every time, and table indices could be used. — Magnus
– Magnus, Commented Dec 11, 2019 at 13:08

phuzi · Accepted Answer · 2019-12-11 13:03:50Z

2

The problem occurs because you are using EF Core 3.0 or greater and EF is unable to translate the expression p => p.ProductNameTR.trCharToEnChar() == productName to SQL.

Prior to EF Core 3.0 the whole set of data would be returned and then further filtered by applying the expression to the result set, returning the filtered result set.

A quick fix would be to change _productDal.AsEnumerable() to _productDal.ToList() to force EF to retrieve the data before filtering. AsEnumerable() isn't sufficient to cause EF to execute the query at the database.

answered Dec 11, 2019 at 13:03

phuzi

13.3k4 gold badges30 silver badges65 bronze badges

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

Comments

Salah Akbari · Accepted Answer · 2019-12-11 13:56:28Z

1

Because LINQ does not know how to translate your trCharToEnChar method to SQL, you can use .ToList, AsEnumerable() or other methods as suggested by error itself to solve it. With .ToList() after data is loaded, any further operation (such as select) is performed using Linq to Objects, on the data already in memory:

context.Set<TEntity>().AsEnumerable().Where(filter).ToList();
// Or context.Set<TEntity>().ToList().Where(filter).ToList();

edited Dec 11, 2019 at 13:56

answered Dec 11, 2019 at 12:54

Salah Akbari

40k10 gold badges85 silver badges115 bronze badges

1 Comment

zaferfrtlec1 Over a year ago

I saw your answer. Once I've received the entire list, filtering is detrimental to this performance, if I don't have a solution, I'll do it last.

Collectives™ on Stack Overflow

Error converting parameter in Linq query Entity Framework Core

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related