EF Core 3.1에서 선택적 반환 열을 사용하여 저장 프로 시저 실행

내 자신의 확장 클래스를 작성했습니다.

    public static class DataModelExtensions
    {
        public static DbCommand LoadStoredProc(
          this DbContext context, string storedProcName)
        {
            var cmd = context.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = storedProcName;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            return cmd;
        }

        public static DbCommand LoadSql(this DbContext context, string sql)
        {
            var cmd = context.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            return cmd;
        }

        public static DbCommand WithSqlParam(this DbCommand cmd, string paramName, object paramValue, ParameterDirection direction = ParameterDirection.Input, int ?size = null)
        {
            if (string.IsNullOrEmpty(cmd.CommandText))
                throw new InvalidOperationException(
                  "Call LoadStoredProc before using this method");
            var param = cmd.CreateParameter();
            param.ParameterName = paramName;
            param.Value = paramValue;
            param.Direction = direction;
            if (size.HasValue)
                param.Size = size.Value;
            cmd.Parameters.Add(param);
            return cmd;
        }

        private static List<T> MapToList<T>(this DbDataReader dr, int? errorCode, string errorText)
        {
            var objList = new List<T>();
            var props = typeof(T).GetRuntimeProperties();

            var colMapping = dr.GetColumnSchema()
              .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
              .ToDictionary(key => key.ColumnName.ToLower());

            int rowNumber = 1;

            T obj = Activator.CreateInstance<T>();
            
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    obj = Activator.CreateInstance<T>();
                    foreach (var prop in props)
                    {
                        if (colMapping.ContainsKey(prop.Name.ToLower()))
                        {
                            object val;
                            var propName = prop.Name.ToLower();
                            val = dr.GetValue(colMapping[propName].ColumnOrdinal.Value);
                            prop.SetValue(obj, val == DBNull.Value ? null : val);
                        }
                    }
                    rowNumber++;
                    objList.Add(obj);
                }
            }
            else
            {
                foreach (var prop in props)
                {
                    var propName = prop.Name;
                    if (propName == "ErrorCode")
                        prop.SetValue(obj, errorCode);
                    else if (propName == "ErrorText")
                        prop.SetValue(obj, errorText);
                }
                objList.Add(obj);
            }
            return objList;
        }

        public static async Task<List<T>> Execute<T>(this DbCommand command)
        {
            using (command)
            {
                //System.Diagnostics.Debug.WriteLine(command.ToLog());
                if (command.Connection.State == System.Data.ConnectionState.Closed)
                    command.Connection.Open();
                try
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        return reader.MapToList<T>((int?)command.Parameters["@ErrorCode"].Value, (string)command.Parameters["@ErrorText"].Value);
                    }
                }
                catch (Exception e)
                {
                    throw (e);
                }
                finally
                {
                    command.Connection.Close();
                }
            }
        }

        public static async Task<bool> HasRows(this DbCommand command)
        {
            using (command)
            {
                if (command.Connection.State == System.Data.ConnectionState.Closed)
                    command.Connection.Open();
                try
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        return reader.HasRows;
                    }
                }
                catch (Exception e)
                {
                    throw (e);
                }
                finally
                {
                    command.Connection.Close();
                }
            }
        }

        public static async Task<bool> QueryHasRows(this DbContext context, string sql)
        {
            return await context.LoadSql(sql).HasRows();
        }

        public static string ToLog(this DbCommand command)
        {
            //-- @p5: Input Int (Size = -1; Prec = 0; Scale = 0) [194]
            StringBuilder sb = new StringBuilder();
            sb.AppendLine();
            sb.AppendLine(command.CommandText);
            foreach(DbParameter parameter in command.Parameters)
            {
                if(parameter.Value != null && !string.IsNullOrEmpty(parameter.Value.ToString()))
                {
                    sb.AppendLine($"{parameter.ParameterName}={parameter.Value}");
                    //sb.AppendLine($"-- {parameter.ParameterName}: {parameter.Direction} {parameter.DbType} (Size = {parameter.Size}; Prec = {parameter.Precision}; Scale = {parameter.Scale}) [{parameter.Value}]");
                }
            }
            return sb.ToString();
        }
    }

사용 예 :

        public async Task<string> UserSessionGet(string user, string password, string ip)
        {
            //SessionItem lst = null;
            IList<SessionItem> lst = null;
            try
            {
                int errorCode = 0;
                string errorText = string.Empty;

                List<SessionItem> result = new List<SessionItem>();
                result = await this.LoadStoredProc("UserSessionGet")
                .WithSqlParam("@UserLogin", user)
                .WithSqlParam("@UserPassword", password)
                .WithSqlParam("@IP", ip)
                .WithSqlParam("@ErrorCode", errorCode, ParameterDirection.Output)
                .WithSqlParam("@ErrorText", errorText, ParameterDirection.Output, 255)
                .Execute<SessionItem>();
                if (result.Count == 1)
                {
                    return result[0].Session;
                }
                //return result.FirstOrDefault(x => x.iscurrent.ToLower() == "да");
                return string.Empty;
            }
            catch (Exception ex)
            {
                throw ex;
            }

       }