ExecuteScalar() analogue in custom Linq to Sql query

开发者_运维知识库

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?

---

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.

---

you may try changing your code from

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

to

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

Because ROW_NUMBER() function in SQL returns BigInt instead of Int