ExecuteScalar () in Linq to Sql custom query

Question

I need to execute a custom SQL query, which I cannot do with regular L2S:

select [row_number] from (select row_number() over (order by CreatedOn desc, ID desc) as [row_number], ID from MyTable) as T1 where ID = {0}

so i try

var r = db.ExecuteQuery<int>(q, id).Single();

but this will not work (getting System.InvalidCastException: the specified listing is not valid). Any suggestions?

+3

Usercontrol Mar 21 '11 at 14:55

2 answers

you can try changing your code from

int recordsAffected = SqlDBUtils.GetInt(dr, "SerialNumber");

to

int recordsAffected = SqlDBUtils.GetInt64(dr, "SerialNumber");

Since a function ROW_NUMBER()in SQL returns BigIntinstead of Int

0

Ashok tewatia Jul 31 '13 at 6:21

Alex · Accepted Answer · 2011-03-21T15:24:25+0000

Change your code to:

var r = db.ExecuteQuery<long>(q, id).Single();

changing the return type from System.Int32( int) to System.Int64( long).

The T-SQL function ROW_NUMBERreturns a type bigint, not intas you expected.