-1

I tried to use DbFunctions.Like with EF 6.2 and got run-time error:

LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method, and this method cannot be translated into a store expression.

Code:

list=list.Where(p=> DbFunctions.Like(p.Master_Bill,"somestring%")); where list is IQueryable<SomeView>

It compiles OK. I thought it can be used with EF 6.2. I know there is also EF Core, did not look at it

Any ideas? thanks

jarlh
  • 42,561
  • 8
  • 45
  • 63
NGC
  • 1

1 Answers1

0

What about

list = list.Where( p => p.Master_Bill.StartsWith(someString));

?

If the user can enter a wildcard(s) in their search string you can evaluate the string for legal combinations I.e. "?somestring", "somestring?" or "?somestring?" then choose the appropriate where condition.

wildcardResult = evaluateWildcard(someString);
switch(wildcardResult.Result)
{
    case WildcardResult.NoWildcard:
        list = list.Where( p => p.Master_Bill == wildcardResult.SearchString);
        break;
    case WildcardResult.StartsWith:
        list = list.Where( p => p.Master_Bill.StartsWith(wildcardResult.SearchString));
        break;
    case WildcardResult.EndsWith:
        list = list.Where( p => p.Master_Bill.EndsWith(wildcardResult.SearchString));
        break;
    case WildcardResult.Contains:
        list = list.Where( p => p.Master_Bill.Contains(wildcardResult.SearchString));
        break;
}

Where the result class contains an enum for the detected search expression pattern, and the search expression with the wildcard characters stripped to use as the SearchString.

It would also be advisable to evaluate the length of the search string for a minimum viable length when using wildcards. Users could trigger rather expensive queries by using expressions like "?" or "?e?".

Edit: DbFunctions.Like does work as well with SQL Server. Any error you are getting is likely due to an assumption about the IQueryable you are running or the field you are comparing. (I.e. not a mapped column, or a particular data type?)

For instance: Something like this works just fine..

var data = _context.People.Where(p => DbFunctions.Like(p.Name, "s%")).ToList();

Which would return all People with a Name starting with "S". (case insensitive)

I'd look at what your entire IQueryable looks like, as well as that Master_Bill is both a mapped column and a regular NVARCHAR/VARCHAR column.

Steve Py
  • 26,149
  • 3
  • 25
  • 43
  • Thanks. I understand i have to escape characters that are special for LIKE usage, % and _ i believe, but it sems this is not an issue. it gives run-time error cannot translate to SQL. StartsWith is something we are trying to get away from. it uses CharIndex SQL Server function, not Like. according to our DB Admin it does not use Index. I also looked at execution plan and it indeed does not. Contains results in %string%, we need it anchored at the beginning of the string, but my main problem is error. It looks like EF 6.2 cannot translate DBFunctions.Like to SQL.Also string is limited in len. – NGC Oct 06 '20 at 23:56
  • I found quite a few mentions on the Internet that with 6.2 EF I should be able to to use DBFunctions.Like. but who knows... – NGC Oct 07 '20 at 00:08
  • Yes, DbFunctions.Like will work as well. You will need to provide a minimum reproducible example to determine why it isn't working for you. I've done a test like what you outlined with EF 6.2 and SQL Server and received no error so there is some other factor at play. – Steve Py Oct 07 '20 at 00:29
  • thanks again. column is mapped varchar column. I think simething about IQueryable is the reason or smth weird altogether. thanks for your suggestions – NGC Oct 07 '20 at 00:50
  • Consider updating your example to show how `list` is being populated. If it's rather complex then there may be a limit to what EF can translate depending on where the dbFunctions method is in the processing chain. – Steve Py Oct 07 '20 at 02:47
  • I think you are right. list is constructed by initially applying Where to _context var which is ObjectSet. it returns IQueryable to which Where is repeatedly applied.Then it is ordered and paging mechanism is applied before it is executed by calling ToLIst(). so probably somewhere there that Like function confuses entity, i will try simpler query tomorrow, just to test theory. Thanks – NGC Oct 07 '20 at 04:20
  • issue with run-time error on calling DBFunctions.Like with EF 6.2 solved I guess. My project was upgraded to use Entity 6.2 from 4.0 but edmx was created using Entity 4.0, so it fails translation. We created a small project from scratch using 6.2 code-first - works OK. Using 6.2 edmx works OK. Translates to LIKE SQL. we then used edmx created with EF 4.0 - Bingo same error. Does not help me though, unless I recreate edmx. Huge project and application is heavily used – NGC Oct 11 '20 at 01:01