Is it possible to mix writing an EF Core query with LINQ and a dynamic string query?

Question

Is it possible to mix writing an EF Core query with LINQ and a dynamic string query?

c# ef-core-2.0 entity-framework-core

Question

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.

ernest

View more on Stack Overflow

M.Hassan · Accepted Answer · 2/27/2018 9:08:26 PM

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%')

Is it possible to mix writing an EF Core query with LINQ and a dynamic string query?

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related