Executing a update/insert stored procedure with entity framework 4.1?

Question

I tried to call a stored procedure from entity framework 4.1 (mvc 3 web application). Its not throwing an exception but the insert/update is not happening? When I manually executed the stored proc it works. ``` CREATE PROCEDURE [dbo].[SP_AddUpdateResponse] @QuestionID int, @UserID int, @AnswerValue nvarchar(2000), @ReviewID int AS MERGE [dbo].[Responses] AS [Target] USING (SELECT @QuestionID, @UserID, @AnswerValue, @ReviewID) AS [Source] ([QuestionID], [UserID], [AnswerValue], [ReviewID] ) ON [Target].[QuestionID] = [Source].[QuestionID] AND [Target].[ReviewID] = [Source].[ReviewID] WHEN MATCHED THEN UPDATE SET [AnswerValue] = [Source].[AnswerValue] WHEN NOT MATCHED THEN INSERT ( [QuestionID], [UserID], [AnswerValue], [ReviewID] ) VALUES ( [Source].[QuestionID], [Source].[UserID], [Source].[AnswerValue], [Source].[ReviewID] ); ``` Code that calls the procedure: ``` using System.Data.SqlClient; using System.Data.Metadata.Edm; using (var db = new NexGenContext()) { foreach (var key in f…

Accepted Answer

try to use `ExecuteSqlCommand` instead of `SqlQuery`

```
db.Database.ExecuteSqlCommand(
                            "EXEC SP_AddUpdateResponse @QuestionID, @UserID, @AnswerValue, @ReviewID",
                            new SqlParameter("@QuestionID", questionId),
                            new SqlParameter("@UserID", 9999),
                            new SqlParameter("@AnswerValue", answer),
                            new SqlParameter("@ReviewID", id)
                        );
```

In description of the `SqlQuery` method said: "Use method to return entities that are tracked by the context", so you can use it only for `SELECT` queries.

Executing a update/insert stored procedure with entity framework 4.1?

Solution

Problem