EF Core中执行Sql语句查询操作之FromSql，ExecuteSqlCommand，SqlQuery

一、目前EF Core的版本为V2.1

相比较EF Core v1.0 目前已经增加了不少功能。

EF Core除了常用的增删改模型操作，Sql语句在不少项目中是不能避免的。

在EF Core中上下文，可以返货DbConnection ，执行sql语句。这是最底层的操作方式，代码写起来还是挺多的。

初次之外 EF Core中还支持 FromSql,ExecuteSqlCommand 连个方法，用于更方便的执行Sql语句。

另外，目前版本的EF Core 不支持SqlQuery，但是我们可以自己扩展一个。坐等升级以后支持吧。

1.FromSql，执行列表查询

public static IQueryable<TEntity> FromSql<TEntity>([NotNullAttribute] this IQueryable<TEntity> source, [NotParameterized] RawSqlString sql, [NotNullAttribute] params object[] parameters) where TEntity : class;

这种方式，仅用于当前上线文中注册的 模型对象。

对于上下文DbSet<T>中没有定义的不起作用。

示例代码1：

//执行sql查询语句 FromSql()
QLLB_SWXContext _Context = new QLLB_SWXContext();
string sql = "select * from Article where CategoryID=1;";
List<Article> list = _Context.Article.FromSql(sql).ToList();
foreach (var item in list)
{
Console.WriteLine(item.Title);
}

示例代码2：视图中的查询

---创建视图，查询没有分配角色的菜单
create view view_NoRole
as
select * from Sys_Navigation
where NavID not in (
select distinct NavID from Sys_Role_Nav
)
//查询视图
string sql2 = "select * from view_NoRole";
List<SysNavigation> roleList = _Context.SysNavigation.FromSql(sql2).ToList();
foreach (var item in roleList)
{
Console.WriteLine(item.Title);
}

2.ExecuteSqlCommand，执行Sql操作处理

QLLB_SWXContext _Context = new QLLB_SWXContext();
//执行数据操作sql,返回受影响的行数
string sql = "update Sys_Role set SortValue=1 ;";
int count = _Context.Database.ExecuteSqlCommand(sql);
Console.WriteLine(count);

3.自定义SqlQuery，执行列表查询，在上线文中不存的对象。

示例代码1:

QLLB_SWXContext _Context = new QLLB_SWXContext();
//特别说明，自定义分装的不支持 单个值查询
//不支持object 查询
//自定义查询操作 SqlQuery
string sql = "select sum(ViewCount)*1.11 as allCount from Article;";
TempData result = _Context.Database.SqlQuery<TempData>(sql).FirstOrDefault();
Console.WriteLine(result.AllCount);

对象定义

public class TempData
{
public int CategoryID { get; set; }
public string Title { get; set; }
public int ArtCount { get; set; }
/// <summary>
/// 求和结果
/// </summary>
public decimal AllCount { get; set; }
}

示例代码2：

执行视图查询：

--定义视图，文章分类和对应分类的文章数量
create view view_CateCount
as
select C.CategoryID,C.Title, (
select count(*) from Article where CategoryID=C.CategoryID
) as ArtCount from ArticleCategory C;

C#代码：

//组合查询
string sql2 = "select * from view_CateCount;";
List<TempData> tempList = _Context.Database.SqlQuery<TempData>(sql2).ToList();
foreach (var item in tempList)
{
Console.WriteLine(item.Title);
}

SqlQuery扩展定义：

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

namespace Som.Common
{
    public static class DbContextExtensions
    {
        private static void CombineParams(ref DbCommand command, params object[] parameters)
        {
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (!parameter.ParameterName.Contains("@"))
                        parameter.ParameterName = $"@{parameter.ParameterName}";
                    command.Parameters.Add(parameter);
                }
            }
        }

        private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters)
        {
            DbConnection conn = facade.GetDbConnection();
            dbConn = conn;
            conn.Open();
            DbCommand cmd = conn.CreateCommand();
            if (facade.IsSqlServer())
            {
                cmd.CommandText = sql;
                CombineParams(ref cmd, parameters);
            }
            return cmd;
        }
        /// <summary>
        /// 执行SQL返回受影响的行数
        /// </summary>
        public static int ExecSqlNoQuery<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new()
        {
            return facade.ExecuteNoQuery<T>( sql, sqlParams);
        }
        /// <summary>
        /// 执行存储过程返回IEnumerable数据集
        /// </summary>
        public static IEnumerable<T> ExecProcReader<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new()
        {
            return facade.Execute<T>( sql, CommandType.StoredProcedure, sqlParams);
        }
        /// <summary>
        /// 执行sql返回IEnumerable数据集
        /// </summary>
        public static IEnumerable<T> ExecSqlReader<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new()
        {
            return facade.Execute<T>(sql,CommandType.Text, sqlParams);
        }
        /// <summary>
        /// 执行SQL 返回受影响的行数 用于update delete 等
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        private static int ExecuteNoQuery<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams) where T : new()
        {
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, sqlParams);
            int result = 0;            
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            if (sqlParams != null)
            {
                cmd.Parameters.AddRange(sqlParams);
            }
            result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }
        private static IEnumerable<T> Execute<T>(this DatabaseFacade facade, string sql, CommandType type, SqlParameter[] parameters) where T : new()
        {
            
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);            
            cmd.CommandText = sql;
            cmd.CommandType = type;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            DataTable dt = new DataTable();
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
            }
            conn.Close();
            return dt.ToCollection<T>();
        }
        /// <summary>
        /// DataTable和集合的扩展
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static IEnumerable<T> ToCollection<T>(this DataTable dt) where T : new()
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return Enumerable.Empty<T>();
            }
            IList<T> ts = new List<T>();
            // 获得此模型的类型 
            Type type = typeof(T);
            string tempName = string.Empty;
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                PropertyInfo[] propertys = t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;
                    //检查DataTable是否包含此列（列名==对象的属性名）     
                    if (dt.Columns.Contains(tempName))
                    {
                        // 判断此属性是否有Setter   
                        if (!pi.CanWrite) continue;//该属性不可写，直接跳出   
                        object value = dr[tempName];
                        if (value != DBNull.Value)
                            //pi.SetValue(t, value, null);
                            pi.SetValue(t, StrHelper.ConvertType(value, pi.PropertyType), null);
                    }
                }
                ts.Add(t);
            }
            return ts;
        }

        /// <summary>
        /// 执行SQL 返回DataTable
        /// </summary>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
            DbDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            conn.Close();
            return dt;
        }
        /// <summary>
        /// 执行SQL 返回 IEnumerable<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static IEnumerable<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
        {
            DataTable dt = SqlQuery(facade, sql, parameters);
            return dt.ToEnumerable<T>();
        }
     
        public static IEnumerable<T> ToEnumerable<T>(this DataTable dt) where T : class, new()
        {
            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            T[] ts = new T[dt.Rows.Count];
            int i = 0;
            //定义一个临时变量  存放属性名称
            string tempName = string.Empty;
            foreach (DataRow row in dt.Rows)
            {
                T t = new T();
                foreach (PropertyInfo p in propertyInfos)
                {
                    tempName = p.Name;//将属性名称赋值给临时变量  
                    //检查DataTable是否包含此列（列名==对象的属性名） 
                    string tpye1 = p.PropertyType.ToString();
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                    {
                        // 判断此属性是否有Setter  
                        if (!p.CanWrite) continue;//该属性不可写，直接跳出  
                        object value = row[tempName];
                        p.SetValue(t, StrHelper.ConvertType(value, p.PropertyType), null);
                    }
                }
                ts[i] = t;
                i++;
            }
            return ts;
        }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Som.Common
{
    /// <summary>
    /// 字符串操作 - 工具方法
    /// </summary>
    public static partial class StrHelper
    {
        #region Empty(空字符串)

        /// <summary>
        /// 空字符串
        /// </summary>
        public static string Empty
        {
            get { return string.Empty; }
        }

        /// <summary>
        /// 判断这个Object是否为空
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool IsEmpty(object obj)
        {
            if (obj == null)
            {
                return true;
            }
            else if (obj.ToString() == "")
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// 将obj转换为Type类型
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public static object ConvertType(object obj, Type t)
        {
            if (obj == null || obj.ToString() == "")
            {
                return null;
            }

            string tpye1 = t.ToString();
            if (tpye1.IndexOf("System.String") != -1)
            {
                return Convert.ToString(obj);
            }
            else if (tpye1.IndexOf("System.Int32") != -1)
            {
                if (obj.ToString().ToLower()=="true")
                {
                    obj = "1";
                }
                else if (obj.ToString().ToLower() == "false")
                {
                    obj = "0";
                }
                return Convert.ToInt32(obj);
            }
            else if (tpye1.IndexOf("System.Int64") != -1)
            {
                
                return Convert.ToInt64(obj);
            }
            else if (tpye1.IndexOf("System.DateTime") != -1)
            {
                if (StrHelper.IsNumber(obj.ToString()))
                {
                    return Convert.ToDateTime(Convert.ToDouble(obj));
                }
                else
                {
                    return Convert.ToDateTime(obj);
                }
            }
            else if (tpye1.IndexOf("System.Boolean") != -1)
            {
                if (obj.ToString() == "是")
                {
                    obj = true;
                }
                else if (obj.ToString() == "否")
                {
                    obj = false;
                }

                return Convert.ToBoolean(obj);
            }
            else if (tpye1.IndexOf("System.Decimal") != -1)
            {
                return Convert.ToDecimal(obj);
            }
            else if (tpye1.IndexOf("System.Double") != -1)
            {
                return Convert.ToDouble(obj);
            }

            //else if (tpye1.IndexOf("System.Collections.Generic.ICollection`1") != -1)
            //{
            //    string Type = tpye1.Replace("System.Collections.Generic.ICollection`1[Model.", "").Replace("]", "");
            //    using (var context = new MESContext())
            //    {
            //        var postTitles = context.Database.SqlQuery<string>("SELECT ID FROM dbo." + Type + "s where ID in (" + obj.ToString() + ")").ToList();
            //        return postTitles;
            //    }
            //}
            return obj;
        }

        #endregion

        #region PinYin(获取汉字的拼音简码)

        /// <summary>
        /// 获取汉字的拼音简码，即首字母缩写,范例：中国,返回zg
        /// </summary>
        /// <param name="chineseText">汉字文本,范例： 中国</param>
        public static string PinYin(string chineseText)
        {
            if (string.IsNullOrWhiteSpace(chineseText))
                return string.Empty;
            var result = new StringBuilder();
            foreach (char text in chineseText)
                result.AppendFormat("{0}", ResolvePinYin(text));
            return result.ToString().ToLower();
        }

        /// <summary>
        /// 解析单个汉字的拼音简码
        /// </summary>
        /// <param name="text">单个汉字</param>
        private static string ResolvePinYin(char text)
        {
            byte[] charBytes = Encoding.Default.GetBytes(text.ToString());
            if (charBytes[0] <= 127)
                return text.ToString();
            var unicode = (ushort) (charBytes[0] * 256 + charBytes[1]);
            string pinYin = ResolvePinYinByCode(unicode);
            if (!string.IsNullOrWhiteSpace(pinYin))
                return pinYin;
            return ResolvePinYinByFile(text.ToString());
        }

        /// <summary>
        /// 使用字符编码方式获取拼音简码
        /// </summary>
        private static string ResolvePinYinByCode(ushort unicode)
        {
            if (unicode >= 'uB0A1' && unicode <= 'uB0C4')
                return "A";
            if (unicode >= 'uB0C5' && unicode <= 'uB2C0' && unicode != 45464)
                return "B";
            if (unicode >= 'uB2C1' && unicode <= 'uB4ED')
                return "C";
            if (unicode >= 'uB4EE' && unicode <= 'uB6E9')
                return "D";
            if (unicode >= 'uB6EA' && unicode <= 'uB7A1')
                return "E";
            if (unicode >= 'uB7A2' && unicode <= 'uB8C0')
                return "F";
            if (unicode >= 'uB8C1' && unicode <= 'uB9FD')
                return "G";
            if (unicode >= 'uB9FE' && unicode <= 'uBBF6')
                return "H";
            if (unicode >= 'uBBF7' && unicode <= 'uBFA5')
                return "J";
            if (unicode >= 'uBFA6' && unicode <= 'uC0AB')
                return "K";
            if (unicode >= 'uC0AC' && unicode <= 'uC2E7')
                return "L";
            if (unicode >= 'uC2E8' && unicode <= 'uC4C2')
                return "M";
            if (unicode >= 'uC4C3' && unicode <= 'uC5B5')
                return "N";
            if (unicode >= 'uC5B6' && unicode <= 'uC5BD')
                return "O";
            if (unicode >= 'uC5BE' && unicode <= 'uC6D9')
                return "P";
            if (unicode >= 'uC6DA' && unicode <= 'uC8BA')
                return "Q";
            if (unicode >= 'uC8BB' && unicode <= 'uC8F5')
                return "R";
            if (unicode >= 'uC8F6' && unicode <= 'uCBF9')
                return "S";
            if (unicode >= 'uCBFA' && unicode <= 'uCDD9')
                return "T";
            if (unicode >= 'uCDDA' && unicode <= 'uCEF3')
                return "W";
            if (unicode >= 'uCEF4' && unicode <= 'uD188')
                return "X";
            if (unicode >= 'uD1B9' && unicode <= 'uD4D0')
                return "Y";
            if (unicode >= 'uD4D1' && unicode <= 'uD7F9')
                return "Z";
            return string.Empty;
        }

        /// <summary>
        /// 从拼音简码文件获取
        /// </summary>
        /// <param name="text">单个汉字</param>
        private static string ResolvePinYinByFile(string text)
        {
            int index = Const.ChinesePinYin.IndexOf(text, StringComparison.Ordinal);
            if (index < 0)
                return string.Empty;
            return Const.ChinesePinYin.Substring(index + 1, 1);
        }

        #endregion

        #region Splice(拼接集合元素)

        /// <summary>
        /// 拼接集合元素
        /// </summary>
        /// <typeparam name="T">集合元素类型</typeparam>
        /// <param name="list">集合</param>
        /// <param name="quotes">引号，默认不带引号，范例：单引号 "'"</param>
        /// <param name="separator">分隔符，默认使用逗号分隔</param>
        public static string Splice<T>(IEnumerable<T> list, string quotes = "", string separator = ",")
        {
            if (list == null)
                return string.Empty;
            var result = new StringBuilder();
            foreach (var each in list)
                result.AppendFormat("{0}{1}{0}{2}", quotes, each, separator);
            return result.ToString().TrimEnd(separator.ToCharArray());
        }

        #endregion

        #region FirstUpper(将值的首字母大写)

        /// <summary>
        /// 将值的首字母大写
        /// </summary>
        /// <param name="value">值</param>
        public static string FirstUpper(string value)
        {
            string firstChar = value.Substring(0, 1).ToUpper();
            return firstChar + value.Substring(1, value.Length - 1);
        }

        #endregion

        #region ToCamel(将字符串转成驼峰形式)

        /// <summary>
        /// 将字符串转成驼峰形式
        /// </summary>
        /// <param name="value">原始字符串</param>
        public static string ToCamel(string value)
        {
            return FirstUpper(value.ToLower());
        }

        #endregion

        #region ContainsChinese(是否包含中文)

        /// <summary>
        /// 是否包含中文
        /// </summary>
        /// <param name="text">文本</param>
        public static bool ContainsChinese(string text)
        {
            const string pattern = "[u4e00-u9fa5]+";
            return Regex.IsMatch(text, pattern);
        }

        #endregion

        #region ContainsNumber(是否包含数字)

        /// <summary>
        /// 是否包含数字
        /// </summary>
        /// <param name="text">文本</param>
        public static bool ContainsNumber(string text)
        {
            const string pattern = "[0-9]+";
            return Regex.IsMatch(text, pattern);
        }

        #endregion

        #region Distinct(去除重复)

        /// <summary>
        /// 去除重复
        /// </summary>
        /// <param name="value">值，范例1："5555",返回"5",范例2："4545",返回"45"</param>
        public static string Distinct(string value)
        {
            var array = value.ToCharArray();
            return new string(array.Distinct().ToArray());
        }

        #endregion

        #region Truncate(截断字符串)

        /// <summary>
        /// 截断字符串
        /// </summary>
        /// <param name="text">文本</param>
        /// <param name="length">返回长度</param>
        /// <param name="endCharCount">添加结束符号的个数，默认0，不添加</param>
        /// <param name="endChar">结束符号，默认为省略号</param>
        public static string Truncate(string text, int length, int endCharCount = 0, string endChar = ".")
        {
            if (string.IsNullOrWhiteSpace(text))
                return string.Empty;
            if (text.Length < length)
                return text;
            return text.Substring(0, length) + GetEndString(endCharCount, endChar);
        }

        /// <summary>
        /// 获取结束字符串
        /// </summary>
        private static string GetEndString(int endCharCount, string endChar)
        {
            StringBuilder result = new StringBuilder();
            for (int i = 0; i < endCharCount; i++)
                result.Append(endChar);
            return result.ToString();
        }

        #endregion

        #region Unique(获取全局唯一值)

        /// <summary>
        /// 获取全局唯一值
        /// </summary>
        public static string Unique()
        {
            return Guid.NewGuid().ToString().Replace("-", "");
        }

        #endregion

        public static bool IsNumber(string strNumber)
        {
            Regex objNotNumberPattern = new Regex("[^0-9.-]");
            Regex objTwoDotPattern = new Regex("[0-9]*[.][0-9]*[.][0-9]*");
            Regex objTwoMinusPattern = new Regex("[0-9]*[-][0-9]*[-][0-9]*");
            String strValidRealPattern = "^([-]|[.]|[-.]|[0-9])[0-9]*[.]*[0-9]+$";
            String strValidIntegerPattern = "^([-]|[0-9])[0-9]*$";
            Regex objNumberPattern = new Regex("(" + strValidRealPattern + ")|(" + strValidIntegerPattern + ")");

            return !objNotNumberPattern.IsMatch(strNumber) &&
                   !objTwoDotPattern.IsMatch(strNumber) &&
                   !objTwoMinusPattern.IsMatch(strNumber) &&
                   objNumberPattern.IsMatch(strNumber);
        }

        /// <summary>
        /// 将字符串格式化成指定的数据类型
        /// </summary>
        /// <param name="str"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static object Format(this string str, Type type)
        {
            if (String.IsNullOrEmpty(str))
                return null;
            if (type == null)
                return str;
            if (type.IsArray)
            {
                Type elementType = type.GetElementType();
                String[] strs = str.Split(new char[] {';'});
                Array array = Array.CreateInstance(elementType, strs.Length);
                for (int i = 0, c = strs.Length; i < c; ++i)
                {
                    array.SetValue(ConvertSimpleType(strs[i], elementType), i);
                }

                return array;
            }

            return ConvertSimpleType(str, type);
        }

        private static object ConvertSimpleType(object value, Type destinationType)
        {
            object returnValue;
            if ((value == null) || destinationType.IsInstanceOfType(value))
            {
                return value;
            }

            string str = value as string;
            if ((str != null) && (str.Length == 0))
            {
                return null;
            }

            TypeConverter converter = TypeDescriptor.GetConverter(destinationType);
            bool flag = converter.CanConvertFrom(value.GetType());
            if (!flag)
            {
                converter = TypeDescriptor.GetConverter(value.GetType());
            }

            if (!flag && !converter.CanConvertTo(destinationType))
            {
                throw new InvalidOperationException("无法转换成类型：" + value.ToString() + "==>" + destinationType);
            }

            try
            {
                returnValue = flag
                    ? converter.ConvertFrom(null, null, value)
                    : converter.ConvertTo(null, null, value, destinationType);
            }
            catch (Exception e)
            {
                throw new InvalidOperationException("类型转换出错：" + value.ToString() + "==>" + destinationType, e);
            }

            return returnValue;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <param name="d">默认值</param>
        /// <returns></returns>
        public static string ToString(this decimal? s, string d)
        {
            return s.ToString(0, d);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <param name="s">小数保留位数</param>
        /// <param name="d">默认值</param>
        /// <returns></returns>
        public static string ToString(this decimal? s, int i, string d)
        {
            if (!s.HasValue)
                return d;
            return s.Value.ToString("f" + i);
        }

        /// <summary>
        /// 获取中英文混排字符串的实际长度(字节数)
        /// </summary>
        /// <param name="str">要获取长度的字符串</param>
        /// <returns>字符串的实际长度值（字节数）</returns>
        public static int GetStringLength(string str)
        {
            if (str.Equals(string.Empty))
                return 0;
            int strlen = 0;
            ASCIIEncoding strData = new ASCIIEncoding();
            //将字符串转换为ASCII编码的字节数字
            byte[] strBytes = strData.GetBytes(str);
            for (int i = 0; i <= strBytes.Length - 1; i++)
            {
                if (strBytes[i] == 63) //中文都将编码为ASCII编码63,即"?"号
                    strlen++;
                strlen++;
            }

            return strlen;
        }

        /// <summary>
        /// 查找字符串第N次位置
        /// </summary>
        /// <param name=""></param>
        /// <param name=""></param>
        /// <param name=""></param>
        /// <returns></returns>
        //参数：字符串，要查找的字符串值，要查找第几个该字符串值
        public static int GetStrFindIndexToN(string str, string cha, int num)
        {
            var x = str.IndexOf(cha);
            for (var i = 0; i < num - 1; i++) //从第0位开始，NUM需要-1
            {
                x = str.IndexOf(cha, x + 1);
            }
            if (x==-1)
            {
                x = str.Length;
            }
            return x;
        }
    }
}

using Microsoft.EntityFrameworkCore;using Microsoft.EntityFrameworkCore.Infrastructure;using System;using System.Collections.Generic;using System.Data;using System.Data.Common;using System.Data.SqlClient;using System.Linq;using System.Reflection;using System.Text;
namespace Som.Common{    public static class DbContextExtensions    {        private static void CombineParams(ref DbCommand command, params object[] parameters)        {            if (parameters != null)            {                foreach (SqlParameter parameter in parameters)                {                    if (!parameter.ParameterName.Contains("@"))                        parameter.ParameterName = $"@{parameter.ParameterName}";                    command.Parameters.Add(parameter);                }            }        }
        private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters)        {            DbConnection conn = facade.GetDbConnection();            dbConn = conn;            conn.Open();            DbCommand cmd = conn.CreateCommand();            if (facade.IsSqlServer())            {                cmd.CommandText = sql;                CombineParams(ref cmd, parameters);            }            return cmd;        }        /// <summary>        /// 执行SQL返回受影响的行数        /// </summary>        public static int ExecSqlNoQuery<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new()        {            return facade.ExecuteNoQuery<T>( sql, sqlParams);        }        /// <summary>        /// 执行存储过程返回IEnumerable数据集        /// </summary>        public static IEnumerable<T> ExecProcReader<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new()        {            return facade.Execute<T>( sql, CommandType.StoredProcedure, sqlParams);        }        /// <summary>        /// 执行sql返回IEnumerable数据集        /// </summary>        public static IEnumerable<T> ExecSqlReader<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams = null) where T : new()        {            return facade.Execute<T>(sql,CommandType.Text, sqlParams);        }        /// <summary>        /// 执行SQL 返回受影响的行数 用于update delete 等        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="facade"></param>        /// <param name="sql"></param>        /// <param name="sqlParams"></param>        /// <returns></returns>        private static int ExecuteNoQuery<T>(this DatabaseFacade facade, string sql, SqlParameter[] sqlParams) where T : new()        {            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, sqlParams);            int result = 0;                        cmd.CommandText = sql;            cmd.CommandType = CommandType.Text;            if (sqlParams != null)            {                cmd.Parameters.AddRange(sqlParams);            }            result = cmd.ExecuteNonQuery();            conn.Close();            return result;        }        private static IEnumerable<T> Execute<T>(this DatabaseFacade facade, string sql, CommandType type, SqlParameter[] parameters) where T : new()        {                        DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);                        cmd.CommandText = sql;            cmd.CommandType = type;            if (parameters != null)            {                cmd.Parameters.AddRange(parameters);            }            DataTable dt = new DataTable();            using (DbDataReader reader = cmd.ExecuteReader())            {                dt.Load(reader);            }            conn.Close();            return dt.ToCollection<T>();        }        /// <summary>        /// DataTable和集合的扩展        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="dt"></param>        /// <returns></returns>        public static IEnumerable<T> ToCollection<T>(this DataTable dt) where T : new()        {            if (dt == null || dt.Rows.Count == 0)            {                return Enumerable.Empty<T>();            }            IList<T> ts = new List<T>();            // 获得此模型的类型             Type type = typeof(T);            string tempName = string.Empty;            foreach (DataRow dr in dt.Rows)            {                T t = new T();                PropertyInfo[] propertys = t.GetType().GetProperties();                foreach (PropertyInfo pi in propertys)                {                    tempName = pi.Name;                    //检查DataTable是否包含此列（列名==对象的属性名）                         if (dt.Columns.Contains(tempName))                    {                        // 判断此属性是否有Setter                           if (!pi.CanWrite) continue;//该属性不可写，直接跳出                           object value = dr[tempName];                        if (value != DBNull.Value)                            //pi.SetValue(t, value, null);                            pi.SetValue(t, StrHelper.ConvertType(value, pi.PropertyType), null);                    }                }                ts.Add(t);            }            return ts;        }
        /// <summary>        /// 执行SQL 返回DataTable        /// </summary>        /// <param name="facade"></param>        /// <param name="sql"></param>        /// <param name="parameters"></param>        /// <returns></returns>        public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)        {            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);            DbDataReader reader = cmd.ExecuteReader();            DataTable dt = new DataTable();            dt.Load(reader);            reader.Close();            conn.Close();            return dt;        }        /// <summary>        /// 执行SQL 返回 IEnumerable<T>        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="facade"></param>        /// <param name="sql"></param>        /// <param name="parameters"></param>        /// <returns></returns>        public static IEnumerable<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()        {            DataTable dt = SqlQuery(facade, sql, parameters);            return dt.ToEnumerable<T>();        }             public static IEnumerable<T> ToEnumerable<T>(this DataTable dt) where T : class, new()        {            PropertyInfo[] propertyInfos = typeof(T).GetProperties();            T[] ts = new T[dt.Rows.Count];            int i = 0;            //定义一个临时变量  存放属性名称            string tempName = string.Empty;            foreach (DataRow row in dt.Rows)            {                T t = new T();                foreach (PropertyInfo p in propertyInfos)                {                    tempName = p.Name;//将属性名称赋值给临时变量                      //检查DataTable是否包含此列（列名==对象的属性名）                     string tpye1 = p.PropertyType.ToString();                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)                    {                        // 判断此属性是否有Setter                          if (!p.CanWrite) continue;//该属性不可写，直接跳出                          object value = row[tempName];                        p.SetValue(t, StrHelper.ConvertType(value, p.PropertyType), null);                    }                }                ts[i] = t;                i++;            }            return ts;        }    }}