LinqToSql converts a single C # variable to multiple SQL parameters

Question

I have a variable in C # that I use several times in a LinqToSql query. Generated SQL declares a variable twice, although it is the same physical variable in C #

WITH#

const string abc = "ABC"; var test = (from f in DataContext.Foo where f.Name == abc || f.Description == abc select f.Field1).ToList(); 

Generated SQL

 exec sp_executesql N'SELECT [t0].[Field1] FROM [dbo].[Foo] AS [t0] WHERE ([t0].[Name] = @p0) OR ([t0].[Description] = @p1)',N'@p0 varchar(3),@p1 varchar(3)',@p0='ABC',@p1='ABC' 

It is noted that @ p0 and @ p1 are both "ABC".

I tried to make the C # variable a constant to see if this helps, but it doesn't matter.

Is it possible to get LinqToSql to pass this variable only once, given that it is the same physical variable in C #? This is just a simple example, but in more complex queries, where I skip the same variable several times, I finish the number of undefined parameters in SQL.

Please ignore the actual SQL - this is just an example, it can be any SQL, I'm just wondering why the parameter is declared twice, even through the same variable in C #.

The Gavin clause using "let" generated the following SQL:

 exec sp_executesql N'SELECT [t1].[Field1] FROM ( SELECT [t0].[Field1], @p0 AS [value] FROM [dbo].[Foo] AS [t0] ) AS [t1] WHERE ([t1].[Name] = [t1].[value]) OR ([t1].[Name] = [t1].[value])',N'@p0 nvarchar(3)',@p0=N'ABC' 

So, the parameter is declared once, but now the internal SELECT is used.

Answer 1

Try the following. I have nothing to test it, but it can just do the trick.

 const string abc = "ABC"; var test = (from f in DataContext.Foo let search = abc where f.Name == search || f.Description == search select f.Field1).ToList(); 

Answer 2

This is Linq2Sql for you.

I do not think that some additional parameters will even have a noticeable effect on the performance of your system.

If you want to improve performance, there are probably other parts of your system that you should look at.

I am not saying that the request is perfect (it is not), but you probably should not worry about it.

If you desperately want to get the installation of these duplicate parameters, than write a stored procedure and build the query manually.