How should I return an int from a stored procedure in entity framework 4.1?
Solution 1
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.
Solution 2
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
Solution 3
@@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)
Jonas Lincoln
SQL Server C# .Net PCI Manager and IT Consultant at Netlight Consulting AB
Updated on June 05, 2022Comments
-
Jonas Lincoln almost 2 years
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?
-
Captain Kenpachi about 11 yearsInteresting 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.