目录
背景
实体框架核心允许我们在使用关系数据库时下拉到原始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", "[email protected]"),
- 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
- IDbContextTransaction to DbTransaction
- 要列出的DbDataReader
- DbDataReader列名称和类型
关于代码示例
- 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
标签:Core,Name,SQL,EF,List,using,new,null,public From: https://www.cnblogs.com/wl-blog/p/17213870.html