I was hoping to be able to write most of the query in LINQ, but then write the WHERE
clause as a string, like you would a dynamic query. So something like:
var query = from part in _context.CurrentInventory
query.Where = "part.PartNumber LIKE '%a%' OR part.PartNumber LIKE '%b%'
The reason is because the WHERE can get quite large and using EF.Functions.Like
and Contains
really slows down the query.
I could build the whole thing dynamically and just execute the string, but I was hoping to avoid that.
You can use System.Linq.Dynamic which is based on previous work
Install-Package System.Linq.Dynamic
The query
query.Where = "part.PartNumber LIKE '%a%' OR part.PartNumber LIKE '%b%'
can be re-written as:
query.Where ("part.PartNumber.Contains(@0) or part.PartNumber.Contains(@1)","a" ,"b")
Working example at fiddle
Update:
I used dynamic Linq with EntityFramework
using (var context = new NorthwindEntities())
{
var customers = context.Customers.Where("ContactName.Contains(@0) or ContactName.Contains(@1)", "Maria","Carine").ToList();
Console.WriteLine(customers.Count);
}
The generated Sql (as seen from sql profiler) is:
SELECT
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Address] AS [Address],
[Extent1].[City] AS [City],
[Extent1].[Region] AS [Region],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Country] AS [Country],
[Extent1].[Phone] AS [Phone],
[Extent1].[Fax] AS [Fax]
FROM [dbo].[Customers] AS [Extent1]
WHERE ([Extent1].[ContactName] LIKE N'%Maria%') OR ([Extent1].[ContactName] LIKE N'%Carine%')
The where condition is translated to:
WHERE ([Extent1].[ContactName] LIKE N'%Maria%') OR ([Extent1].[ContactName] LIKE N'%Carine%')