DbContextExtensions.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace Dal
{
    public static class DbContextExtensions
    {
        public static async Task<IEnumerable<T>> FromSqlCommand<T>(this DbContext context, string procName, params SqlParameter[] parameters) where T : new()
        {

            var conn = (SqlConnection)context.Database.GetDbConnection();
            var command = new SqlCommand(procName, conn) { CommandType = CommandType.StoredProcedure };

            if (parameters != null && parameters.Any())
            {
                command.Parameters.AddRange(parameters);
            }
            IEnumerable<T> results;
            {
                conn.Open();
                results = await (await command.ExecuteReaderAsync()).MapResults<T>();
                conn.Close();
            }
            
            return results;


        }
        private static async Task<IEnumerable<T>> MapResults<T>(this SqlDataReader reader) where T: new()
        {
            var results = new List<T>();
            while (await reader.ReadAsync())
            {
                var result = new T();
                foreach (var prop in typeof(T).GetProperties())
                {
                    try
                    {
                        if (!prop.CanWrite || !reader.HasColumn(prop.Name))
                        {
                            continue;
                        }
                        switch (prop.PropertyType.Name)
                        {
                            case "string":
                                prop.SetValue(result, reader[prop.Name] is DBNull ? null : (string)reader[prop.Name]);
                                break;
                            case "int":
                                prop.SetValue(result, reader[prop.Name] is DBNull ? null : (int?)int.Parse(reader[prop.Name].ToString()));
                                break;
                            case "decimal":
                                prop.SetValue(result, reader[prop.Name] is DBNull ? null : (decimal?)decimal.Parse(reader[prop.Name].ToString()));
                                break;
                            case "bool":
                                prop.SetValue(result, reader[prop.Name] is DBNull ? null : (bool?)bool.Parse(reader[prop.Name].ToString()));
                                break;
                            case "DateTime":
                                prop.SetValue(result, reader[prop.Name] is DBNull ? null : (DateTime?)DateTime.Parse(reader[prop.Name].ToString()));
                                break;
                            default:
                                prop.SetValue(result, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
                                break;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception($"Unable to map property {prop.Name}.", ex.InnerException);
                    }
                }
                results.Add(result);
            }
            return results;
        }

        private static bool HasColumn(this IDataRecord dr, string columnName)
        {
            for (var i = 0; i < dr.FieldCount; i++)
            {
                if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                    return true;
            }
            return false;
        }
        
    }
}