20230223-EF6用原生的命令执行SQL命令

Jack_software

已于 2024-05-21 12:52:23 修改

阅读量1k

点赞数

分类专栏： C#Helper 文章标签： linq 数据库 EF6 原生SQL C#

于 2023-02-23 14:57:40 首次发布

本文链接：https://blog.csdn.net/Jack_software/article/details/129181755

版权

C#Helper 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

关注公众号 `N学无止界` 获取更多

目录:

一、环境说明

二、背景

三、示例代码

四、思考总结

一、环境说明

本机环境：windows10 操作系统
使用工具：Visual Studio 2022
Net版本：Net6.0

二、背景

在使用winform EF 开发过程中，需要用到类似 QueryList QueryScalar 等功能的时候，发现EF6只提供标量的List列表，且在一个context中，QueryScalar（执行SQL命令的事务有问题，无法更新到数据），所以需要一个扩展来执行特定的命令（SQL语句），以及可以实现动态的（dynamic）类型来满足开发需要。

官网参考资料：

SQL 查询 - EF Core | Microsoft Learn

查询标量（非实体）类型

备注

EF Core 7.0 中已引入此功能。

虽然可以使用 FromSql 来查询模型中定义的实体，但如果使用 SqlQuery，你就可以通过 SQL 轻松查询非实体标量类型，无需下降到较低级别的数据访问 API。例如，以下查询从 Blogs 表中提取所有 ID：C#复制


var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

还可以在 SQL 查询的基础上组合使用 LINQ 运算符。但是，由于 SQL 成为子查询，其输出列需要由 SQL EF 添加项来引用，因此必须为输出列 Value 命名。例如，以下查询返回的 ID 高于 ID 平均值：C#复制


var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

FromSql 可与数据库提供程序支持的任何标量类型配合使用。如果想要使用数据库提供程序不支持的类型，可以使用约定前配置为其定义值转换。

SqlQueryRaw 允许动态构造 SQL 查询，就像 FromSqlRaw 对实体类型所做的那样。

执行非查询 SQL

在某些情况下，可能需要执行不返回任何数据的 SQL，通常用于修改数据库中的数据或调用不返回任何结果集的存储过程。可以通过 ExecuteSql 完成此操作：C#复制


using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

它会执行提供的 SQL 并返回修改的行的数目。 ExecuteSql 使用安全的参数化来防止 SQL 注入，就像 FromSql 一样，而 ExecuteSqlRaw 允许动态构造 SQL 查询，就像 FromSqlRaw 对查询所做的那样。

备注

在 EF Core 7.0 之前，有时必须使用 ExecuteSql API 对数据库执行“批量更新”，如上所示；这比在查询所有匹配行后使用 SaveChanges 来修改它们要高效得多。 EF Core 7.0 引入了 ExecuteUpdate 和 ExecuteDelete，因此可以通过 LINQ 表达高效的批量更新操作。建议尽可能使用这些 API 而非 ExecuteSql。

限制

从 SQL 查询返回实体类型时，需注意以下几个限制：

SQL 查询必须返回实体类型的所有属性的数据。

结果集中的列名必须与属性映射到的列名称匹配。请注意，此行为与 EF6 不同；EF6 忽略了 SQL 查询的属性-列映射，只需结果集列名与这些属性名相匹配即可。

SQL 查询不能包含关联数据。但是，在许多情况下你可以在查询后面紧跟着使用

Include 方法以返回关联数据（请参阅包含关联数据）。

三、示例代码

说明：编写扩展程序，用基本的SQL语句支持动态或者泛型支持执行数据库

程序代码：


using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OpenCvSharp;

namespace .......config
{
    /// <summary>
    /// DbContextExtension
    /// </summary>
    public static class DbContextExtension
    {
        /// <summary>
        /// QueryList
        /// </summary>
        /// <param name="context"></param>
        /// <param name="sql"></param>
        /// <param name="map"></param>
        /// <returns></returns>
        public static IEnumerable<dynamic> QueryList(this DbContext context, string sql, Dictionary<string, object>? map = null)
        {
            using (var cmd = context.Database.GetDbConnection().CreateCommand())
            {
                CmdExecute(cmd, sql, map);
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        var row = new ExpandoObject() as IDictionary<string, object>;
                        for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
                        {
                            row.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
                        }
                        yield return row;
                    }
                }
            }
        }

        /// <summary>
        /// QueryList
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="sql"></param>
        /// <param name="itemReader"></param>
        /// <param name="map"></param>
        /// <returns></returns>
        public static IEnumerable<T> QueryList<T>(this DbContext context, string sql, Func<IDataReader, T> itemReader, Dictionary<string, object>? map = null)
        {
            using (var cmd = context.Database.GetDbConnection().CreateCommand())
            {
                CmdExecute(cmd, sql, map);
                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        yield return itemReader(dataReader);
                    }
                }
            }
        }

        /// <summary>
        /// QueryList
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="sql"></param>
        /// <param name="itemReader"></param>
        /// <param name="map"></param>
        /// <returns></returns>
        public static T? QueryScalar<T>(this DbContext context, string sql, Dictionary<string, object>? map = null)
        {
            using (var cmd = context.Database.GetDbConnection().CreateCommand())
            {
                CmdExecute(cmd, sql, map);
                object? result = cmd.ExecuteScalar();
                return result is DBNull ? default(T) : (T)result!;
            }
        }

        /// <summary>
        /// CmdExecute
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="sql"></param>
        /// <param name="map"></param>
        private static void CmdExecute(DbCommand cmd, string sql, Dictionary<string, object>? map)
        {
            cmd.CommandText = sql;
            if (null != cmd.Connection && cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }
            if (null != map)
            {
                foreach (KeyValuePair<string, object> p in map)
                {
                    DbParameter dbParameter = cmd.CreateParameter();
                    dbParameter.ParameterName = p.Key;
                    dbParameter.Value = p.Value;
                    cmd.Parameters.Add(dbParameter);
                }
            }
        }
    }
}

调用主要代码:

四、思考总结


///核心代码

        /// <summary>
        /// QueryList
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <param name="sql"></param>
        /// <param name="itemReader"></param>
        /// <param name="map"></param>
        /// <returns></returns>
        public static T? QueryScalar<T>(this DbContext context, string sql, Dictionary<string, object>? map = null)
        {
            using (var cmd = context.Database.GetDbConnection().CreateCommand())
            {
                CmdExecute(cmd, sql, map);
                object? result = cmd.ExecuteScalar();
                return result is DBNull ? default(T) : (T)result!;
            }
        }

        /// <summary>
        /// CmdExecute
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="sql"></param>
        /// <param name="map"></param>
        private static void CmdExecute(DbCommand cmd, string sql, Dictionary<string, object>? map)
        {
            cmd.CommandText = sql;
            if (null != cmd.Connection && cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }
            if (null != map)
            {
                foreach (KeyValuePair<string, object> p in map)
                {
                    DbParameter dbParameter = cmd.CreateParameter();
                    dbParameter.ParameterName = p.Key;
                    dbParameter.Value = p.Value;
                    cmd.Parameters.Add(dbParameter);
                }
            }
        }
    }

上述代码通过 context.Database.GetDbConnection().CreateCommand() 得到Command , 然后根据底层的SQL命令进行执行原来的逻辑。根据需要的场景满足业务需求，后续代码可以进一步封装进行泛型变换,支持VO BO 等面向领域对象的获取，欢迎大家沟通。