EF4 SPROC Complex Type Mapping - Problem with ROW_NUMBER()

0
votes

I've been working with EF4/Stored Procedures/Complex Types for a while now, but i haven't seen this issue before.

I have a stored proc which returns a bunch of fields, mapped to a collection of complex types. Was all working fine until i introduced this extra field.

It's using ROW_NUMBER from T-SQL (used in ranking results):

SELECT ... 
       ROW_NUMBER() OVER (ORDER BY [Field1], [Field2]) AS [SearchRank],
       ...
FROM   @ResultSet

In my complex type, i have this set as a non-nullable Int32, and i'm also using POCO's, so i have this as a regular int on the POCO.

But when i try and execute the query, i get this error:

System.InvalidOperationException: The 'SearchRank' property on 'RankedLocationSearchResult' could not be set to a 'Int64' value. You must set this property to a non-null value of type 'Int32'.

I just don't get it. Nowhere have i said this property/field is Int64. And my property is a non-null value of type 'Int32'.

Now, i am certain the problem is with ROW_NUMBER().

Because if i change that T-SQL to just 1 AS [SearchRank] (hard code, for testing), it works fine.

It's almost as like EF sees ROW_NUMBER() as returning Int64.

Why? Do we have to cast this as a 32-bit integer or something?

Anyone had this issue?

3
tsqlstored-proceduresentity-framework-4row-numbercomplextype

3 Answers

3
votes

So after reading up on the MSDN documentation for ROW_NUMBER(), turns out the return type for this function is bigint.

So i had to change the complex type to Int64, and my POCO property to long.

Then it works.

1
votes

I was using a view with ROW_NUMBER() usage for unique id generation.

In my case, even if I changed the complex type to Int64 and the POCO property to long, it did not work.

I had to remove the ROW_NUMBER() usage from my view. I finally merged the tables used in my view and created a single table. It worked that way.

0
votes

I Casted the ROW_NUMBER() return value to INT in the query itself as follows:

SELECT ... 
       CAST(ROW_NUMBER() OVER (ORDER BY [Field1], [Field2]) AS INT) AS [SearchRank],
   ...
FROM   @ResultSet