【EF Core】 EF Core 原生SQL查询深度解析-CSDN博客

本文链接：https://blog.csdn.net/Arabys/article/details/148137073

文章目录

前言
一、基于实体的 SQL 语句查询
二、执行非查询SQL
三、查询标量（非实体）类型的SQL 语句
总结

前言

在EF Core中使用原生SQL执行查询本身不是一种常见的使用案例。但是当我们面对要去执行数据报表这类非常复杂的查询逻辑，其中来回关联了很多张表，并且执行过程中创建了很多临时表。这种查询是很难用 LINQ 方法语法来表示的，即使可能能使用和SQL语句类似查询语法，但也会非常复杂。另外一种就是EF Core翻译出来的SQL执行效率很低，也会碰到使用原生SQL执行查询这种场景。

在使用关系型数据库时，EF Core 允许直接使用 SQL 查询。当所需的查询无法通过 LINQ 表达式构建，或者某个 LINQ 查询会导致 EF 生成低效的 SQL 时，SQL 查询就会非常有用。SQL 查询可以返回常规实体类型，也可以返回模型中包含的无键实体类型。

提示：以下是本篇文章正文内容，下面案例可供参考

一、基于实体的 SQL 语句查询

1.1 通过FromSql实现基于实体的 SQL 语句查询

使用FromSql执行原生SQL语句查询，FromSql 只能直接在 DbSet 上使用，也就是SQL语句必须有一个对应的实体。也就是说

SQL语句查询必须返回实体类型对应数据库表的所有列
结果集中的列名必须与属性映射到的列名称匹配
只能单表查询，不能使用Join语句进行关联查询。但是可以在查询后面使用Include()来进行关联数据的获取

using (ApplicationDbContext dbContext = new ApplicationDbContext())
{
    const int age = 21;
    var students = dbContext.Students.FromSql($"select * from T_Student where T_Student.Age > {age}");
    foreach (var item in students)
	{
        Console.WriteLine(item.Name);
	}
}

1.2 通过FromSql实现基于实体的储存过程 / 视图查询

执行储存过程与执行SQL语句类型。

var user = new SqlParameter("Age", "21");

var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetHighestGPAForStudent {user}")
    .ToListAsync();

1.3 FromSql在底层做了些什么

另外我们发现FromSql这个方法所接收的参数是一个FormattableString类型的字符串，而不是普通的String。如果去除示例代码FromSql方法里字符串的$，代码会报错。
当我们使用带插值的字符串（如 $“select * from T_Student”）并将其赋值给 FormattableString 类型的变量时，C# 会自动将其转换为 FormattableString 对象。

在了解为什么EF Core团队要如此设计时，我们先来观察一下FormattableString对象。
FormattableString 包含两个关键的东西：

Format属性：获取格式字符串（例如 “Hello, {0}!”）
GetArguments方法：获取插值表达式的参数数组

FormattableString 源码

namespace System
{
    public abstract class FormattableString : IFormattable
	{
	    /// <summary>
	    /// The composite format string.
	    /// </summary>
	    [StringSyntax(StringSyntaxAttribute.CompositeFormat)]
	    public abstract string Format { get; }
	
	    /// <summary>
	    /// Returns an object array that contains zero or more objects to format. Clients should not
	    /// mutate the contents of the array.
	    /// </summary>
	    public abstract object?[] GetArguments();   
	}
}

通过观察FromSql源码，我们发现该方法是DbSet< TEntity>的扩展方法，接受一个FormattableString 类型的参数。在方法内部，分别获取FormattableString 的格式字符串和插值表达式的参数数组。
FromSql源码

public static IQueryable<TEntity> FromSql<TEntity>(
    this DbSet<TEntity> source,
    [NotParameterized] FormattableString sql)
    where TEntity : class
{
    Check.NotNull(sql, nameof(sql));
    Check.NotEmpty(sql.Format, nameof(source));

    var queryableSource = (IQueryable)source;
    return queryableSource.Provider.CreateQuery<TEntity>(
        GenerateFromSqlQueryRoot(
            queryableSource,
            sql.Format,
            sql.GetArguments()));
}

也就是说最终FromSql是对格式字符串和插值表达式的参数数组进行整合，创建一个全新的SQL语句，最后发送到数据库去执行。这一点我们通过观察EF Core实际执行的SQL语句也能窥见一二

SELECT s.Id, s.Age, s.ClassId, s.Name
FROM select * from T_Student where T_Student.Age > {0})

1.4 参数化的好处

通过FormattableString 的格式字符串和插值表达式的参数数组，EFCore在生成的SQL语句中参数名称插入被到指定了 {0} 占位符的位置。这使得 FromSql 可以免受 SQL 注入攻击，可以将值高效且正确地发送到数据库。

SQL 注入的主要形式包括直接将代码插入到与 SQL 命令串联在一起并使其得以执行的用户输入变量。一种间接的攻击会将恶意代码注入要在表中存储或作为元数据存储的字符串。在存储的字符串随后串连到一个动态 SQL 命令中时，将执行该恶意代码。
注入过程的工作方式是提前终止文本字符串，然后追加一个新的命令。由于插入的命令可能在执行前追加额外字符串，因此攻击者将用批注标记 – 来标记终止注入的字符串。执行时，此后的文本将被忽略。

如果上文代码示例中，我们直接通过拼接字符串的方式接受Age变量，假设程序里的Age变量被设置为string类型，且不法分子成功注入了SQL，将Age对象设置为";delete from T_Student “。这将对系统造成严重的损害。而采用FromSql这种参数化的方案时。”;delete from T_Student "只会被当作变量的值，而不是作为一个单独的sql语句执行。

1.5 LINQ组合

可以使用 LINQ 运算符在初始 SQL 查询的基础上进行组合；EF Core 会将 SQL 视为子查询，在数据库中以它为基础进行组合。我们观察到上位示例代码里执行完FromSql的students对象，其类型是IQueryable类型，IQueryable其一大特性就是查询的时候复用。下面的示例使用 SQL 查询。然后，使用 LINQ 进行Include预加载数据，并返回一个新的对象，从而对其进行组合。

using (ApplicationDbContext dbContext = new ApplicationDbContext())
{
    const int age = 21;
    var students = dbContext.Students.FromSql($"select * from T_Student where T_Student.Age > {age}")
        .Include(x => x.Class).Select(e => new
        {
            Name = e.Name,
            Age = e.Age,
            ClassName = e.Class.ClassName
        }).ToList();
    foreach (var item in students)
    {
        Console.WriteLine($"Name:{item.Name}-Age:{item.Age}-ClassName:{item.ClassName}");
    }
}

1.6 FromSqlInterpolated与FromRaw

FromSqlInterpolated：与任何接受 SQL 的 API 一样，必须参数化任何用户输入，以防止 SQL 注入攻击。可以在 SQL 查询字符串中包含内插参数占位符。提供的任何内插参数值将自动转换为 DbParameter。FromSqlInterpolated与FromSql类型，EF Core 7.0 中引入了 FromSql。建议新版本的项目里使用FromSql。
FromRaw：FromRaw接受一个字符串，可以直接将变量数据内插到 SQL 字符串中，而不使用数据库参数。使用 FromSqlRaw 时要非常小心，始终确保值来自安全的源，或者经过正确清理。 SQL 注入攻击可能会为应用程序带来灾难后果。

二、执行非查询SQL

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

using (ApplicationDbContext dbContext = new ApplicationDbContext())
{
    const int age = 21;
    dbContext.Database.ExecuteSql($"update T_Student set Age = 22 where Age > {age}");
}

ExecuteSql 使用安全的参数化来防止 SQL 注入，就像 FromSql 一样，而 ExecuteSqlRaw 允许动态构造 SQL 查询，就像 FromSqlRaw 对查询所做的那样。

三、查询标量（非实体）类型的SQL 语句

使用 SqlQuery，你就可以通过 SQL 轻松查询非实体标量类型，而非强制定义在某一个实体上。SqlQuery与FromSql类似，也是接受一个FormattableString类型的字符串。

要使用SqlQuery，其EF Core 版本需要在7.0以上

public static IQueryable<TResult> SqlQuery<TResult>(
    this DatabaseFacade databaseFacade,
    [NotParameterized] FormattableString sql)
    => SqlQueryRaw<TResult>(databaseFacade, sql.Format, sql.GetArguments()!);

这里，我们使用SQL查询两张表，返回的字段分别属于两张表的部分列。这种作法必须指定明确的DTO。

using (ApplicationDbContext dbContext = new ApplicationDbContext())
{
    const int age = 21;
    var newStudents = await dbContext.Database
        .SqlQuery<NStudent>(@$"select T_Student.Name,T_Student.Age,T_Class.ClassName from T_Student 
            inner join T_Class on T_Student.ClassId = T_Class.Id")
    .ToListAsync();
}

class NStudent
{
    public String Name { get; set; }
    public Int32 Age { get; set; }
    public String ClassName { get; set; }
}