【EFCore】笔记7 查看SQL脚本

我们知道，EFCore 将我们的 C# 对象查询和修改语句在后台自动翻译为 SQL 语句然后交给数据库系统执行，那么如何查看 EFCore 生成的 SQL 语句呢？下面介绍几种方法。

Migration SQL

在 Update-Database 时，EFCore 会将当前数据库更新或回滚到程序员指定的 Migration，这也是通过生成 SQL 语句并执行而完成的。使用 Script-Migration 命令可以获取背后的 SQL 脚本。

在无参数的情况下，Script-Migration 生成从零开始到最新 Migration 的 SQL 语句，在控制台执行下面命令：

Script-Migration

EFCore 会生成一个脚本文件，类似这样：

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

BEGIN TRANSACTION;
GO

CREATE TABLE [T_Authors] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max) NULL,
    CONSTRAINT [PK_T_Authors] PRIMARY KEY ([Id])
);
GO

CREATE TABLE [T_Books] (
    [Id] bigint NOT NULL IDENTITY,
    [Title] nvarchar(50) NOT NULL,
    [AuthorId] int NOT NULL,
    [Time] datetime2 NOT NULL,
    [Price] float NOT NULL,
    CONSTRAINT [PK_T_Books] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_T_Books_T_Authors_AuthorId] FOREIGN KEY ([AuthorId]) REFERENCES [T_Authors] ([Id]) ON DELETE CASCADE
);
GO

CREATE INDEX [IX_T_Books_AuthorId] ON [T_Books] ([AuthorId]);
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20220804153349_init', N'5.0.17');
GO

COMMIT;
GO

BEGIN TRANSACTION;
GO

CREATE TABLE [T_Stores] (
    [Id] uniqueidentifier NOT NULL,
    [Name] nvarchar(max) NULL,
    CONSTRAINT [PK_T_Stores] PRIMARY KEY ([Id])
);
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20220805071641_AddStore', N'5.0.17');
GO

COMMIT;
GO

由于我用的是 SQLServer，所以这里生成的是 SQLServer 的脚本，从脚本可以看出它先创建了 __EFMigrationsHistory 这个表（用于储存当前数据库的 Migration 状态）以及各个实体的表，所有的实体配置都被翻译为了 SQL 语句。这个 SQL 脚本可以拿到 SQLServer 里执行，和我们执行 Update-Database 的效果是一样的。

为什么有了 Update-Database 还要生成 SQL 脚本呢？在实际项目中，程序员可能是在开发服务器而非生产服务器中开发的，这个时候 Update-Database 命令可以在开发服务器中使用，但到最终提交到生成服务器前，可能需要交给 DBA 审核，而程序员也不一定拥有生产服务器的权限，那么自然不可能在生产服务器中执行 Update-Database。所以需要 Script-Migration 命令来生成 SQL 脚本，在生产服务器中执行，这样才能保证安全性。

但生产服务器中自然不需要从零开始创建数据库，我们需要从当前数据库状态到最新的 Migration 的 SQL 脚本，这个时候就需要给命令增加参数了。

Script-Migration <OldMigrationName> <NewMigrationName>

这个命令从生成一个从名为 <OleMigrationName> 的 Migration 到名为<NewMigrationName> 的 Migration 的 SQL 脚本。也可以省略后面的 <NewMigrationName> 参数，这样默认生成到最新 Migration 的 SQL 脚本。

注意，Script-Migraion 只是生成脚本，并不会执行它。

对象操作 SQL

在对数据库进行增删查改时，EFCore 也会将我们的 C# 代码翻译为 SQL 语句，如何获取呢？有几种方法，如果是 SQLServer，可以使用 SQLServer Profiler 工具，这个工具会监视该数据库所有执行的脚本，EFCore 生成的自然可以查看了。例如下面的 C# 代码：

var book = ctx.Books.Single(e => e.Title == "西游记");
book.Price *= 0.8;
ctx.SaveChanges();

在 SQLServer Profiler 里查看 SQL 语句：

SELECT TOP(2) [t].[Id], [t].[AuthorId], [t].[Price], [t].[Time], [t].[Title]
FROM [T_Books] AS [t]
WHERE [t].[Title] = N'西游记'

exec sp_executesql N'SET NOCOUNT ON;
UPDATE [T_Books] SET [Price] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;

',N'@p1 bigint,@p0 float',@p1=1,@p0=160

这里 EFCore 生成了两句 SQL，第一句是 SELECT 语句，对应 C# 代码里的 Single 方法，而第二句是一个 UPDATE 语句，对应 C# 代码里修改 Price 的那一行。

当然，EFCore 也并不一定要使用 SQLServer，如果是别的数据库，比如 MySQL 的话，没有工具的话怎么查看 SQL 语句呢？这个时候就要使用代码来获取了。在 EFCore 执行过程中，会顺带输出日志，在这个日志里会显示出生成了什么 SQL 语句，但默认情况下这个日志我们是看不到的。如果需要，那么可以让 EFCore 增加日志输出，可以使用 LoggerFactory。在DbContext 作如下设置：

public static readonly ILoggerFactory logger = LoggerFactory.Create(builder => builder.AddConsole());

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
     string connStr = @"Data source=localhost;database=Test;Integrated Security=SSPI;";
     base.OnConfiguring(optionsBuilder);
     optionsBuilder.UseSqlServer(connStr);
     //此处使用 LoggerFactory
     optionsBuilder.UseLoggerFactory(logger);

 }

这个时候再执行刚刚的 C# 代码就能看到 SQL 语句了：

使用 LoggerFactory 的方法是比较推荐的，毕竟 Logger 可以通过依赖注入来获取，而且这样做也不会受限于数据库。还有一种方法来输出日志的，就是通过 optionsBuilder.LogTo 方法，它接受一个委托参数，这个委托有一个字符串参数，EFCore 会将生成的日志传入该委托。例如：

optionsBuilder.UseSqlServer(connStr);
//optionsBuilder.UseLoggerFactory(logger);
optionsBuilder.LogTo(s => Console.WriteLine(s));

这个时候会通过 Console.WriteLine 输出日志，但是这里得到的日志是完整日志，很多细节都输出了，其中一部分：

dbug: 2022/8/5 18:12:08.096 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [t].[Id], [t].[AuthorId], [t].[Price], [t].[Time], [t].[Title]
      FROM [T_Books] AS [t]
      WHERE [t].[Title] = N'西游记'
info: 2022/8/5 18:12:08.140 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (48ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [t].[Id], [t].[AuthorId], [t].[Price], [t].[Time], [t].[Title]
      FROM [T_Books] AS [t]
      WHERE [t].[Title] = N'西游记'

除了使用日志来查看 SQL 语句，还可以通过 IQueryable 接口，因为 IQueryable 接口有一个叫 ToQueryString 的扩展方法，可以输出 SQL 语句，Where 语句就是返回 IQueryable 的。比如：

IQueryable<Book> book = ctx.Books.Where(e => e.Title == "西游记");
Console.WriteLine(book.ToQueryString());

这样的方法只能用于查看查询操作的 SQL 语句，而修改、删除是没有办法的。在开发中，使用这个方法可以便捷地获取一个查询操作的 SQL 语句，而且并没有在数据库中执行它。

Migration SQL

对象操作 SQL

Leave a Reply Cancel reply