65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 Entity Framework Core 6 执行原始 SQL 查询

<b>Dipon<font color="orange">Roy</font></b>
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.64/5 (10投票s)

2022 年 8 月 10 日

CPOL

4分钟阅读

viewsIcon

94563

downloadIcon

540

运行原始 SQL 查询 - Entity Framework Core 6

背景

Entity Framework Core 允许我们在处理关系数据库时直接使用原始 SQL 查询。此外，它还提供了使用 ADO.NET 功能直接针对数据库执行原始 SQL 查询的机制。在这里，我们将探讨在 Entity Framework Core 中运行原始 SQL 的现有选项和自定义选项，但将更多地关注使用 ADO.NET 的扩展方法实现。

现有选项

在 Entity Framework Core 中，有多种选项可以运行原始 SQL 查询。要使用它们，我们需要安装 Microsoft.EntityFrameworkCore.Relational 以及 Microsoft.EntityFrameworkCore。

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Relational

ExecuteSqlRaw

执行非查询 SQL。这里有一些 insert、update 和 delete 的示例。参数化查询是可选的，如果需要可以省略。

Insert

object[] paramItems = new object[]
{
    new SqlParameter("@paramName", "Ben"),
    new SqlParameter("@paramCreatedBy", "Ben"),
    new SqlParameter("@paramCreatedOn", DateTime.UtcNow),
    new SqlParameter("@paramIsDeleted", true),
};
int items = Db.Database.ExecuteSqlRaw("INSERT INTO Users([Name], 
[IsDeleted], CreatedOn, CreatedBy) VALUES (@paramName, @paramIsDeleted, 
@paramCreatedOn, @paramCreatedBy)", paramItems);

更新

object[] paramItems = new object[]
{
    new SqlParameter("@paramEmail", "ben@gmail.com"),
    new SqlParameter("@paramName", "Ben")
};
int items = Db.Database.ExecuteSqlRaw
("UPDATE Users SET Email = @paramEmail WHERE [Name] = @paramName", paramItems);

删除

object[] paramItems = new object[]
{
    new SqlParameter("@paramName", "Ben")
};
int items = Db.Database.ExecuteSqlRaw("DELETE FROM Users 
            WHERE [Name] = @paramName", paramItems);

在 3.1 之前，有 ExecuteSqlCommand。

FromSqlRaw<T>

选择数据并映射到现有的 DbSet<TSource>。

List<User> usersInDb = Db.Users.FromSqlRaw
(
    "SELECT * FROM Users WHERE Name=@paramName",
    new SqlParameter("@paramName", user.Name)
)
.ToList();

这仅适用于 DbSet 声明。这里的 Users 是 DbSet<T>，在 DbContext 中声明。

public class CpuAppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
}

FromSqlInterpolated<T>

List<User> usersInDb = Db.Users.FromSqlInterpolated<User>
(
    $"SELECT * FROM Users WHERE Name={user.Name}"
)
.ToList();

自定义 DbContext 扩展方法

这里有一些用于 DbContext 和 DatabaseFacade 对象的扩展方法，用于运行原始 SQL。在 Database.Core 项目的帮助类 EfSqlHelper.cs 中，我们可以找到列出的扩展方法。

ExecuteScalar

  • 返回查询返回的结果集中的第一行的第一个列
  • 可选的查询参数化
  • 可选的命令类型和命令超时

ExecuteNonQuery

  • 执行不返回任何数据的原始 SQL 查询
  • 返回受影响的行数
  • 可选的查询参数化
  • 可选的命令类型和命令超时
  • 支持数据库事务

FromSqlQuery<T>

  • 执行返回数据的原始 SQL 查询
  • 将返回的数据行映射到给定的类型 T
    • 手动映射数据
    • 自动映射数据
  • 可选的查询参数化。
  • 可选的命令类型和命令超时

FromSqlRaw<T>

  • 内置 FromSqlRaw 的通用包装器

ExecuteScalar

执行查询，并返回查询返回的结果集中的第一行的第一个列。其他列或行将被忽略。

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

namespace EfCoreHelper.Database.Core
{
    public static class EfSqlHelper
    {
        public static DbTransaction GetDbTransaction(this IDbContextTransaction source)
        {
            return (source as IInfrastructure<DbTransaction>).Instance;
        }
        
        public static object ExecuteScalar(this DbContext context, string sql, 
        List<DbParameter> parameters = null, 
        CommandType commandType = CommandType.Text, 
        int? commandTimeOutInSeconds = null)
        {
            Object value = ExecuteScalar(context.Database, sql, parameters, 
                                         commandType, commandTimeOutInSeconds);
            return value;
        }

        public static object ExecuteScalar(this DatabaseFacade database, 
        string sql, List<DbParameter> parameters = null, 
        CommandType commandType = CommandType.Text, 
        int? commandTimeOutInSeconds = null)
        {
            Object value;
            using (var cmd = database.GetDbConnection().CreateCommand())
            {
                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Open();
                }
                cmd.CommandText = sql;
                cmd.CommandType = commandType;
                if (commandTimeOutInSeconds != null)
                {
                    cmd.CommandTimeout = (int)commandTimeOutInSeconds;
                }
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }
                value = cmd.ExecuteScalar();
            }
            return value;
        }
    }
}

在提取方法中，我们使用了 ADO.NET 功能。从 Ef DbContext 的数据库对象，我们访问底层 DB 连接对象并从中创建 Db 命令。然后，将所有必需的参数（如 SQL、命令类型、SQL 参数、使用现有 DB 事务以及可选的命令超时）分配给新创建的命令。最后，调用 ExecuteScalar() 来执行原始 SQL 查询。

int count = (int)Db.ExecuteScalar
(
    "SELECT COUNT(1) FROM Users WHERE Name=@paramName",
    new List<DbParameter>() { new SqlParameter("@paramName", user.Name) }
);

ExecuteNonQuery

针对连接执行 Transact-SQL 语句，并返回受影响的行数。

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

namespace EfCoreHelper.Database.Core
{
    public static class EfSqlHelper
    {
        public static DbTransaction GetDbTransaction(this IDbContextTransaction source)
        {
            return (source as IInfrastructure<DbTransaction>).Instance;
        }
        
        public static int ExecuteNonQuery(this DbContext context, string command, 
        List<DbParameter> parameters = null, 
        CommandType commandType = CommandType.Text, 
        int? commandTimeOutInSeconds = null)
        {
            int value = ExecuteNonQuery(context.Database, command, 
                        parameters, commandType, commandTimeOutInSeconds);
            return value;
        }

        public static int ExecuteNonQuery(this DatabaseFacade database, 
               string command, List<DbParameter> parameters = null, 
               CommandType commandType = CommandType.Text, 
               int? commandTimeOutInSeconds = null)
        {
            using (var cmd = database.GetDbConnection().CreateCommand())
            {
                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Open();
                }
                var currentTransaction = database.CurrentTransaction;
                if (currentTransaction != null)
                {
                    cmd.Transaction = currentTransaction.GetDbTransaction();
                }
                cmd.CommandText = command;
                cmd.CommandType = commandType;
                if (commandTimeOutInSeconds != null)
                {
                    cmd.CommandTimeout = (int)commandTimeOutInSeconds;
                }
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }
                return cmd.ExecuteNonQuery();
            }
        }
    }
}

提取方法与前一种非常相似。从 DbContext 的数据库对象创建 Db 命令。然后，将所有必需的参数（如 SQL、命令类型、SQL 参数、使用现有 DB 事务以及可选的命令超时）分配给命令。最后，调用 ExecuteNonQuery() 来执行原始 SQL 查询。

Db.ExecuteNonQuery("DELETE FROM Users WHERE Id < @paramId", new List<DbParameter>() 
                  { new SqlParameter("@paramId", user.Id) });

使用事务

Exception error = null;
using (var tran = Db.Database.BeginTransaction())
{
    try
    {
        Db.ExecuteNonQuery("UPDATE Users SET Email = 
           @paramEmail WHERE Id = @paramId", new List<DbParameter>() 
           { new SqlParameter("@paramEmail", newEmailOfOldUser), 
             new SqlParameter("@paramId", oldUser.Id) });
        Db.ExecuteNonQuery("UPDATE Users SET Email = 
           @paramEmail WHERE Id = @paramId", new List<DbParameter>() 
           { new SqlParameter("@paramEmail", newEmailOfUser), 
             new SqlParameter("@paramId", user.Id) });
        tran.Commit();
    }
    catch (Exception ex)
    {
        error = ex;
        tran.Rollback();
    }
}

使用事务范围

Exception error = null;
using (var scope = new TransactionScope())
{
    try
    {
        Db.ExecuteNonQuery("UPDATE Users SET Email = 
           @paramEmail WHERE Id = @paramId", new List<DbParameter>() 
           { new SqlParameter("@paramEmail", newEmailOfOldUser), 
             new SqlParameter("@paramId", oldUser.Id) });
        Db.ExecuteNonQuery("UPDATE Users SET Email = @paramEmail WHERE Id = @paramId", 
        new List<DbParameter>() { new SqlParameter("@paramEmail", newEmailOfUser), 
        new SqlParameter("@paramId", user.Id) });
        scope.Complete();
    }
    catch (Exception ex)
    {
        error = ex;
    }
}

FromSqlQuery<T>

创建一个将返回给定泛型类型元素的原始 SQL 查询。在旧版 Entity Framework 中，有 Database.SqlQuery<T>，它可以执行类似的操作，但在新版本/核心版中已被删除。现在，这种泛型类型映射可以通过两种方式完成：

  • 手动映射数据
  • 自动映射数据
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;

namespace EfCoreHelper.Database.Core
{
    public static class EfSqlHelper
    {
        private class PropertyMapp
        {
            public string Name { get; set; }
            public Type Type { get; set; }
            public bool IsSame(PropertyMapp mapp)
            {
                if (mapp == null)
                {
                    return false;
                }
                bool same = mapp.Name == Name && mapp.Type == Type;
                return same;
            }
        }
        
        public static DbTransaction GetDbTransaction(this IDbContextTransaction source)
        {
            return (source as IInfrastructure<DbTransaction>).Instance;
        }        
        
        public static IEnumerable<T> FromSqlQuery<T>
        (this DbContext context, string query, List<DbParameter> parameters = null, 
         CommandType commandType = CommandType.Text, 
         int? commandTimeOutInSeconds = null) where T : new()
        {
            return FromSqlQuery<T>(context.Database, query, parameters, 
                                   commandType, commandTimeOutInSeconds);
        }

        public static IEnumerable<T> FromSqlQuery<T>
               (this DatabaseFacade database, string query, 
                List<DbParameter> parameters = null, 
                CommandType commandType = CommandType.Text, 
                int? commandTimeOutInSeconds = null) where T : new()
        {
            const BindingFlags flags = BindingFlags.Public | 
            BindingFlags.Instance | BindingFlags.NonPublic;
            List<PropertyMapp> entityFields = (from PropertyInfo aProp 
                                               in typeof(T).GetProperties(flags)
                                               select new PropertyMapp
                                               {
                                                   Name = aProp.Name,
                                                   Type = Nullable.GetUnderlyingType
                                          (aProp.PropertyType) ?? aProp.PropertyType
                                               }).ToList();
            List<PropertyMapp> dbDataReaderFields = new List<PropertyMapp>();
            List<PropertyMapp> commonFields = null;

            using (var command = database.GetDbConnection().CreateCommand())
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                var currentTransaction = database.CurrentTransaction;
                if (currentTransaction != null)
                {
                    command.Transaction = currentTransaction.GetDbTransaction();
                }
                command.CommandText = query;
                command.CommandType = commandType;
                if (commandTimeOutInSeconds != null)
                {
                    command.CommandTimeout = (int)commandTimeOutInSeconds;
                }
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters.ToArray());
                }
                using (var result = command.ExecuteReader())
                {
                    while (result.Read())
                    {
                        if (commonFields == null)
                        {
                            for (int i = 0; i < result.FieldCount; i++)
                            {
                                dbDataReaderFields.Add(new PropertyMapp 
                                { Name = result.GetName(i), 
                                  Type = result.GetFieldType(i) });
                            }
                            commonFields = entityFields.Where
                            (x => dbDataReaderFields.Any(d => 
                             d.IsSame(x))).Select(x => x).ToList();
                        }

                        var entity = new T();
                        foreach (var aField in commonFields)
                        {
                            PropertyInfo propertyInfos = 
                                    entity.GetType().GetProperty(aField.Name);
                            var value = (result[aField.Name] == DBNull.Value) ? 
                                null : result[aField.Name]; //if field is nullable
                            propertyInfos.SetValue(entity, value, null);
                        }
                        yield return entity;
                    }
                }
            }
        }

        public static IEnumerable<T> FromSqlQuery<T>
        (this DbContext context, string query, Func<DbDataReader, T> map, 
        List<DbParameter> parameters = null, CommandType commandType = CommandType.Text, 
        int? commandTimeOutInSeconds = null)
        {
            return FromSqlQuery(context.Database, query, map, parameters, 
                                commandType, commandTimeOutInSeconds);
        }

        public static IEnumerable<T> FromSqlQuery<T>
        (this DatabaseFacade database, string query, Func<DbDataReader, T> map, 
        List<DbParameter> parameters = null, 
        CommandType commandType = CommandType.Text, 
        int? commandTimeOutInSeconds = null)
        {
            using (var command = database.GetDbConnection().CreateCommand())
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                var currentTransaction = database.CurrentTransaction;
                if (currentTransaction != null)
                {
                    command.Transaction = currentTransaction.GetDbTransaction();
                }
                command.CommandText = query;
                command.CommandType = commandType;
                if (commandTimeOutInSeconds != null)
                {
                    command.CommandTimeout = (int)commandTimeOutInSeconds;
                }
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters.ToArray());
                }
                using (var result = command.ExecuteReader())
                {
                    while (result.Read())
                    {
                        yield return map(result);
                    }
                }
            }
        }
    }
}

模型

public class UserModel
{
    public string Name { get; set; }
    public string Email { get; set; }
    public bool? IsDeleted { get; set; }
}

表格

DROP TABLE IF EXISTS [dbo].[Users]
GO
CREATE TABLE [dbo].[Users](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NULL,
    [IsDeleted] [bit] NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [CreatedBy] [nvarchar](max) NOT NULL,
    [ModifiedOn] [datetime2](7) NULL,
    [ModifiedBy] [nvarchar](max) NULL
)

这里，Name、Email 和 IsDeleted 同时存在于 C# 模型和数据库表中。数据类型也相似。

手动映射

我们可以使用列索引或列名进行手动映射。

使用索引
List<UserModel> usersInDb = Db.FromSqlQuery
(
    "SELECT Name, Email FROM Users WHERE Name=@paramName",
    x => new UserModel 
    { 
        Name = (string)x[0], 
        Email = (string)x[1] 
    },
    new List<DbParameter>() { new SqlParameter("@paramName", user.Name) }
)
.ToList();
使用列名
List<UserModel> usersInDb = Db.FromSqlQuery
(
    "SELECT Name, Email FROM Users WHERE Name=@paramName",
    x => new UserModel 
    { 
        Name = x["Name"] is DBNull ? "" : (string)x["Name"], 
        Email = x["Email"] is DBNull ? "" : (string)x["Email"] 
    },
    new List<DbParameter>() { new SqlParameter("@paramName", user.Name) }
)
.ToList();

自动映射

映射过程取决于类属性名、数据类型与列名、以及数据类型。此自动映射是通过反射完成的。因此，最好不要使用此方法选择非常大的数据集。

List<UserModel> usersInDb = Db.FromSqlQuery<UserModel>
(
    "SELECT Name, Email, IsDeleted FROM Users WHERE Name=@paramName",
    new List<DbParameter>() { new SqlParameter("@paramName", user.Name) }
)
.ToList();

FromSqlRaw<T>

这是内置 FromSqlRaw<T> 的通用包装器。

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

namespace EfCoreHelper.Database.Core
{
    public static class EfSqlHelper
    {
        public static IQueryable<TSource> FromSqlRaw<TSource>
        (this DbContext db, string sql, params object[] parameters) 
         where TSource : class
        {
            var item = db.Set<TSource>().FromSqlRaw(sql, parameters);
            return item;
        }
    }
}

TSource 应作为 DbSet<TSource> 包含在 DbContext 中。

使用扩展方法
List<User> usersInDb = Db.FromSqlRaw<User>
(
    "SELECT * FROM Users WHERE Name=@paramName",
    new SqlParameter("@paramName", user.Name)
)
.ToList();

限制

  • 存储过程未经测试，但应类似于 EXEC sp_name 或使用命令类型 CommandType.StoredProcedure。
  • FromSqlQuery<T> 的自动映射是通过反射完成的。对于大型数据集，可能会遇到性能问题。
  • 避免将 FromSqlQuery<T> 与其他 IEnumerable<T> 在 Linq 中进行连接。
  • 无论我们是否调用 Db.SaveChanges()，ExecuteNonQuery 或 FromSqlQuery<T> SQL 语句都会立即执行。
  • ExecuteSqlRaw 或 FromSqlRaw<T> 也会立即执行。
  • 已在 **SQL Server** 和 **Oracle** 上测试

参考文献

关于代码示例

  • Visual Studio 2022 解决方案
  • ASP.NET 6
  • EF Core 6
  • 此示例也在 Core 5 中进行了测试

**Database.Test** 是一个非常有趣的单元测试项目。请更改 appsettings.json 中的连接字符串。在数据库中创建 Users 表，请查看 **Database.Application** 项目的 db.sql。请检查/运行 EfSqlHelperTests.cs 的单元测试。

{
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source=.\\SQLEXPRESS;Initial Catalog=Cup;Integrated Security=True"
  }
}

DROP TABLE IF EXISTS [dbo].[Users]
GO
CREATE TABLE [dbo].[Users](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NULL,
    [IsDeleted] [bit] NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [CreatedBy] [nvarchar](max) NOT NULL,
    [ModifiedOn] [datetime2](7) NULL,
    [ModifiedBy] [nvarchar](max) NULL
)

历史

  • 2022 年 8 月 10 日：初始版本
© . All rights reserved.