0

Given:

In the original post I was attempting to call db.Database.ExecuteCommand, since then I changed that to what you see here. (Which works). Execute Command was always returning 0xfffff. SQLQuery returns the value of the last select statement or the rows inserted depending on how you code it up.

using (db=new MyEntites())
{
    var inserted = db.Database.SqlQuery<decimal>(query,parms}
}

Where last two lines in query are: 

SET NOCOUNT OFF  
Select SCOPE_IDENTITY() AS newID
T.S.
  • 18,195
  • 11
  • 58
  • 78
JWP
  • 6,672
  • 3
  • 50
  • 74

2 Answers2

1

From the documentation for ExecuteSqlCommand method, the return value is

The result returned by the database after executing the command.

This means you need to RETURN the value in your procedure, not as a result set. So do this in your stored procedure as the last command:

RETURN SCOPE_IDENTITY()
DavidG
  • 113,891
  • 12
  • 217
  • 223
  • I'm not using Stored Procedures, rather writing direct queries. – JWP Oct 09 '14 at 15:34
  • SET NOCOUNT ON; INSERT INTO dbo.SomeTable (field1,field2,field3) VALUES (@fields1,@field2,@field3); Select SCOPE_IDENTITY(); SET NOCOUNT OFF; – JWP Oct 09 '14 at 15:34
  • If you're not using a stored procedure (and maybe if you are, memory fails me right now) the return value of `ExecuteSqlCommand` is the number of rows affected, not the result set. You may need to look at using `SqlQuery<>` instead. – DavidG Oct 09 '14 at 15:39
  • This works in SQL directly SET NOCOUNT ON; INSERT INTO dbo.Table (field) VALUES ('test'); Select SCOPE_IDENTITY() as TESTID; SET NOCOUNT OFF; But not in EF using the commandm I'll try the other method, but I had thought that the command method was required by Inserts – JWP Oct 09 '14 at 15:48
0

Thanks to your suggestions, Step 1 put something like this in the query string as last select:

Select SCOPE_IDENTITY() as TESTID;

Then use this EF form of SQLQuery, the type of decimal is what is returned for the select example above.

using (db=new MyEntites())
{
   var identity = db.Database.SqlQuery<decimal>(query,parms}
}
JWP
  • 6,672
  • 3
  • 50
  • 74