How to pass multiple parameters to stored procedure on .net core 3.1

Question

I wrote a function years ago, and works perfectly until migrate to .NET Core 3.1. Now parameters has not pass to SQL Server. My function is:

public async Task<IList<string>> GetTree(GetPayload payload)
{
    var cx = this.uow.Repository<Organization>().Context;

    var parameters = new List<SqlParameter>();

    if (string.IsNullOrEmpty(payload.Code) == false)
        parameters.Add(new SqlParameter("@code", payload.Code));

    if (payload.ParentId.IsNullOrEmpty() == false)
        parameters.Add(new SqlParameter("@parentId", payload.ParentId));
    
    var orgRes = await cx.Organization.FromSqlRaw("SP_GetOrganization", parameters.ToArray()).ToListAsync();
  
    return orgRes
}

When debug this code, parameters succesfully come to API. But when SQL call with 'FromSqlRaw', parameters as shown '0'. Why?

Here is my stored procedure:

DECLARE @RC int
DECLARE @code nvarchar(50)
DECLARE @parentId uniqueidentifier
DECLARE @perId uniqueidentifier
DECLARE @isDeleted bit

-- TODO: Set parameter values here.

EXECUTE @RC = [dbo].[SP_GetOrgTree] 
   @code
  ,@parentId
  ,@perId
  ,@isDeleted
GO

In SQL, when edit code like this: procedure is run with no problem. How can I fix this in EF Core?

DECLARE @RC int
    DECLARE @code nvarchar(50)
    DECLARE @parentId uniqueidentifier
    DECLARE @perId uniqueidentifier
    DECLARE @isDeleted bit
    
    SET @code = 'sample code'
    
    EXECUTE @RC = [dbo].[SP_GetOrgTree] 
       @code
      ,@parentId
      ,@perId
      ,@isDeleted
    GO

Answer 1

Finally solve my problem. Big thanks guys. Here is the answer:

If one parameters can be null, check like this:

if (string.IsNullOrEmpty(payload.Code) == false)
    parameters.Add(new SqlParameter("@code", payload.Code));
else
    parameters.Add(new SqlParameter("@code", DBNull.Value));

and call procedure like this:

var orgRes = await cx.PdOrganizationTreeQuery
                   .FromSqlRaw("SP_Organization @code,@parentId,@perId,@isDeleted", parameters: parameters.ToArray())
                   .ToListAsync();

Note: all parameters MUST write at FromSqlRaw(). Because SQL Server expect those parameters.

Thanks to Sowmyadhar Gourishetty and Panagiotis Kanavos

Answer 2

When you execute the below code the values for @parentId, @perId, @isDeleted will be passed as NULL. You have to do the same from the c# when you call the SPROC.

DECLARE @RC int
DECLARE @code nvarchar(50)
DECLARE @parentId uniqueidentifier
DECLARE @perId uniqueidentifier
DECLARE @isDeleted bit

SET @code = 'sample code'

EXECUTE @RC = [dbo].[SP_GetOrgTree] 
   @code
  ,@parentId
  ,@perId
  ,@isDeleted
GO

Please see the below code to pass the NULL values. I'm modifying the same code. Please do the same for the other two parameters.

public async Task<IList<string>> GetTree(GetPayload payload)
{
    var cx = this.uow.Repository<Organization>().Context;

    var parameters = new List<SqlParameter>();

    if (string.IsNullOrEmpty(payload.Code) == false)
        parameters.Add(new SqlParameter("@code", payload.Code));
    else
        parameters.Add(new SqlParameter("@code", DBNull.Value)); // Modified the code here

    if (payload.ParentId.IsNullOrEmpty() == false)
        parameters.Add(new SqlParameter("@parentId", payload.ParentId));
    else
        parameters.Add(new SqlParameter("@parentId", DBNull.Value)); // Modified the code here

    // Do the same for @perId, @isDeleted parameters. 
    // If they don't exist in your payload, just pass them as NULL directly

    // parameters.Add(new SqlParameter("@perId", DBNull.Value));
    // parameters.Add(new SqlParameter("@isDeleted", DBNull.Value));

    var orgRes = await cx.Organization.FromSqlRaw("SP_GetOrganization", parameters.ToArray()).ToListAsync();

    return orgRes;
}