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, this is taking an incredibly long time to run (several minutes). If I run an equivalent query in management studio (select * from table where acct_no = 12345), it takes less than a second on a very large table (~7MM rows). After some digging with the SQL profiler, I found that linq is passing the acctNum parameter as a Decimal(29,0) while the field is stored in the database as a Numeric(18,0). If I take the generated SQL and just change the parameter type from decimal to numeric, it runs in less than a second. In the profiler, I can see that the linq version uses almost 2 million reads versus about 400 for the numeric parameter query. How can I force linq to pass this parameter as numeric instead of decimal?

Answer 1

Most likely the problem lies with the type of p.ACCT_NO (in other words it was probably generated as a floating-point numeric type). Make sure that this property is also typed as an int and it should work.