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

user_120453 0
Viewed 223

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%')
linq-to-sql linq performance c#
3 Answers

You could try :

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

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%'
user_93607510 
entities = entities.Where(e => startsWith.Any(sw => e.StringProperty.Contains("string1") || e.StringProperty.Contains("string2") || e.StringProperty.Contains("string3") ))).Select(e => e.Id);
user_72877510
Related