EFCore基础之如何执行原生SQL语句

阅读本文你的收获：

1. 学习EF Core中自带的执行原生SQL语句的方法
2. 了解EF Core中自带的方法有什么限制？
3. 为EF Core编写一个扩展方法，使其突破限制，执行任何SQL语句

---

Entity Framework (EF) Core 是轻量化、可扩展、开源和跨平台版的常用 数据访问技术。EF Core 是一个对象关系映射程序 (O/RM)框架，这可以实现以下两点：

• 使 .NET 开发人员能够使用 C#的对象来处理数据库。
• 无需再像通常那样编写大部分数据访问代码。

一. 场景描述

使用EF core去操作数据库的时候，基本的增删改查、甚至两表联查等都是不需要去写SQL语句的，一来节约我们大量拼接SQL语句的时间，二来它可以根据使用数据库的不同，自动生成对应的SQL语句。本来O/RM的目的就是为了提高我们的开发效率。

但是在做一些报表功能时，需要写一个复杂的多表联查、分组查询，EF Core给我们自动生成的SQL语句，在执行时难免会消耗更多的执行时间。好在EF Core的开发者早就考虑到了这种对性能有极致要求的场景，所以给我们提供了一些方法，让我们直接执行原生的SQL语句。

二. EF Core中执行原生SQL

2.1 如何执行原生SQL查询语句

Entity Framework Core为DbSet<TEntity>提供了扩展方法:

• FromSqlRaw方法：执行原生SQL查询语句（select语句），如需传参，可用SqlParameter传参；
• FromSqlInterpolated方法：如果SQL语句字符串中用$插值来拼接参数，则使用该方法。这个方法能防止SQL注入。

注意： 如果是Like 模糊查询，建议用FromSqlRaw方法，因为FromSqlInterpolated方法会将特殊字符 like ，%等 给过滤掉。

例子：

//引用命名空间
using Microsoft.EntityFrameworkCore; //应用ef core的命名空间
//...
OADbContext _db;  //OADbContext是EF Core上下文对象,依赖注入该对象（略）

/// <summary>
/// 根据角色名称，模糊查询角色
/// </summary>
/// <returns></returns>
public List<Role> GetRolesByName(string roleName)
{
    string strsql = "select * from roles where RoleName like @para1";
    return _db.Roles.FromSqlRaw(strsql,
                       new SqlParameter("@para1", roleName) //传参
                     ).ToList() ;
}
//=====================
//再看一下 怎么用FromSqlInterpolated
public List<Role> GetRolesByName(string roleName)
{
   //用$""对字符串进行插值格式化
    string strsql = $"select * from roles where RoleName={roleName}";
    return _db.Roles.FromSqlInterpolated(strsql).ToList() ;
}

这仅适用于DbSet声明。下面Roles是一个DbSet，在DbContext中声明。

public class OADbContext: DbContext
{
    public DbSet<Role> Roles { get; set; }
}

2.2 如何执行增、删、改的命令SQL语句

Entity Framework Core为Database提供了两个扩展方法:

• ExecuteSqlRaw方法：执行原生SQL命令语句（insert 、delete、update语句），如果传参，可用SqlParameter传参；
• ExecuteSqlInterpolated方法：如果SQL语句字符串中用$插值来拼接参数，则调用该方法。这个方法能防止SQL注入。

//引用命名空间
using Microsoft.EntityFrameworkCore; //应用ef core的命名空间
//...
OADbContext _db;  //OADbContext是EF Core上下文对象,依赖注入该对象（略）

/// <summary>
/// 给角色添加权限（这里开启了事务）
/// </summary>
/// <param name="rid">角色Id</param>
/// <param name="pid">权限的Id：用逗号隔开的字符串</param>
/// <returns></returns>
public int AddRolePermission(int rid, string pids)
{
    using (var tran = _db.Database.BeginTransaction())
    {
        try
        {
            int affectRows = _db.Database.ExecuteSqlInterpolated(
                			$"delete from RolePermissions where RoleId ={rid}");

            var spid = pid.Split(',');
            foreach (var perId in spid)
			{
				affectRows += _db.Database.ExecuteSqlInterpolated(
					$"insert into RolePermissions values({rid},{perId})");
			}
			//提交
            tran.Commit();
            return affectRows; //返回受影响行数
        }
        catch (Exception ex)
        {
            //回滚
            tran.Rollback();
            return -1;
        }
    }
}

三. EF Core执行原生SQL语句的局限性

在EFCore下执行原生SQL查询语句的方法——FromSqlRaw和FromSqlInterpolated，不能查找部分列，只能查找全部列，而且只能单表查询，不能使用join联查，这是这两个方法的局限性。

而实际场景中，我们经常会处理一些报表等功能，查询语句往往要涉及到多个表，而且还会有一些分组查询之类的，所以这两个方法使用起来就会“捉襟见肘”。

这时候，我们可以将复杂的查询语句，创建成数据库视图，再将视图映射成实体类，并在DbContext上下文中添加对应的DbSet< T > 。再去应用FromSqlRaw和FromSqlInterpolated这两个方法。

	//复杂查询,统计每个部门人数
	select DeptName，Count(*) as UserCount from Users group by DeptName
	
	//需要创建一些不对应表的实体，这些不是实体，但在代码层面上它又是实体
	class DeptUser
	{
		public string DeptName{get;set;}
		public int UserCount {get;set;}
	}
	
	//上下文类声明DbSet<DeptUser>
	public class MyDbContext: DbContext
	{
	    public DbSet<DeptUser> DeptUsers { get; set; }
	}

注意：虽然EF Core是允许你这样去做的，但是非实体的DbSet，会使DbSet膨胀，视图过多，以后不好维护。所以不太建议这样做。

四. 使用EF Core的底层ADO.NET去执行SQL语句

我们可以为EF Core写一个扩展方法，利用它的底层ADO.NET去执行SQL语句，源码文件取名为EFCoreExtentions.cs，（完整版可在我资源中下载）

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public static class EFCoreExtentions
{
    /// <summary>
    /// 查询方法
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdType"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public static IEnumerable<dynamic> ExecuteSqlQuery(this DbContext dbContext, string cmdText, CommandType cmdType = CommandType.Text, params DbParameter[] parameters)
    {
        using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
        {
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (cmd.Connection.State != ConnectionState.Open)
            {
                cmd.Connection.Open(); //打开连接
            }
            //添加输入参数
            cmd.Parameters.AddRange(parameters);

            //执行命令，读取器读取数据
            using (var dataReader = cmd.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    IDictionary<string, object> row = new ExpandoObject(); //实例化一个动态可扩展对象
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        row.Add(dataReader.GetName(i), dataReader[i]);
                    }
                    yield return row;
                }
            }
        }
    }

    /// <summary>
    /// 执行增、删、改的命令
    /// </summary>
    /// <param name="dbContext"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdType"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public static int ExecuteNonQuery(this DbContext dbContext, string cmdText, CommandType cmdType = CommandType.Text,  params DbParameter[] parameters)
    {
        //1. 创建连接对象
        using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
        {
            //接下来把异常处理加入
            try
            {
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Open(); //打开连接
                }
                //处理输入参数
                cmd.Parameters.AddRange(parameters);

                //事务
                //cmd.Transaction = tran; 

                int result = cmd.ExecuteNonQuery();   //执行增删改命令
                return result;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}

使用以上扩展方法，执行一个复杂的查询语句：

OADbContext _db;  //OADbContext是EF Core上下文对象,依赖注入该对象（略）
// ...
var result = _db.ExecuteSqlQuery("select DeptName，Count(*) as UserCount from Users group by DeptName")

如何使用该扩展方法来执行存储过程呢？我们将在下篇文章中分享，传送门——EFCore基础之如何执行数据库存储过程

五. 使用Dapper来执行SQL语句

因为本篇讨论的是EF Core下执行原生SQL语句，所以我将在另外一篇专门讲Dapper的文章中分享。

---

本次分享就这么多，希望对你有帮助。欢迎多点赞+评论+关注。