question

JamesBound-0429 avatar image
0 Votes"
JamesBound-0429 asked DanielZhang-MSFT commented

EntityFrameworkCore 5.05 ExecuteSqlRaw fail multiple update

I try to Execute a stored procedure from , if I execute it directly from SSMS, it works as expected, but if I use it from ExecuteSqlRaw or ExecuteSqlRawAsync, it fails as soon as it hits the first SELECT COUNT(), no error, but the second UPDATE never run.

An example of the stored procedure:

 ALTER PROCEDURE [fact].[SetJobDoneByProcess]
      @JobId int
     ,@JobProcId int
     ,@JobUserId smallint
     ,@Done bit
        
 AS
 BEGIN
    
     DECLARE @procCnt int = 0 
            ,@procCntDone int = 0
            ,@CompletedDtTm datetime = NULL;
    
     --Update this process status
     IF @Done = 1
     BEGIN
         SET @CompletedDtTm = GETDATE();
     END
    
     UPDATE fact.Jobs_Processes
     SET CompletedDtTm = @CompletedDtTm, JobUserId = @JobUserId
     WHERE JobProcId = @JobProcId
    
     -- Compare job processes finished
     SET @CompletedDtTm = NULL;
     SET @procCnt = (SELECT COUNT(JobProcId) FROM fact.Jobs_Processes WHERE JobId = @JobId)
     SET @procCntDone = (SELECT COUNT(JobProcId) FROM fact.Jobs_Processes WHERE JobId = @JobId AND NOT CompletedDtTm IS NULL)
    
     -- Update job
     IF @procCnt = @procCntDone
     BEGIN
         SET @CompletedDtTm = GETDATE();
         UPDATE fact.Jobs
         SET CompletedDtTm = @CompletedDtTm
         WHERE JobId = @JobId
     END
     ELSE
     BEGIN
         UPDATE fact.Jobs
         SET StartedDtTm = GETDATE()
         WHERE JobId = @JobId
         AND StartedDtTm IS NULL
     END
    
 END


dotnet-entity-framework-core
· 10
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JamesBound-0429,
Please provide codes about your ExecuteSqlRaw or ExecuteSqlRawAsync method, and you need to note that in order to avoid SQL injection attacks, always use parameterization to send values separate from the SQL text.
More details please refer to this document.
Best Regards,
Daniel Zhang


0 Votes 0 ·

Here it is... I think it should be in the rules of art...

   var parameters = new[]
     {
        new SqlParameter("@JobId", SqlDbType.Int)
         {
           Direction = ParameterDirection.Input,
           Value = JobProcId
         },
        new SqlParameter("@JobProcId", SqlDbType.Int)
         {
           Direction = ParameterDirection.Input,
           Value = JobProcId
         },
        new SqlParameter("@JobUserId", SqlDbType.SmallInt)
         {
           Direction = ParameterDirection.Input,
           Value = UserId
         },
        new SqlParameter("@Done", SqlDbType.Bit)
         {
           Direction = ParameterDirection.Input,
           Value = Done
         },
     };
        
     ctx.Database.ExecuteSqlRaw("exec fact.SetJobDoneByProcess @JobId, @JobProcId, @JobUserId, @Done", parameters:parameters);


0 Votes 0 ·

Here is the call in the C#, in the rules of art...

var parameters = new[]
{
new SqlParameter("@JobId", SqlDbType.Int)
{
Direction = ParameterDirection.Input,
Value = JobProcId
},
new SqlParameter("@JobProcId", SqlDbType.Int)
{
Direction = ParameterDirection.Input,
Value = JobProcId
},
new SqlParameter("@JobUserId", SqlDbType.SmallInt)
{
Direction = ParameterDirection.Input,
Value = UserId
},
new SqlParameter("@Done", SqlDbType.Bit)
{
Direction = ParameterDirection.Input,
Value = Done
},
};

  ctx.Database.ExecuteSqlRaw("exec fact.SetJobDoneByProcess @JobId, @JobProcId, @JobUserId, @Done", parameters:parameters);


0 Votes 0 ·

Hi @JamesBound-0429,
>>SET @procCnt = (SELECT COUNT(JobProcId) FROM fact.Jobs_Processes WHERE JobId = @JobId)
>>SET @procCntDone = (SELECT COUNT(JobProcId) FROM fact.Jobs_Processes WHERE JobId = @JobId AND NOT CompletedDtTm IS NULL)
If you change the SELECT COUNT() statement to a constant, can it run successfully?
Best Regards,
Daniel Zhang




0 Votes 0 ·
Show more comments

0 Answers