ExecuteScalar() analogue in custom Linq to Sql query

Question

I need to execute a custom sql query that i cannot do with regular L2S means:

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'm trying

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

but that doesn't work (getting System.InvalidCastException: Specified cast is not valid). Any suggestions?

Answer 1

Change your code to:

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

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

T-SQL function ROW_NUMBER return type is bigint, not int as you expected.