Unable to Execute Stored Procedure from EF Core

Question

Unable to Execute Stored Procedure from EF Core

Kmcnet 1,006

Hello and thanks for the help in advance. I am trying to execute a sproc that copies one record from a staging table to a permanent table using the ID from the staging table as the parameter. I have verified the sproc works correctly using the SQL Server management console. But when I execute from EF, the sproc does not appear to execute and the record does not copy from one table to another. I have had similar issues executing sprocs from EF, but can't seem to get the hang of this. Here is my code:



                using (var ctxtest = new myDbContext())
                {
                    //  Copying 
                    var resp = ctxtest.tbl_Log_Registry_Staging.FromSqlRaw("Exec sp_CopyRegistryStagingToRegistry @ID", new SqlParameter("@ID", 58));                   

                }

The code does not seem to generate any error messages.  Any help would be appreciated.

Kmcnet 1,006 Reputation points

2023-02-13T22:22:02.3333333+00:00

To the forum moderator, I posted a similar question that was removed, and I do not want to violate any community standards, but am not sure why it was removed. Thank you for any input.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-14T01:46:34.93+00:00

@Kmcnet, Welcome to Microsoft Q&A, do you mean that this question was removed? After checking, I find the question is not removed. could you check it again?
Kmcnet 1,006 Reputation points

2023-02-14T01:55:43.6166667+00:00

Check my previous post entitled "Problems Executing sproc that Copies Record". States it was removed for violating community guidelines. Not sure what I did.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-14T02:01:33.42+00:00

@Kmcnet, I recommend that you could ask a new question here with the tag Microsoft Q&A. And you could ask why the question was removed and request for turning this question into a normal state.
Kmcnet 1,006 Reputation points

2023-02-14T02:48:31.5733333+00:00

Thanks for the response. I'm not sure how (or who) to ask why it was removed. In any event, the question I asked in this thread is one I am needing help with.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-14T02:52:39.0733333+00:00

@Kmcnet , so, do you mean that you still want to get a solutionin this question even if you have known the answer?
Kmcnet 1,006 Reputation points

2023-02-14T12:18:46.63+00:00

I do not know the answer, This was never ansswered,
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-15T02:17:04.1066667+00:00

@Kmcnet, based on your last question, there are two solutions should work for you.

First:

ctxtest.tbl_Log_Registry_Staging.FromSqlRaw("Exec sp_CopyRegistryStagingToRegistry @ID", new SqlParameter("@ID", 58));

Second:

context.Database.ExecuteSqlRaw(" @sp_CopyRegistryStagingToRegistry p0", parameters: new[] { 58});
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-21T05:37:06.1+00:00

@Kmcnet, is any update? Have you tried my solution?
Kmcnet 1,006 Reputation points

2023-02-22T02:42:40.9266667+00:00

Thanks for the response. FromRawSql does not run the query (an update). As I mentioned earlier, I cannot find ExecuteSqlRaw. Using EF core 6 and SQL Core 6
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-22T03:02:43.5466667+00:00

@Kmcnet, thanks for the feedback, If you already installed nuget-package Microsoft.EntityFrameworkCore.SqlServer, please check if you add the using statement in your project like the following:

If you used FromSqlRaw method, we need to add the statement using Microsoft.Data.SqlClient; to your project.
Kmcnet 1,006 Reputation points

2023-02-23T12:37:01.1533333+00:00

Thank you for the response. I decided to start a fresh test project to see if that made a difference. I installed Entity Framework Core 7.0.3 and Entity Framework Sql Server 7.0.3. The project is a fresh Asp.Net Core MVC with Asp.Net.Core.App and .NetCore.app dependencies. Using FromSqlRaw on multiple sprocs that perform update only functions fails to update. The controller has using Microsoft.AspNetCore.Mvc; using Microsoft.Data.SqlClient; and using Microsoft.EntityFrameworkCore. Other stored procedures work using FromRawSql for queries that return lists. I have verified all sprocs work correctly within SQL Server Management Console. The problem seems to be related to sprocs that only update a record.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-27T07:35:54.34+00:00

@Kmcnet, let me confirm your question again. Do you have any questions now after you tested it in the new project?

Other stored procedures work using FromRawSql for queries that return lists. I have verified all sprocs work correctly within SQL Server Management Console. The problem seems to be related to sprocs that only update a record.

I guess that your problem is that you could not use the ef core execute the stored procedure to update the record in the database, right?

If so, please tell me the design of the stored procedure. If not, please tell me what you are stuck in?
Kmcnet 1,006 Reputation points

2023-03-02T01:07:02.73+00:00

You are correct. In reality, there are actually two sprocs that do not execute correctly using EF. The first is an insert with a select statement that inserts the values from a select statement copying one record from the staging table to the production table. The only parameter is an ID that is passed to the sproc. I have confirmed the ID exists in the table and that the sproc actually works.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-03-02T08:00:44.8433333+00:00

@Kmcnet, thanks for the feedback, if possible, please provide the sql query about your stored procedure and the related table design? I will make a test and check where the problem is.

Kmcnet 1,006

The SQL:


	Update [myDb].[dbo].[tbl_Log_ImmunizationInventory] 
		SET RemainingDosesInLot = RemainingDosesInLot -1, DosesInLotUsed = DosesInLotUsed + 1, LastUpdated = GetDate() 
		WHERE ID = @InventoryID

The table:

CREATE TABLE [dbo].[tbl_Log_ImmunizationInventory](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TotalDosesInLot] [int] NULL,
	[DosesInLotUsed] [int] NULL,
	[RemainingDosesInLot] [int] NULL,
	[LastUpdated] [datetime] NULL
)

Kmcnet 1,006 Reputation points

2023-03-05T02:17:52.6966667+00:00

I have multiple sprocs like this, none of which work. They all basically accept some type of table ID and update one or multiple tables.

1 answer

Your answer

Kmcnet 1,006 Reputation points

2023-02-13T22:22:02.3333333+00:00

To the forum moderator, I posted a similar question that was removed, and I do not want to violate any community standards, but am not sure why it was removed. Thank you for any input.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-14T01:46:34.93+00:00

@Kmcnet, Welcome to Microsoft Q&A, do you mean that this question was removed? After checking, I find the question is not removed. could you check it again?
Kmcnet 1,006 Reputation points

2023-02-14T01:55:43.6166667+00:00

Check my previous post entitled "Problems Executing sproc that Copies Record". States it was removed for violating community guidelines. Not sure what I did.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-14T02:01:33.42+00:00

@Kmcnet, I recommend that you could ask a new question here with the tag Microsoft Q&A. And you could ask why the question was removed and request for turning this question into a normal state.
Kmcnet 1,006 Reputation points

2023-02-14T02:48:31.5733333+00:00

Thanks for the response. I'm not sure how (or who) to ask why it was removed. In any event, the question I asked in this thread is one I am needing help with.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-14T02:52:39.0733333+00:00

@Kmcnet , so, do you mean that you still want to get a solutionin this question even if you have known the answer?
Kmcnet 1,006 Reputation points

2023-02-14T12:18:46.63+00:00

I do not know the answer, This was never ansswered,
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-15T02:17:04.1066667+00:00

@Kmcnet, based on your last question, there are two solutions should work for you.

First:

ctxtest.tbl_Log_Registry_Staging.FromSqlRaw("Exec sp_CopyRegistryStagingToRegistry @ID", new SqlParameter("@ID", 58));

Second:

context.Database.ExecuteSqlRaw(" @sp_CopyRegistryStagingToRegistry p0", parameters: new[] { 58});
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-21T05:37:06.1+00:00

@Kmcnet, is any update? Have you tried my solution?
Kmcnet 1,006 Reputation points

2023-02-22T02:42:40.9266667+00:00

Thanks for the response. FromRawSql does not run the query (an update). As I mentioned earlier, I cannot find ExecuteSqlRaw. Using EF core 6 and SQL Core 6
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-22T03:02:43.5466667+00:00

@Kmcnet, thanks for the feedback, If you already installed nuget-package Microsoft.EntityFrameworkCore.SqlServer, please check if you add the using statement in your project like the following:

If you used FromSqlRaw method, we need to add the statement using Microsoft.Data.SqlClient; to your project.
Kmcnet 1,006 Reputation points

2023-02-23T12:37:01.1533333+00:00

Thank you for the response. I decided to start a fresh test project to see if that made a difference. I installed Entity Framework Core 7.0.3 and Entity Framework Sql Server 7.0.3. The project is a fresh Asp.Net Core MVC with Asp.Net.Core.App and .NetCore.app dependencies. Using FromSqlRaw on multiple sprocs that perform update only functions fails to update. The controller has using Microsoft.AspNetCore.Mvc; using Microsoft.Data.SqlClient; and using Microsoft.EntityFrameworkCore. Other stored procedures work using FromRawSql for queries that return lists. I have verified all sprocs work correctly within SQL Server Management Console. The problem seems to be related to sprocs that only update a record.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-02-27T07:35:54.34+00:00

@Kmcnet, let me confirm your question again. Do you have any questions now after you tested it in the new project?

Other stored procedures work using FromRawSql for queries that return lists. I have verified all sprocs work correctly within SQL Server Management Console. The problem seems to be related to sprocs that only update a record.

I guess that your problem is that you could not use the ef core execute the stored procedure to update the record in the database, right?

If so, please tell me the design of the stored procedure. If not, please tell me what you are stuck in?
Kmcnet 1,006 Reputation points

2023-03-02T01:07:02.73+00:00

You are correct. In reality, there are actually two sprocs that do not execute correctly using EF. The first is an insert with a select statement that inserts the values from a select statement copying one record from the staging table to the production table. The only parameter is an ID that is passed to the sproc. I have confirmed the ID exists in the table and that the sproc actually works.
Jack J Jun 24,881 Reputation points Microsoft External Staff

2023-03-02T08:00:44.8433333+00:00

@Kmcnet, thanks for the feedback, if possible, please provide the sql query about your stored procedure and the related table design? I will make a test and check where the problem is.
Kmcnet 1,006 Reputation points

2023-03-05T02:15:31.62+00:00

The SQL:

Update [myDb].[dbo].[tbl_Log_ImmunizationInventory] SET RemainingDosesInLot = RemainingDosesInLot -1, DosesInLotUsed = DosesInLotUsed + 1, LastUpdated = GetDate() WHERE ID = @InventoryID The table: CREATE TABLE [dbo].[tbl_Log_ImmunizationInventory]( [ID] [int] IDENTITY(1,1) NOT NULL, [TotalDosesInLot] [int] NULL, [DosesInLotUsed] [int] NULL, [RemainingDosesInLot] [int] NULL, [LastUpdated] [datetime] NULL )
Kmcnet 1,006 Reputation points

2023-03-05T02:17:52.6966667+00:00

I have multiple sprocs like this, none of which work. They all basically accept some type of table ID and update one or multiple tables.

Answer 1

SCRIBE Patrice 41

Hi,

I would start by using a breakpoint to make sure this code is called and check if the expected entity is returned.

Also as the id is hardcoded, what if you run this twice or if the id doesn't exists ? Sometimes it can be that you run/check agaisnt the wrong database or that your code hide exceptions.

Finally I helped once someone wo deleted test records after his insert statement.

In short make first sure that it really fails rather than it actually runs under unexcepted circumstances.

Share via

Unable to Execute Stored Procedure from EF Core

1 answer

Your answer