SQL Like clause in LINQ

Question

I am trying to migrate a SQL Server stored procedure to LINQ and I am having difficulties when I have a list of partial matches that I need to find in the main table.

In short I need to replicate the following SQL into a LINQ. Any help would be much appreciated.

SQL

DECLARE @filters TABLE([filter] NVARCHAR(20))

INSERT INTO @filters VALUES ('Den%');
INSERT INTO @filters VALUES ('%zil');
INSERT INTO @filters VALUES ('%la%');

SELECT c.*  
FROM [Northwind].[dbo].[Customers] c
INNER JOIN @filters f ON c.Country LIKE (f.filter)
ORDER BY Country

C#

var filters = new string[] { "Den*", "*zil", "*la*" };

var results = from C in ctx.Customers
              join f in filters c.Country like f
              Select new 
                     {
                         c.CustomerId,
                         c.Country
                     };

I assume you realize that a row where country = 'Denlazil" would return 3 rows? — D Stanley, Nov 05 '21 at 16:46

Facundo · Answer 1 · 2021-11-09T13:24:54.237

0

Yo could use the following example:

var result = context.Customers.AsNoTracking()
                    .Include(x => x.Country)
                    .Where(x => x.Country.Contains("Den"));

as this example:

https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.contains?view=net-5.0

edited Nov 09 '21 at 13:24

answered Nov 06 '21 at 16:19

Facundo

39
1
3

score 0 · Answer 2 · edited Nov 06 '21 at 17:37

0

var result = context.Customers.AsNoTracking()
                    .Include(x => x.Country)
                    .Where(x => x.Country.Contains("la") || x.Country.Startwith("Den") || x.Country.EndWith("zil"))

edited Nov 06 '21 at 17:37

Tomerikoo

18,379
16
47
61

answered Nov 06 '21 at 17:01

mouhamadou diallo

11
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 06 '21 at 17:38

score 0 · Answer 3 · edited Nov 06 '21 at 21:18

You can use EF.Functions.Like() method provided in EF Core. We use % for LIKE in strings and not *. So your query would look like:

var filters = new string[] { "Den%", "%zil", "%la%" };

var result = context.Customers.AsNoTracking()
                 .Where(c => filters.Any(f => EF.Functions.Like(c.Country, f)))
                 .OrderBy(c => c.Country)
                 .ToList();

If you just have one filter then your query would simplify to:-

var filter = "%la%";

var result = context.Customers.AsNoTracking()
                 .Where(c => EF.Functions.Like(c.Country, filter))
                 .OrderBy(c => c.Country)
                 .ToList();

SQL Like clause in LINQ

3 Answers3