2

I've got a linq statement like this:

this.DataContext.TableName.Where(r => r.Field =="REQUEST" || r.Field== "UPDATE")

The statement when converted to sql looks like this:

 WHERE ([Extent1].[Field] IN (N'REQUEST',N'UPDATE'))

rather than:

WHERE Field = 'REQUEST' or Field = 'UPDATE'

The bottom example runs twice as fast as the top. Is anyone able to point me in the right direction in order to get the converted SQL to look like the below example.

I'm using C# ASP.Net MVC 5, EF6, but whatever I seem to try just gives the same results and uses the IN statement.

Habib
  • 219,104
  • 29
  • 407
  • 436
user1085867
  • 71
  • 1
  • 5
  • 2
    Hi Can you post an evaluation of the execution plan of both query to compare if they are running in a different way. In my opinion both will run in the same way, but check the exeution plan and post an image plase. – Juan Jul 06 '15 at 14:55
  • Have you tried `(r.Field == "REQUEST") || (r.Field == "UPDATE")` ? Also, I tend to agree with @Juan. – Orel Eraki Jul 06 '15 at 15:03

2 Answers2

2

I use LINQPad4 with:

Customers.Where(x => x.Name == "Tom" || x.Name == "Dick").Dump()

Generate:

-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'Tom'
DECLARE @p1 NVarChar(1000) = 'Dick'
-- EndRegion
SELECT [t0].[ID], [t0].[Name]
FROM [Customer] AS [t0]
WHERE ([t0].[Name] = @p0) OR ([t0].[Name] = @p1)

IN vs OR is discussed here: IN vs OR in the SQL WHERE Clause

Hope this helps.

Community
  • 1
  • 1
hungndv
  • 2,121
  • 2
  • 19
  • 20
0

In this case if the concern is performance well, you have a big long list of articles to read to have your own opinion. For once maybe you have to concern about if your column needs and index and if your statistics are updated.

If your values are request and update as a string, evaluate if you need a related table to hold this values and have a related id in your table with an integer value. This int column can have an index and this will provide better performance and a smaller footprint in your data and index size.

LINQ will try to provide the best execution based in your database structure. So for instance if you provide a good design in your database you dont have to bother in most cases of what is the result sql from the linq query. (both in big querys and with big databases always make this check, and check the execution plan)

Juan
  • 1,352
  • 13
  • 20
  • 1
    After examining the query plan I realised that the IN statement was having to do an implicit convert as it's a Varchar column and the linq create the values to search by as N'REQUEST' so a conversion to nvarchar has to be performed. It makes me wonder in how many other places performance is being comprised by this as the database i've inherited has a lot of varchar fields. Thanks for the nudge in the right direction. – user1085867 Jul 06 '15 at 15:47