11

I am using Entity Framework 4.1 and do sometimes need to call stored procedures. Some of these return ints as return values. For instance

CREATE PROCEDURE ...
...
INSERT INTO ...
SELECT @@Identity

(Update: removed return value, not relevant. We're returning the identity)

I have the following code in my respository class:

var orderNo = context.Database.SqlQuery<int>("EXEC myProc").Single();

This fails with the error message The specified cast from a materialized 'System.Decimal' type to the 'System.Int32' type is not valid.

If I change the code above to 

var orderNo = context.Database.SqlQuery<decimal>("EXEC myProc").Single();

everything works.

Now, I think that I should be able to return an int. What's the proper way to do this?

Jonas Lincoln
  • 9,567
  • 9
  • 35
  • 49
  • 3
    Don't return @@Identity or you may run into unexpected behavior since @@Identity return the last id regardless of the session. Use `SCOPE_IDENTITY()` instead – Icarus Nov 15 '11 at 15:49

3 Answers3

10

I assume, based on the edit to your question - I clicked it shortly after your posting, then refreshed a bit later - that this is only an issue when you're returning the identity?

If so, it's a decimal because @@IDENTITY (and SCOPE_IDENTITY) return numeric(38,0). See the answer to this question: Why does select SCOPE_IDENTITY() return a decimal instead of an integer? As noted there, casting to int should allow the EF to properly detect the type.

Community
  • 1
  • 1
Esoteric Screen Name
  • 6,082
  • 4
  • 29
  • 38
7

Don't return @@Identity or you may run into unexpected behavior since @@Identity returns the last id regardless of the session. Use SCOPE_IDENTITY() instead

I would imagine that returning SELECT cast(SCOPE_IDENTITY() as int)

Would do for you on the C# side

Icarus
  • 63,293
  • 14
  • 100
  • 115
5

@@IDENTITY returns a numeric type with precision of 38 and scale of 0, so .NET correctly recognizes your SP as returning a decimal. You can fix this by adding a cast in your SP:

CREATE PROCEDURE ...
...
INSERT INTO ...
SELECT CAST(@@Identity as int)
dasblinkenlight
  • 151
  • 1
  • Interesting to note that this does not seem to be an issue when the SQL server is 2012, but IS if its 200 R2. Which is why I came across this topic. – Captain Kenpachi Apr 04 '13 at 15:31