EF Core下 怎么跑sql语句

    兴致来了，多写一篇吧。 

    所有转netcore的小伙伴们都发现了： ef core跟以前的ef差距非常大，view（视图）无法通过dbfirst生成了，存储过程也一样（虽然我现在开始转codefirst了）。  然而，如果真的想直接执行sql语句怎么办？  我们发现context下的Database属性跟以前也不一样了，只能做些事务操作，没有执行sql了。可以执行sql的变成了每张具体的表（DbSet<T>）下面的FromSql方法了（需要显式引用Microsoft.EntityFrameworkCore命名空间）。 但是这个方法存在问题，只能返回该表类型的结果，无法返回任意类型。

    so，跟大家一样，我去网上搜搜解决方案。查到了一个方案。

    然而，为了响应ef本身跨数据库种类的设计要求，我觉得应该做一个可以自行根据数据库类型判断需要执行什么语句的组件，所以我给我们亲爱的dbcontext写了一个扩展：

    首先请自觉nuget拉一下包 Microsoft.EntityFrameworkCore.Relational。

    整个扩展的大体思路是： 针对一次查询，将各种数据库的查询语句封装到一起，在运行时自动判断当前连接的数据库环境，执行相应的语句。这样不破坏依赖注入的原则，需要的是编程人员根据可能连接的数据库种类，写对应库的查询语句。

    另外这里还用了一个神奇的技术（我自己早就在使用了），以方便查询参数的操作。就是使用匿名类型来提供查询语句参数，如"select * from abc where id between @start and @end"语句，只需要提供参数 new {start = 1, end=100} 作为参数传入即可。不用再去new 一个parameter，每次填一堆。

 

    废话够多了，直接上代码了：

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;

namespace SS.Data.EntityFramework.Core
{
    /// <summary>
    /// 数据库查询语句
    /// </summary>
    public class DbContextSqlQueryCommand
    {
        /// <summary>
        /// 使用不含参数的查询语句
        /// </summary>
        /// <param name="query"></param>
        public DbContextSqlQueryCommand(string query)
        {
            Query = query;
        }
        /// <summary>
        /// 使用包含参数的查询语句
        /// </summary>
        /// <param name="query"></param>
        public DbContextSqlQueryCommand(string query, object @params)
        {
            Query = query;
            Parameters = @params;
        }
        /// <summary>
        /// 查询语句
        /// </summary>
        public string Query { get; set; }
        /// <summary>
        /// 参数
        /// </summary>
        public object Parameters { get; set; }
    }

    /// <summary>
    /// 数据库查询语句集合
    /// </summary>
    public class DbContextSqlQueryCommands
    {
        /// <summary>
        /// 数据库为SqlServer时使用的查询语句
        /// </summary>
        public DbContextSqlQueryCommand Sql { get; set; }
        /// <summary>
        /// 数据库为MySql时使用的查询语句
        /// </summary>
        public DbContextSqlQueryCommand MySql { get; set; }
        /// <summary>
        /// 数据库为InMemory时使用的查询语句
        /// </summary>
        public DbContextSqlQueryCommand InMemory { get; set; }
        /// <summary>
        /// 数据库为Sqlite时使用的查询语句
        /// </summary>
        public DbContextSqlQueryCommand Sqlite { get; set; }
    }

    /// <summary>
    /// 数据库类型
    /// </summary>
    public enum DbContextType
    {
        InMemory = 0,
        SqlServer = 1,
        MySql = 2,
        Sqlite = 3,
    }

    /// <summary>
    /// EF上下文扩展
    /// </summary>
    public static class DbContextExtensions
    {
        //拼接参数
        private static void combineParams(DbContextType type, ref DbCommand command, object @params = null)
        {
            if (@params != null)
            {
                Type paramType;
                string prefix;
                switch (type)
                {
                    case DbContextType.InMemory:
                        throw new Exception("未实现的数据库类型");
                    case DbContextType.SqlServer:
                        paramType = typeof(SqlParameter);
                        prefix = "@";
                        break;
                    case DbContextType.MySql:
                        paramType = typeof(MySqlParameter);
                        prefix = "@";
                        break;
                    case DbContextType.Sqlite:
                        paramType = typeof(SqliteParameter);
                        prefix = "@";
                        break;
                    default:
                        throw new Exception("未实现的数据库类型");
                }
                foreach (var param in @params.GetType().GetProperties())
                {
                    var paramItem = Activator.CreateInstance(paramType, $"{prefix}{param.Name}", (object)param.GetValue(@params));
                    command.Parameters.Add(paramItem);
                }
            }
        }
        //创建命令（同时返回连接符）
        private static DbCommand createCommand(DbContext context, DbContextSqlQueryCommands commands, out DbConnection connection)
        {
            var conn = context.Database.GetDbConnection();
            connection = conn;
            conn.Open();
            var cmd = conn.CreateCommand();
            if (commands.Sqlite != null && context.Database.IsSqlite())
            {
                cmd.CommandText = commands.Sqlite.Query;
                combineParams(DbContextType.Sqlite, ref cmd, commands.Sqlite.Parameters);
            }
            else if(commands.MySql != null && context.Database.IsMySql())
            {
                cmd.CommandText = commands.MySql.Query;
                combineParams(DbContextType.MySql, ref cmd, commands.MySql.Parameters);
            }
            else if (commands.Sql != null && context.Database.IsSqlServer())
            {
                cmd.CommandText = commands.Sql.Query;
                combineParams(DbContextType.SqlServer, ref cmd, commands.Sql.Parameters);
            }
            else if (commands.InMemory != null)
            {
                throw new NotImplementedException();
            }
            return cmd;
        }

        /// <summary>
        /// 执行sql语句，返回受影响行数
        /// </summary>
        /// <param name="context">EF上下文</param>
        /// <param name="commands">数据库查询语句集合</param>
        /// <returns>受影响行数</returns>
        public static int Exec(this DbContext context, DbContextSqlQueryCommands commands)
        {
            var command = createCommand(context, commands, out var conn);
            var rsl = command.ExecuteNonQuery();
            conn.Close();
            return rsl;
        }

        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="context">EF上下文</param>
        /// <param name="commands">数据库查询语句集合</param>
        /// <returns>数据DataTable</returns>
        public static DataTable Query(this DbContext context, DbContextSqlQueryCommands commands)
        {
            var command = createCommand(context, commands, out var conn);
            var reader = command.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            conn.Close();
            return dt;
        }

        /// <summary>
        /// 查询数据库，返回多个查询结果集
        /// </summary>
        /// <param name="context">EF上下文</param>
        /// <param name="commands">数据库查询语句集合</param>
        /// <returns>数据DataSet</returns>
        public static DataSet QuerySet(this DbContext context, DbContextSqlQueryCommands commands)
        {
            var dt = Query(context, commands);
            var ds = new DataSet();
            ds.Tables.Add(dt);
            return ds;
        }

        /// <summary>
        /// 查询数据库，返回IEnumerable的强类型数据
        /// </summary>
        /// <typeparam name="T">查询结果类型</typeparam>
        /// <param name="context">EF上下文</param>
        /// <param name="commands">数据库查询语句集合</param>
        /// <returns>IEnumerable的强类型数据</returns>
        public static IEnumerable<T> Query<T>(this DbContext context, DbContextSqlQueryCommands commands)
        {
            var dt = Query(context, commands);
            return dt.ToEnumerable<T>();
        }

        /// <summary>
        /// 查询数据库，返回第一条数据
        /// </summary>
        /// <param name="context">EF上下文</param>
        /// <param name="commands">数据库查询语句集合</param>
        /// <returns>查询到的第一条数据或null</returns>
        public static DataRow QueryOne(this DbContext context, DbContextSqlQueryCommands commands)
        {
            var dt = Query(context, commands);
            return dt.Rows.Count > 0 ? dt.Rows[0] : null;
        }

        /// <summary>
        /// 查询数据库，返回第一条强类型数据
        /// </summary>
        /// <typeparam name="T">查询结果类型</typeparam>
        /// <param name="context">EF上下文</param>
        /// <param name="commands">数据库查询语句集合</param>
        /// <returns>查询到的第一条强类型数据</returns>
        public static T QueryOne<T>(this DbContext context, DbContextSqlQueryCommands commands)
        {
            var dr = QueryOne(context, commands);
            return dr.ToObject<T>();
        }

        /// <summary>
        /// 查询数据库，返回唯一数据
        /// </summary>
        /// <param name="context">EF上下文</param>
        /// <param name="commands">数据库查询语句集合</param>
        /// <returns>查询到的唯一数据</returns>
        public static object QueryObject(this DbContext context, DbContextSqlQueryCommands commands)
        {
            var command = createCommand(context, commands, out var conn);
            var rsl = command.ExecuteScalar();
            conn.Close();
            return rsl;
        }

        /// <summary>
        /// 查询数据库，返回唯一强类型数据
        /// </summary>
        /// <typeparam name="T">查询结果类型</typeparam>
        /// <param name="context">EF上下文</param>
        /// <param name="commands">数据库查询语句集合</param>
        /// <returns>查询到的唯一强类型数据</returns>
        public static T QueryObject<T>(this DbContext context, DbContextSqlQueryCommands commands)
        {
            return (T)QueryObject(context, commands);
        }
    }
}

    这里并未提供ToObject和ToEnumerable等扩展方法的实现，这些方法只是简单的将DataTable或者DataRow转换成强类型，利用反射可以轻松做到，有兴趣的朋友可以自己实现一下。 或者maybe我会在后面的文章里贴一下。

 

    这个扩展是针对DbContext的扩展，暴露的方法大体上是：Exec 返回影响行数，  Query 返回查询的列表，可以通过泛型重载Query<T>直接返回强类型的IEnumerable接口对象，QueryOne返回一行，同理QueryOne<T>返回强类型对象。QueryObject返回object，同理QueryObject<T>返回简单类型值。

 

    具体用法示例：

var list = db.Query<xxxxxxxClass>(new DbContextSqlQueryCommands
        {
            //调用存储过程
            Sql = new DbContextSqlQueryCommand(@"exec GetXXXProcedure @ids", new
            {
                ids = string.Join(",", cids),
            })
        });

    so, that's it.   好好工作，天天向太阳……

 

----------------------------------------华丽的分割线-------------------------------------

    补充一句，此扩展依赖Microsoft.EntityFrameWorkCore.xxx 的一堆，包括.SqlServer， .Sqlite，.InMemory等，其中Mysql微软未提供官方库，因而用了Pomelo.EntityFrameworkCore.MySql这个第三方库。
    另外一点就是，上述代码其实未实现InMemory的库的操作，主要是我还没搞清InMemory的库到底怎么用

-----------------------------------------我是最新分割线-------------------------------------------

有朋友问我ToEnumerable和ToObject的实现，这里贴一下吧

    /// <summary>
    /// 数据相关扩展
    /// </summary>
    public static class DataExtensions
    {
        //从属性列表为DataTable创建列
        private static void createColumns(DataTable table, PropertyInfo[] piArr)
        {
            table.Columns.Clear();
            foreach (var pi in piArr)
            {
                table.Columns.Add(pi.Name, pi.PropertyType);
            }
        }

        //用obj的属性填充row
        private static void fillDataRow(DataRow row, object obj, PropertyInfo[] piArr)
        {
            foreach (var pi in piArr)
            {
                row[pi.Name] = pi.GetValue(obj);
            }
        }

        //用row的栏填充obj
        public static void fillObject<T>(T obj, DataRow row, PropertyInfo[] piArr)
        {
            foreach (var pi in piArr)
            {
                try
                {
                    pi.SetValue(obj, row[pi.Name]);
                }
                catch
                {
                }
            }
        }

        /// <summary>
        /// 从类型为DataTable创建Columns
        /// </summary>
        /// <param name="table">DataTable对象</param>
        /// <param name="type">作为创建模板的类型</param>
        public static void CreateColumsFromType(this DataTable table, Type type)
        {
            PropertyInfo[] piArr = type.GetProperties();
            createColumns(table, piArr);
        }

        /// <summary>
        /// 从object为DataTable创建Columns
        /// </summary>
        /// <param name="table">DataTable对象</param>
        /// <param name="obj">作为创建模板的object</param>
        public static void CreateColumsFromObject(this DataTable table, object obj)
        {
            CreateColumsFromType(table, obj.GetType());
        }

        /// <summary>
        /// 将DataRow转换为强类型
        /// </summary>
        /// <typeparam name="T">要转换为的强类型</typeparam>
        /// <param name="row">要转换的DataRow对象</param>
        /// <returns>转换后的强类型对象</returns>
        public static T ToObject<T>(this DataRow row)
        {
            if (row == null)
            {
                return default(T);
            }
            var obj = Activator.CreateInstance<T>();
            PropertyInfo[] piArr = typeof(T).GetProperties();
            fillObject(obj, row, piArr);
            return obj;
        }

        /// <summary>
        /// 将对象转换为DataRow对象
        /// </summary>
        /// <param name="obj">要转换的对象</param>
        /// <returns>转换后的DataRow对象</returns>
        public static DataRow ToDataRow(this object obj)
        {
            if (obj == null)
            {
                return null;
            }
            PropertyInfo[] piArr = obj.GetType().GetProperties();
            DataTable dt = new DataTable();
            createColumns(dt, piArr);

            DataRow row = dt.NewRow();
            fillDataRow(row, obj, piArr);

            return row;
        }

        /// <summary>
        /// 将对象转换为属于指定DataTable的DataRow对象
        /// </summary>
        /// <param name="table">属于的table</param>
        /// <param name="obj">要转换的对象</param>
        /// <returns>转换后的属于指定DataTable的DataRow对象</returns>
        public static DataRow ToDataRow(this object obj, DataTable table)
        {
            if (obj == null)
            {
                return null;
            }
            PropertyInfo[] piArr = obj.GetType().GetProperties();
            createColumns(table, piArr);

            DataRow row = table.NewRow();
            fillDataRow(row, obj, piArr);

            return row;
        }


        /// <summary>
        /// 将DataTable转换为IEnumerable的强类型对象
        /// </summary>
        /// <typeparam name="T">要转换为的强类型</typeparam>
        /// <param name="table">要转换的DataTable对象</param>
        /// <returns>转换后的IEnumerable的强类型对象</returns>
        public static IEnumerable<T> ToEnumerable<T>(this DataTable table)
        {
            List<T> list = new List<T>();
            PropertyInfo[] piArr = typeof(T).GetProperties();
            foreach (DataRow row in table.Rows)
            {
                var obj = Activator.CreateInstance<T>();
                fillObject(obj, row, piArr);
                list.Add(obj);
            }
            return list.AsEnumerable<T>();
        }
        
        /// <summary>
        /// 将IEnumerable的强类型对象转换为DataTable
        /// </summary>
        /// <typeparam name="T">要转换的强类型</typeparam>
        /// <param name="objArr">要转换的IEnumerable的强类型对象</param>
        /// <returns>转换后的DataTable对象</returns>
        public static DataTable ToDataTable<T>(this IEnumerable<T> objArr)
        {
            DataTable dt = new DataTable();
            PropertyInfo[] piArr = typeof(T).GetProperties();
            createColumns(dt, piArr);

            foreach (var obj in objArr)
            {
                DataRow row = dt.NewRow();
                fillDataRow(row, obj, piArr);
                dt.Rows.Add(row);
            }
            return dt;
        }

        /// <summary>
        /// 将DataSet转换为IEnumerable的IEnumerable的强类型对象
        /// </summary>
        /// <typeparam name="T">要转换为的强类型</typeparam>
        /// <param name="set">要转换的DataSet对象</param>
        /// <returns>转换后的IEnumerable的IEnumerable的强类型对象</returns>
        public static IEnumerable<IEnumerable<T>> ToEnumerableEnumerable<T>(this DataSet set)
        {
            List<IEnumerable<T>> rsl = new List<IEnumerable<T>>();
            PropertyInfo[] piArr = typeof(T).GetProperties();

            foreach (DataTable dt in set.Tables)
            {
                List<T> list = new List<T>();
                foreach (DataRow row in dt.Rows)
                {
                    var obj = Activator.CreateInstance<T>();
                    fillObject(obj, row, piArr);
                    list.Add(obj);
                }
                rsl.Add(list.AsEnumerable<T>());
            }
            return rsl.AsEnumerable<IEnumerable<T>>();
        }

    }