4

I have the following piece of code:

IQueryable<SomeType> allItems = ...;
var selectedItems = allItems.Where( item => ( item.State == 1 || item.State == 3 ) );
Console.WriteLine( selectedItems.ToString() );

and I get the following query dumped:

SELECT [t0].[ItemId], [t0].[State], <other columns>
FROM [Items] AS [t0]
WHERE [t0].[State] = @p0 OR [t0].[State] = @p1

You see, actual values 1 and 3 are not inserted into query, instead they are passed as parameters which often causes selection of suboptimal execution plan.

Can I somehow make Ling-To-Sql emit WHERE [t0].[State] = 1 OR [t0].[State] = 3 instead?

sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • I doubt that you can force it into generating constants in place of parameters: they think that parameters are better, so they use them everywhere. You could try `item => new[] {1,3}.Contains(item.State)`, but I suspect that you would get `IN (@p0, @p1)` in the SQL. – Sergey Kalinichenko Oct 31 '14 at 14:36
  • Yeap, I get exactly `IN (@p0, @p1)` when I do so. – sharptooth Oct 31 '14 at 14:37
  • So you think LINQ should open up to SQL injection attacks? http://bobby-tables.com/ – paparazzo Oct 31 '14 at 15:58
  • @Blam: How exactly would injecting a number cause unintended behavior? – sharptooth Nov 01 '14 at 11:56
  • SQL injection attacks are well documented. – paparazzo Nov 01 '14 at 12:49
  • @Blam: Sure, can you alter a query by injecting `1` please? – sharptooth Nov 01 '14 at 13:06
  • I don't promised to inject a 1 – paparazzo Nov 01 '14 at 13:17
  • @Blam: I was asking "how do I get `1` injected" and you rushed in waving *Oh no, Bobby Tables is here* flag. What was your claim then? – sharptooth Nov 01 '14 at 13:35
  • What part of "So you think LINQ should open up to SQL injection attacks?" is not clear. News flash - LINQ is not performant. You get some ugly stuff out of LINQ and what you are getting is not even ugly. What next locks and join hints? That is like saying my minivan is a little loose in turns 3 put a spoiler on it and take out the seat belts while you are at it. If you want to validate your data and tune your query there is a tool - TSQL. – paparazzo Nov 02 '14 at 15:19
  • Is it possible? Query performance is not optimal with parameters. – selami Mar 04 '15 at 15:54
  • @hanzala Looks like it is not possible. – sharptooth Mar 05 '15 at 15:26
  • 1
    Maybe, you can get SQL command with DataContext.GetCommand(query) method, and change SQL parameters with String.Replace and then execute command with classical way. But be careful for SQL injection. – selami Mar 06 '15 at 15:12
  • dc.GetCommand(query).CommandText.Replace("@p0", 1) . . . – selami Mar 06 '15 at 15:13
  • @hanzala Maybe that will work but it's so much effort. I'd be happy if the ORM would just generate a good enough SQL query itself. – sharptooth Mar 06 '15 at 15:18

1 Answers1

-1

There are couple of ways to manipulate the sql once Linq to sql has generated it as described here and here, but I would not recommend using either of them as they will likely be stringy and brittle.

Paramaterized sql is one of the key benefits of using an orm like linq to sql or entity framework, this prevents sql injection. Sql server will also cache the execution plans for paramaterized sql resulting in increased overall application performance.

This is a good article on the benefits of paramaterized sql.

Miniver Cheevy
  • 1,667
  • 2
  • 14
  • 20
  • 1
    This is not funny. I know what SQL injection is. In my case the values are guaranteed to be sanitized. – sharptooth Nov 01 '14 at 12:01
  • 1
    this can be useful when you have a filtered index (eg `WHERE x=1 AND y=2`) but if these are parameters they won't be picked up by the index unless the columns are part of the index itself – Simon_Weaver Feb 27 '17 at 07:32