EFCoreExtension

由于EFCore执行SQL和存储过程有局限性,只针对DataSet<T>,所以写一个扩展,能返回自定义对象集合

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;

namespace RD.Common
{
    /// <summary>
    /// EFCoreExtension
    /// </summary>
    public static class EFCoreExtension
    {
        /// <summary>
        /// 执行SQL查询,返回对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
        {
            var dt = SqlQuery(facade, sql, parameters);
            return dt.ToList<T>();
        }
        private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection, params object[] parameters)
        {
            var conn = facade.GetDbConnection();
            connection = conn;
            conn.Open();
            var cmd = conn.CreateCommand();
            if (facade.IsSqlServer())
            {
                cmd.CommandText = sql;
                if (parameters != null && parameters.Length > 0) cmd.Parameters.AddRange(parameters);
            }
            return cmd;
        }
        /// <summary>
        /// 执行SQL查询,返回DataTable
        /// </summary>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
            var reader = command.ExecuteReader();
            var dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            conn.Close();
            return dt;
        }
        /// <summary>
        /// DataTable转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static List<T> ToList<T>(this DataTable dt) where T : class, new()
        {
            var propertyInfos = typeof(T).GetProperties();
            var list = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                var t = new T();
                foreach (PropertyInfo p in propertyInfos)
                {
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                        p.SetValue(t, row[p.Name], null);
                }
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public static SqlParameter[] CreateParamArray(List<Dtos.InKeyValueDto> dto)
        {
            if (dto == null || dto.Count <= 0) return null;
            var param = new List<SqlParameter>();
            dto.ForEach(x =>
            {
                param.Add(new SqlParameter(x.Key, x.Value));
            });
            return param.ToArray();
        }
    }
}