LINQ to SQL Decimal Parameter

Question

I have a very simple linq to sql query in C #:

int acctNum = 12345; var query = from p in db.table where p.ACCT_NO == acctNum select p; 

This generates the following SQL:

 exec sp_executesql N'SELECT [t0].field1, [t0].field2, [t0].ACCT_NO FROM [dbo].[table] AS [t0] WHERE [t0].[ACCT_NO] = @p0', N'@p0 decimal(29,0)', @p0 = 12345 

For some reason, it takes a very long time to start (a few minutes). If I run an equivalent query in the management studio (select * from the table where acct_no = 12345), it takes less than a second in a very large table (rows ~ 7MM). After some digging with the SQL profiler, I found that linq passes the parameter acctNum as decimal (29.0), and the field is stored in the database as numeric (18.0). If I take the generated SQL and just change the parameter type from decimal to numeric, it works in less than a second. In the profiler, I see that the linq version uses almost 2 million reads, against 400 to request numerical parameters. How to force linq to pass this parameter as a numeric rather than decimal?

Answer 1

Most likely, the problem lies in the type p.ACCT_NO (in other words, it was probably generated as a floating point number type). Make sure this property is also printed as int , and it should work.