Problems Executing Stored Procedure with Entity Framework

Question

Hello everyone and thanks for the help in advance. Developing an application the executes a stored procedure from MVC page. The sproc simply reduces the available inventory of an item by 1 and increases the number used by 1. I have tested the sproc separately and it works fine. However, when I execute from EF, it fails. Here is the code:

            using (var ctx = new mDbContext())
            {
                var increment = ctx.tbl_Log_Inventory.FromSqlRaw("Exec sp_CountsByInventoryID @InventoryID", new SqlParameter("@InventoryID", 123456));
            }

Seems very simple, but doesn't work. Any help would be appreciated.

Answer 1

@Kmcnet, Welcome to Microsoft Q&A, if you want to update the database through the stored procedure, I recommend that you could use the context.Database.ExecuteSqlRaw Method instead of FromSqlRaw method. As usual, FromSqlRaw method is used to create a LINQ query based on a raw SQL query, which is also used to query data.

Here is my code example and you could have a look.

Stored Procedure:

CREATE PROCEDURE [dbo].[TestProcedure]
	@Name nvarchar(20)
AS
	Update products set Description='This is a test' where Name=@Name
RETURN 0

c# code:

            MyContext context = new MyContext();
            context.Database.ExecuteSqlRaw("TestProcedure @p0", parameters: new[] { "test1"});

Tested result:

Hope my solution could help you.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.