Is there any way to optimize this LINQ where clause that searches for multiple keywords on multiple
- by Daniel T.
I have a LINQ query that searches for multiple keywords on multiple columns. The intention is that the user can search for multiple keywords and it will search for the keywords on every property in my Media entity. Here is a simplified example:
var result = repository.GetAll<Media>().Where(x =>
x.Title.Contains("Apples") || x.Description.Contains("Apples") || x.Tags.Contains("Apples") ||
x.Title.Contains("Oranges") || x.Description.Contains("Oranges") || x.Tags.Contains("Oranges") ||
x.Title.Contains("Pears") || x.Description.Contains("Pears") || x.Tags.Contains("Pears")
);
In other words, I want to search for the keywords Apples, Oranges, and Pears on the columns Title, Description, and Tags.
The outputted SQL looks like this:
SELECT *
FROM Media this_
WHERE ((((((((
this_.Title like '%Apples%'
or this_.Description like '%Apples%')
or this_.Tags like '%Apples%')
or this_.Title like '%Oranges%')
or this_.Description like '%Oranges%')
or this_.Tags like '%Oranges%')
or this_.Title like '%Pears%')
or this_.Description like '%Pears%')
or this_.Tags like '%Pears%')
Is this the most optimal SQL in this case? If not, how do I rewrite the LINQ query to create the most optimal SQL statement? I'm using SQLite for testing and SQL Server for actual deployment.
