背景

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

现有选项

在实体框架核心中，有多个选项可用于运行原始SQL查询。要使用它们，我们需要安装Microsoft.EntityFrameworkCore.Relational和Microsoft.EntityFrameworkCore：

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

ExecuteSqlRaw

执行非查询 SQL。以下是一些insert、update和delete示例。参数化查询是可选的，如果需要，我们可以跳过它。

插入

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();

自定义数据库上下文扩展方法

下面是一些用于运行原始SQL的扩展方法的DbContext和DatabaseFacade对象。在Database.Core项目的帮助程序类 EfSqlHelper.cs 中，我们将找到列出的扩展方法。

ExecuteScalar

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

ExecuteNonQuery

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

FromSqlQuery<T>

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

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命令。然后将所有必需的参数分配给命令对象，如SQL、命令类型、SQL参数、使用现有数据库转换和可选命令超时到新创建的命令。最后，调用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参数、使用现有数据库转换以及命令的可选命令超时。最后，调用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查询，该查询将返回给定泛型类型的元素。在较旧的实体框架版本中，曾经执行类似操作的是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#模型和Db表中。数据类型也类似。

手动映射

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

使用索引

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();

局限性

存储过程未经过测试，但应像EXECsp_name或使用命令类型CommandType.StoredProcedure一样工作
FromSqlQuery<T>自动映射是使用反射完成的。可能会面临大型数据集的性能问题。
避免在Linq中加入FromSqlQuery<T>其他的IEnumerable<T>
ExecuteNonQuery或FromSqlQuery<T> SQL语句立即执行，无论我们是否调用Db.SaveChanges()
ExecuteSqlRaw或者FromSqlRaw<T>，立即执行
使用 SQL Server 和 Oracle 进行测试

引用

运行原始SQL
- [SOLVED] => Raw SQL Query without DbSet - Entity Framework Core
- Executing Raw SQL Queries using Entity Framework Core | Learn Entity Framework Core
IDbContextTransaction to DbTransaction
- Dapper Tutorial
要列出的DbDataReader
- c# - How can I easily convert DataReader to List<T>? - Stack Overflow
DbDataReader列名称和类型
- c# - Find the datatype of Field from DataReader object - Stack Overflow

关于代码示例

Visual Studio 2022 Solution
ASP.NET 6
EF Core 6
此示例也在Core 5中进行了测试

Database.Test 是一个有趣的单元测试项目。在appsettings.json中更改连接字符串。在db中创建用户表，检查项目 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
)

https://www.codeproject.com/Articles/5321286/Executing-Raw-SQL-Queries-using-Entity-Framework-C

转 https://blog.csdn.net/mzl87/article/details/128646754

posted @ 2023-03-14 09:53 dreamw 阅读(1651) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· EFCore——原生Sql语句（12）

· How to configure navigation property for custom SQL in EF Core 5如何在 EF Core 5 中为自定义 SQL 配置导航属性

· C++-研讨会（全）

· EF Core下怎么跑sql语句

· 【EF Core】EF Core 6.0 连接Sqlite3

阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）

使用EF Core 6执行原始SQL查询

背景

现有选项

ExecuteSqlRaw

插入

更新

删除

FromSqlRaw<T>

FromSqlInterpolated<T>

自定义数据库上下文扩展方法

ExecuteScalar

ExecuteNonQuery

FromSqlQuery<T>

FromSqlRaw<T>

ExecuteScalar

ExecuteNonQuery

与事务一起使用

与事务范围一起使用

FromSqlQuery<T>

模型

表

手动映射

使用索引

使用列名

自动映射

FromSqlRaw<T>

使用扩展方法

局限性

引用

关于代码示例

公告

搜索

积分与排名

随笔分类

随笔档案

文章档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论