How to fully simulate the behavior of a SQL Like in Linq-to-Entities

Question

I'm looking to fully simulate the behavior of a SQL Like in Linq-to-Entities.

I don't want to use StartsWith, EndsWith or Contains, which are not working properly when the original query is:

t_test LIKE '%';

I also cannot use Linq-to-SQL methods within SqlFunctions, because I'm not always using a SQL environment over Entity (espacially when testing my repositories with mocked DbContext).

In my main case, Entity is plugged to an Oracle SQL database.

So far, I've tried this lambda in my Where clause (I'm using '*' as the escape character instead of '%'):

x => (testName.StartsWith("*") && testName.EndsWith("*") &&
                         x.Name.Contains(testName.Replace("*", "")) ||
                         testName.StartsWith("*") &&
                         x.Name.EndsWith(testName.Replace("*", "")) ||
                         testName.EndsWith("*") &&
                         x.Name.StartsWith(testName.Replace("*", ""))))

Entity translate this in the following Oracle SQL query:

SELECT 
"Extent1"."KEY" AS "KEY", 
"Extent1"."NAME" AS "NAME", 
FROM "T_NAME" "Extent1"
WHERE ((('*' LIKE '*%') AND ('*' LIKE '%*') AND (( NVL(INSTR("Extent1"."NAME", REPLACE('*', '*', '')), 0) ) > 0)));

which does not return anything.

Any clue or help would be awesome.

For the uninitiated, can you describe what behavior Oracle SQL gives when you query `WHERE t_test LIKE '%'`, which you're trying to emulate? In what ways does `Contains()` fail to imitate LIKE? And is this question purely about getting LINQ to Objects to behave like LINQ to Entities for the sake of getting unit tests to pass? — StriplingWarrior, Jun 13 '19 at 15:35
I think something like [this extension](https://stackoverflow.com/a/42307642/2557128) might do what you want. — NetMage, Jun 13 '19 at 19:30
@StriplingWarrior: Sure. This query in Oracle SQL returns the whole table. Contains() fail to imitiate LIKE because the SQL query built up by the provider (written above) does not translate the full behavior (it returns nothing when it should returns everything). But Contains(), in a Linq-to-Objects context works totally fine. And you're right, I need to have my unit tests to pass, that's why I can't use Linq-to-SQL (that's also why I can't simply use Regex.IsMatch) — Quentin S., Jun 14 '19 at 06:36
If the query is meant to return the whole table, why not omit the `Where` clause entirely? — StriplingWarrior, Jun 14 '19 at 14:44
I find that for classes whose sole purpose is interacting with the database, having correct behavior (verifiable via _Integration_ Tests) is more important than having code that passes _unit_ tests. Tests aren't really valuable if the code behaves differently at run-time: the tests can pass and give you a false sense of security, and then fail when you actually execute. — StriplingWarrior, Jun 14 '19 at 14:58

score 1 · Accepted Answer · answered Jun 14 '19 at 07:34

I finally found the problem.

Acutally, it seems that testName.Replace("*", "") was misinterpreted. When doing the treatement outside of the Linq, the SQL query which is built up is correct, and everything works fine.

The correction:

string correctName = testName.Replace("*", "");

x => (testName.StartsWith("*") && testName.EndsWith("*") &&
                         x.Name.Contains(correctName) ||
                         testName.StartsWith("*") &&
                         x.Name.EndsWith(correctName) ||
                         testName.EndsWith("*") &&
                         x.Name.StartsWith(correctName)))

The SQL coming out of this is:

SELECT
"Extent1"."KEY" AS "KEY", 
"Extent1"."NAME" AS "NAME",
FROM "T_NAME" "Extent1"
WHERE (('*' LIKE '*%') AND ('*' LIKE '%*') AND ("Extent1"."NAME" LIKE "%%" ESCAPE '\'))

Now everything works perfectly fine.

Are you intentionally only allowing `*`s at the start and end of name? Someone couldn't search "Jo*an" to get "Johann" and "Jonathan"? Or is that an accidental side-effect of the fact that you're translating this to LINQ to Entities? This may be one of those cases where dropping to SQL is more appropriate. You could replace "*" with "%" in the given string, and then use the resulting string in a parameterized query with a direct LIKE check. — StriplingWarrior, Jun 14 '19 at 14:57

How to fully simulate the behavior of a SQL Like in Linq-to-Entities

1 Answers1