在使用 EFCore 时,虽然绝大多数时候我们只需要写 C# 代码,让 EFCore 自动生成 SQL 语句就行了。但有些时候,我们可能需要执行自己写的 SQL 脚本,或出于性能原因,或出于已有 SQL 脚本太复杂的原因。EFCore 提供了三种执行 SQL 脚本的方法。

ExecuteSqlInterpolated

第一种是 DbContext.Database.ExecuteSqlInterpolated(Async) 方法。这个方法用于执行非查询的 SQL 脚本,因为它并不会获取脚本执行的结果,当然如果 SQL 脚本有语法错误还是会抛出异常的。使用的方法很简单,只需要在参数中传入 SQL 语句即可:

string title = "红楼梦";
double price = 8;
DateTime time = new DateTime(1999, 9, 9);
await ctx.Database.ExecuteSqlInterpolatedAsync(
    $"INSERT INTO T_Books (Title, Price, Time) VALUES('{title}',{price},{time})");

这里使用字符串插值语法并不只是为了方便,实际上,ExecuteSqlInterpolated 方法的参数类型是 FormattableString。当我们使用字符串插值语法传入 FormattableString 类型时,对应的插值会自动转换为 FormattableString 的 Argument,而 EFCore 会自动将这些 Argument 设置为 SQL 语句的参数。

实际上生成的 SQL 语句是(省略了参数定义):

INSERT INTO T_Books (Title, Price, Time) VALUES('@p0',@p1,@p2)

这样的设计可以防止 SQL 注入的问题。而如果先生成 string 纯文本再转换为 FormattableString 的话,可能会有 SQL 注入的危险。

FromSqlInterpolated

第二种是 DbSet.FromSqlInterpolated 方法,这个方法主要用于查询类的 SQL 脚本,即执行后需要获取查询结果的。EFCore 会自动将查询结果映射为 C# 对象并返回。FromSqlInterpolated 方法返回 IQueryable<T> 类型,其中 T 与具体的 DbSet 实体类型相同。

double maxprice = 10;
var books = ctx.Books.FromSqlInterpolated($"SELECT * FROM T_Books WHERE Price < {maxprice}");
foreach (var book in books)
{
    Console.WriteLine($"{book.Title} {book.Price}");
}

需要注意,由于 IQueryable 的延迟执行特点,这个手写的 SQL 脚本依旧会等到迭代的时候才查询,在迭代之前,我们仍可以对 IQueryable 增加条件,EFCore 会将我们手写的 SQL 语句与增加的条件进行综合(通常,为了保证脚本符合预期,EFCore 会将我们手写的 SQL 语句当成一个子查询),生成新的 SQL 脚本。

这种方法的局限性在于它不能跨表查询,但可以使用 Include 方法获取关联数据。并且手写的 SQL 语句必须查询该实体的所有列,如果需要投影,则应在返回的 IQueryable 再使用 Select 方法,但这样生成的 SQL 脚本通常包含一个子查询,性能相对较差。

ADO.NET

第三种方法,其实已经和 EFCore 没什么关系了:由于 EFCore 底层就是 ADO.NET,我们可以绕过 EFCore 直接获取 ADO.NET 对象查询。具体来说,我们可以通过 DbContext.Database.GetDbConnection 方法获取 DbConnection 对象,之后就可以任意操作了。这种方法的好处在于它几乎没有局限性,可以执行任何类型的 SQL 脚本。缺点也很明显:它已经完全脱离了 ORM,我们无法享受到 EFCore 带来的好处,意味着我们需要手动解析 SQL 返回结果。

var conn = ctx.Database.GetDbConnection();
if (conn.State != System.Data.ConnectionState.Open)
{
    conn.Open();
}
using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "SELECT * FROM T_Books WHERE Price < 10";
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader.GetString(1) + " " + reader.GetDouble(3));
        }

    }
}