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.

Answer 1

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.