How to optimize a LINQ Where condition with Any(x => DbFunctions.Like())

Question

This LINQ statement:

var entities = SomeEntities.Where(se => se.SomeProperty == "SomeValue");

var stringList = new List<string> { "string1", "string2", "string3" }
var startsWith = stringList.Select(x => x + "%");
entities = entities.Where(e => startsWith.Any(sw => DbFunctions.Like(e.StringProperty, sw))).Select(e => e.Id);

Results in a similar SQL query like this:

SELECT 
[Project8].[Id] AS [Id]
FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    FROM  [SomeEntities] AS [Extent1]
    WHERE ([Extent1].[SomeProperty] == 'SomeValue') AND( EXISTS (SELECT 
        1 AS [C1]
        FROM  (SELECT 
            N'string1%' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
        UNION ALL
            SELECT 
            N'string2%' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
        UNION ALL
            SELECT 
            N'string3%' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
        WHERE [Extent1].[StringProperty] LIKE [UnionAll2].[C1]
    )))
)  AS [Project8]
ORDER BY [Project8].[Status] DESC

How can I achieve a query similar to:

SELECT [Id]
FROM [SomeEntities]
WHERE [SomeProperty] == 'SomeValue'
AND (StringProperty] LIKE 'string1%'
    OR StringProperty] LIKE 'string2%'
    OR StringProperty] LIKE 'string3%')

`Where(se => se.SomeProperty == "SomeValue" && startsWith.Any( sw => se.StringProperty.StartsWith( sw )))` ? — Panagiotis Kanavos, Oct 09 '20 at 12:38
To get that exact SQL or something very close to it you'd have to manually create the expression. However I wouldn't worry too much about the exact SQL that is produced. Just worry about the correctness and then if there is some issue with the performance then look into fixing that. — juharr, Oct 09 '20 at 12:49
If you care about SQL and performance you shouldn't be using LINQ-to-SQL in the first place. This project was created mainly as a demonstration of LINQ. It has a *lot* of performance problems, including client-side evaluation without warning. What happens if you use EF Core instead? — Panagiotis Kanavos, Oct 09 '20 at 13:47

Panagiotis Kanavos · Answer 1 · 2020-10-09T12:47:20.963

1

You could try :

var entityIds = SomeEntities
                  .Where(se => se.SomeProperty == "SomeValue" 
                     && stringList.Any(sw => se.StringProperty.StartsWith(sw)))
                  .Select(se=>se.Id);

edited Oct 09 '20 at 12:47

answered Oct 09 '20 at 12:42

Panagiotis Kanavos

120,703
13
188
236

If you're using `StartsWith` then you need to use the `stringList` list and not the `startsWith` list that has the % added to the end of each string. Also I highly doubt that using `Any` there will result in OR statements instead of another EXISTS statement – juharr Oct 09 '20 at 12:46
@juharr oops, I mixed up the variables. – Panagiotis Kanavos Oct 09 '20 at 12:47
Using DBFunctions.Like or StartsWith is not the point. They both result in ugly SQL statements. @Ярослав-Виталиевич provided the answer to improve the resulting statement. – Niels R. Oct 09 '20 at 13:31
@NielsR. if you care about performant SQL you shouldn't be using LINQ-to-SQL in the first place. It was only meant as a demo project for LINQ and hasn't been improved for a loooooong time. I assumed you were using EF or EF Core and used the wrong tag. – Panagiotis Kanavos Oct 09 '20 at 13:47
@NielsR. in this case for example, L2S tried to create a table in that query with all those `(SELECT 1 AS X)`. If EF Core wanted to do that, it would use a table row constructor (it does this to batch multiple INSERTs in a single INSERT query) – Panagiotis Kanavos Oct 09 '20 at 13:49

Yaroslav Bres · Accepted Answer · 2020-10-09T13:16:16.853

Firstly, I suggest using PredicateBuilder for complex queries. It helps easily to create and combine expressions and then evaluate them with EF.

Then, read the difference between EF.Functions.Like and Contains, StartWith, etc and decide what one you need.

The code example:

List<string> searchStrings=new List<string>(){"name1","name2","name3"};
var predicate = searchStrings
            .Select<string, Expression<Func<Person, bool>>>(search => item => EF.Functions.Like(item.Name, $"{search}%"))
            .DefaultIfEmpty(patient => false) //or whatever else you want to do if there are no search strings
            .Aggregate(PredicateBuilder.Or);

predicate = predicate.And(item => item.Deleted == null);        
var filteredPatients = await _context.Persons.Where(predicate).ToListAsync();

The query from sql server profiler:

exec sp_executesql N'SELECT [p].[Id], [p].[B], [p].[Deleted], [p].[Email], 
[p].[Name] FROM [Persons] AS [p]
WHERE [p].[Deleted] IS NULL AND (((([p].[Name] LIKE @__Format_1) OR ([p]. 
[Name] LIKE @__Format_2)) OR ([p].[Name] LIKE @__Format_3)) AND [p].[Deleted] 
IS NULL)',N'@__Format_1 nvarchar(4000),@__Format_2 nvarchar(4000),@__Format_3 
nvarchar(4000)',@__Format_1=N'name1%',@__Format_2=N'name2%',@__Format_3=N'name3%'

score -1 · Answer 3 · answered Oct 09 '20 at 12:39

-1

entities = entities.Where(e => startsWith.Any(sw => e.StringProperty.Contains("string1") || e.StringProperty.Contains("string2") || e.StringProperty.Contains("string3") ))).Select(e => e.Id);

answered Oct 09 '20 at 12:39

S Bradshaw

84
1
7

`Contains` generates `LIKE '%abc%'` – Panagiotis Kanavos Oct 09 '20 at 12:44
If you're going to do the comparisons to each string there is no point in using `startsWith`. Also you'd want to use `StartsWith("string1")` instead of `Contains`. – juharr Oct 09 '20 at 12:44

How to optimize a LINQ Where condition with Any(x => DbFunctions.Like())

3 Answers3