score:1
you seems to be hitting yet another ef core limitation. the problem is not the ef.functions.like
method, but the usage of the interpolated string inside, which as you found works inside regular query, but not inside compiled query definition.
the solution/workaround is to use string concatenation in place of string interpolation:
private static func<dbcontext, string, ienumerable<class1>> search =
ef.compilequery((dbcontext context, string query) => context.class1
.where(c => ef.functions.like(c.param1, "%" + query + "%") // <--
&& c.param2== 1
&& c.param3!= 1)
);
the resulting sql query is a bit different, but at least you got a translation.
score:0
if you are using ef core
, you don't need using compilequery
, just use ef.function.like
as below
var users = context.users.where(a => ef.functions.like(a.firstname, "%a%")).tolist();
so rewriting your code in this way would be like this:
var result= context.class1
.where(c => ef.functions.like(c.param1, $"%{query}%")
&& c.param2 == 1
&& c.param3 != 1).tolist();
updated: if you want to get its result as t-sql, you can use toquerystring()
like this:
var users = context.users.where(a => ef.functions.like(a.firstname, "%a%")).toquerystring();
score:1
private static func<dbcontext, string, ienumerable<class2>> search =
ef.compilequery((dbcontextcontext, string query) =>
context.class1
.where(c => c.param1.tolower().contains(query) && c.param2== 1 && c.param3!= 1)
.orderby(p => p.param1.tolower().startswith(query) ? 0 : 1)
.select(a => new class2{ t1 = a.a1, t2 = a.a2, t3 = a.a3 })
.take(10).asnotracking());
Source: stackoverflow.com
Related Query
- EF.Functions.Like in a compiled query could not be translated to SQL equivalent
- Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet'
- The LINQ expression could not be translated. Eiither rewrite the query in a form that can be translated
- Avoid extra loop and could not find implementation of query pattern for source type int Select not found
- Could not find an implementation of the query pattern for source type
- Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet` 'Where' not found
- System.InvalidOperationException: 'The LINQ expression could not be translated. Either rewrite the query in a form that can be translated
- The LINQ expression 'Expression' could not be translated. Either rewrite the query in a form that can be translated
- LINQ to SQL - Expression checking for null could not be translated
- Could not find an implementation of the query pattern for source type 'Join'
- The LINQ expression 'GroupByShaperExpression: could not be translated while using GROUPBY in LINQ query
- Entity Framework Core 3.1 - Linq - Could not be translated. Either rewrite the query in a form that can be translated
- Get next element from Query after element with specified Id. The LINQ expression could not be translated
- Equivalent LINQ query for SQL query not resulting in expected results
- What would this sql query (w count and group by) look like when translated to linq?
- EF Core (LINQ) - The Query expression could not be Translated
- Orderby could not be translated. Either rewrite the query in a form that can be translated
- Query Source could not be identified
- Th linq query could not be translated. Either rewrite the query in a form that can be translated .NETCore
- Struggling to convert SQL query to it's LINQ equivalent - Multiple joins, groupings and aggregate functions
- Could not find an implementation of the query pattern
- How to write linq query to match SQL like select top 100 * from tab?
- The LINQ expression could not be translated and will be evaluated locally
- LINQ to SQL query not returning correct DateTime
- Simple sql to Linq query with group by and aggregate functions
- Linq "Could not translate expression... into SQL and could not treat it as a local expression."
- The LINQ expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation EF Core 3.1
- SQL Query to LINQ syntax using not exist and join
- Where to find translated Linq to Entity query to Sql
- The LINQ expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation
More Query from same tag
- Asp.net linq query
- linq lambda convert object with list of objects of the same type to another object
- Type declaration in linq in lambda syntax
- How can I order query by SelectListItem?
- How to create Group By LINQ query
- C# Find and edit single object on list
- Unable to cast object of type 'System.Data.Common.DataRecordInternal' to type 'System.Data.IDataReader'
- Optimizing A LINQ To Objects Query
- How to count how many fields in a JObject that are set with LINQ?
- Update a record in L2S and L2E
- Merge two collections in Linq
- Creating where condition for LINQ within switch statement
- Linq for nested loop
- Write Aggregate as Sql Syntax
- Multiple Distincts with Linq
- SortedDictionary in reverse order of keys
- how to use entity framework to group by date not date with time
- Using templates and Create conflicts for creating a Person Object
- ToAsyncEnumerable().Single() vs SingleAsync()
- Keep throwing an error and I do not know why
- Cross Apply - LINQ to Objects
- Can I use LINQ to skip a collection and just return 100 records?
- How to consolidate a Dictionary with multiple (three) keys by reducing one or more key and merging the multiple records (if any) into one single one?
- adding a new element below specific node in a xml file using LINQ - C#
- Linq to XML Disable default namespace
- Difference between binding object/properties in ListView and ListBox
- Dictionary group by value
- Simplify Entity Framework Core query
- C# Linq question
- Generic Get method does not include FK's?