Exception Thrown from SQL Server Stored Procedure aren't capturing in Entity Framework

Question

Prior to writing this post, I had read most of the queries submitted by users earlier on similar topics but didn't have a conclusive answer.

Groundwork I did was the following:

1. There were lots of posts on Stackoverflow and some said the following: 1.1. Swapped from THROW to RAISEERROR; but no luck 1.2. Some said if using RAISEERROR I have to mention the severity to more than 10; I did that but no luck
2. I changed the Code to this but no avail:

   var result = 0;
   SqlCommand cmd = null;
   SqlConnection conx = null;
   
   try
   {
       conx = (SqlConnection)_dbContext.Database.GetDbConnection();
       cmd = new SqlCommand();
   
       cmd.Connection = conx;
       cmd.CommandText = "[dbo].[usp_MyStoredProc]";
       cmd.CommandType = System.Data.CommandType.StoredProcedure;
       cmd.Parameters.AddRange(param);
       conx.Open();
       result = cmd.ExecuteNonQuery();
       conx.Close();
   }
   catch (Exception er)
   {
       _logger.LogInformation(er.Message, er);
   
   }
   finally {
       if (conx != null)
       {
           conx.Dispose();
       }
   }
   

3. Some said to avoid handling exceptions in the Stored Procedure; I couldn't do that as I already use another asp.net .net framework 4.8 MVC web app works well with this proc and throws the exception when appropriate.

All I need is to prompt the Exception thrown from the Stored Procedure. How can I do this?

So I have a Stored Procedure on an Azure Sql Server which looks much similar to this:

ALTER PROCEDURE [dbo].[usp_MyStoredProc]
    @param1 int output,
    @param2 varchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    
    Declare @errorNo as int = 0
    Declare @errorMsg as nvarchar(1024)
    Declare @errorServerity int=0 
    
    BEGIN TRY
        If Validation1 != 'SUCCESS'
        begin
            set @errorMsg = ('Validation 1 Failed' );
            THROW 50000,@errorMsg,1
        end;
        
        If Validation2 != 'SUCCESS'
        begin
            set @errorMsg = ('Validation 2 Failed' );
            THROW 50000,@errorMsg,1
        end;
        
        //DO SOME BUSINESS WORK
        
    END TRY
    
    BEGIN CATCH
        --Any Business Logic Failure will be captured here and will be thrown out again as an Exception
        SELECT @errorNo = ERROR_NUMBER();
        SELECT @errorMsg = ERROR_MESSAGE(); 
        SELECT @errorServerity = ERROR_SEVERITY();

        IF @errorNo = 2627
            SET @errorMsg = 'Can NOT insert Duplicate Request. A Request of same kind exists already for : ' 
                            + @pAccountID;  
        
        --We Tried  'THROW' but no exception is thrown
        --THROW 50000, @errorMsg , 1;
        --We Tried 'RAISERROR' but no exception is thrown
        RAISERROR(@errorMsg,15,1);
    END CATCH
END;

and I am calling the above-Stored Procedure in an ASP.NET Core running Entity Framework on top of .NET Core 7:

public async Task<int> AddAsync(RequestModel request, string subject)
{
            var param = new SqlParameter[] {
                new SqlParameter() {
                            ParameterName = "@param1",
                            SqlDbType =  System.Data.SqlDbType.Int,
                            Direction = System.Data.ParameterDirection.Output
                        },

                new SqlParameter() {
                            ParameterName = "@param2",
                            SqlDbType =  System.Data.SqlDbType.VarChar,
                            Value = "Hello"
                        }
            };
            
            var result = await _dbContext.Database.ExecuteSqlRawAsync("EXEC [dbo].[usp_MyStoredProc] @param1, @param2", param);//<--User defined Error Messages aren't thrown here
            
            return result;
            
}

However, If I run the following SQL Statement which captured from the Profiler in Azure Data Studio, I am getting the Error like this:

declare @p3 int
set @p3=NULL
exec sp_executesql N'EXEC [dbo].[usp_MyStoredProc] @param1, @param2',N'@param1 int output,@param2 varchar(100),',@param1=@p3 output,@param2='Hello'

select @p3

and I am getting this:

Msg 50000, Level 14, State 1, Procedure dbo.usp_MyStoredProc, Line 21 Can NOT insert Duplicate Request. A Request of same kind exists already for : Hello (1 row affected) Total execution time: 00:00:00.033