Entity Framework Core 执行储存过程

一、介绍

    Entity Framework可以自动生成SQL查询，避免用户编写与数据库过于耦合的代码。但存在某些特殊的情况，需要用户手动创建SQL去查询。EF Core提供了两个API可供用户执行手动创建的SQL查询。

1、DbSet.FromSql

    使用DbSet.FromSql需返回一个DbSet的实体类型，且会自动跟踪数据库上下文   

2、Database.ExecuteSqlCommand

    使用Database.ExecuteSqlCommand可以返回一个非实体类型，且不会跟踪数据库上下文

二、实例

    本文使用Database.ExecuteSqlCommand实现储存过程的执行，可通过以下代码直接调用

var result = await _context.GetListBySqlAsync<ProcTestClass>(
                "Exec proc_SG_rs_TestParamerYield_Cube_Dynamic_EX_CCI");

    public static class DbContextExtensions
    {
        /// <summary>
        /// 执行SQL，将结果转成DataTable
        /// </summary>
        /// <param name="context">DbContext</param>
        /// <param name="sql">执行的SQL</param>
        /// <returns></returns>
        public static async Task<DataTable> GetDataTableBySqlAsync(this DbContext context, string sql)
        {
            var dt = new DataTable();
            if (sql.IsEmpty()) return dt;
            //获取数据库连接
            var connection = context.Database.GetDbConnection();
            await using (var cmd = connection.CreateCommand())
            {
                if (connection.State != ConnectionState.Open)
                    await context.Database.OpenConnectionAsync();
                cmd.CommandTimeout = 36000;
                cmd.Connection = connection;
                cmd.CommandText = sql;
                //执行查询
                var dr = await cmd.ExecuteReaderAsync();
                //查询结果转成DataTable
                dt.Load(dr);
                await dr.DisposeAsync();
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL，将结果转成List集合
        /// </summary>
        /// <typeparam name="T">List集合的类型</typeparam>
        /// <param name="context">DbContext</param>
        /// <param name="sql">执行的SQL</param>
        /// <returns></returns>
        public static async Task<List<T>> GetListBySqlAsync<T>(this DbContext context, string sql)
        {
            //实体类型
            var entityType = typeof(T);
            //实体集合的类型
            var lstEntityType = typeof(List<>).MakeGenericType(entityType);
            //创建实体集合的实例
            var lstEntity = Activator.CreateInstance<List<T>>();
            if (sql.IsEmpty()) return lstEntity;
            //获取数据库连接
            var connection = context.Database.GetDbConnection();
            await using (var cmd = connection.CreateCommand())
            {
                if (connection.State != ConnectionState.Open)
                    await context.Database.OpenConnectionAsync();
                cmd.CommandTimeout = 36000;
                cmd.Connection = connection;
                cmd.CommandText = sql;
                //执行查询
                var dr = await cmd.ExecuteReaderAsync();
                //获取查询结果的列信息
                var columnSchema = dr.GetColumnSchema();

                //获取List集合的Add方法
                var addMethod = lstEntityType.GetMethod("Add");
                while (await dr.ReadAsync())
                {
                    //创建单个实体的实例
                    var instance = Activator.CreateInstance<T>();
                    //遍历，通过反射技术将值赋予实体中
                    foreach (var column in columnSchema)
                    {
                        if (column.ColumnOrdinal.HasValue)
                        {
                            var value = dr.GetValue(column.ColumnOrdinal.Value);

                            var property = entityType.GetProperty(column.ColumnName);
                            if (property != null)
                            {
                                property.SetValue(instance, Convert.ChangeType(value, property.PropertyType));
                            }
                        }
                    }
                    //将实例添加到集合中
                    addMethod?.Invoke(lstEntity, new object[] { instance });
                }

                await dr.DisposeAsync();
            }
            return lstEntity;
        }
    }