EFCore DbContext扩展执行原生SQL查询对象集合

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using System.Data;
using System.Data.Common;

namespace JOC.EFCore
{

    /// <summary>
    /// 扩展
    /// </summary>
    public static class CustomDbContextExtensions
    {
        /// <summary>
        /// 执行 SQL 查询并返回自定义对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> ExecuteSqlQuery<T>(this DbContext context, string sql, params object[] parameters) where T : class
        {
            var transaction = context.Database.CurrentTransaction;
            return ExecuteSqlQueryWithTransaction<T>(context, sql, transaction, parameters);
        }

        /// <summary>
        /// 执行 SQL 查询并返回单个自定义对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static T? ExecuteSqlQuerySingle<T>(this DbContext context, string sql, params object[] parameters) where T : class
        {
            var transaction = context.Database.CurrentTransaction;
            return ExecuteSqlQuerySingleWithTransaction<T>(context, sql, transaction, parameters);
        }

        // 执行 SQL 查询并返回自定义对象列表（带事务）
        private static List<T> ExecuteSqlQueryWithTransaction<T>(DbContext context, string sql, IDbContextTransaction? transaction, params object[] parameters) where T : class
        {
            var connection = context.Database.GetDbConnection();
            List<T> results = new List<T>();

            try
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = sql;
                if (parameters.Length > 0)
                    command.Parameters.AddRange(parameters);
                command.Transaction = transaction?.GetDbTransaction();

                var flags = System.Reflection.BindingFlags.Public
                       | System.Reflection.BindingFlags.Instance
                       | System.Reflection.BindingFlags.IgnoreCase;
                var properties = typeof(T).GetProperties(flags);

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var entity = Activator.CreateInstance<T>();

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            string name = reader.GetName(i);
                            var p = properties.Where(w => w.Name.Equals(name, StringComparison.CurrentCultureIgnoreCase)).FirstOrDefault();
                            if (p != null)
                            {

                                p.SetValue(entity, CheckType(reader.GetValue(i), p.PropertyType), null);
                            }
                        }

                        results.Add(entity);
                    }
                }
            }
            finally
            {
                connection.Close();
            }

            return results;
        }

        // 执行 SQL 查询并返回单个自定义对象（带事务）
        private static T? ExecuteSqlQuerySingleWithTransaction<T>(DbContext context, string sql, IDbContextTransaction? transaction, params object[] parameters) where T : class
        {
            var results = ExecuteSqlQueryWithTransaction<T>(context, sql, transaction, parameters);
            return results.FirstOrDefault();
        }

        // <summary>
        /// 对可空类型进行判断转换(*要不然会报错)
        /// </summary>
        /// <param name="value">DataReader字段的值</param>
        /// <param name="conversionType">该字段的类型</param>
        /// <returns></returns>
        private static object? CheckType(object value, Type conversionType)
        {
            if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                if (value == null || value == DBNull.Value)
                    return null;
                System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
                conversionType = nullableConverter.UnderlyingType;
            }
            return Convert.ChangeType(value, conversionType);
        }

    }

}