MassTransit SQL Server传输最小配置搭建报错，请求协助

问题背景 #

作为MassTransit新手，我在标准WebApi项目中尝试以最小配置集成SQL Server传输作为消息总线，还未添加发布控制器就遇到启动错误。我的Program.cs配置如下：

using MassTransit;
var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var connectionString = @"Server=(localdb)\MSSQLLocalDB;Integrated Security=true;Database=MtPlayground;";

builder.Services.AddOptions<SqlTransportOptions>()
    .Configure(options =>
    {
        options.ConnectionString = connectionString;
    });

builder.Services.AddSqlServerMigrationHostedService(x =>
{
    x.CreateDatabase = true;
    x.CreateInfrastructure = true;
});

builder.Services.AddMassTransit(x =>
{
    x.AddSqlMessageScheduler();
    x.UsingSqlServer((context, cfg) =>
    {
        cfg.UseSqlMessageScheduler();
        cfg.ConfigureEndpoints(context);
    });
});

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

启动后出现以下错误：

info: MassTransit.SqlTransport.SqlTransportMigrationHostedService[0]
      MassTransit SQL Transport creating database MtPlayground
fail: Microsoft.Extensions.Hosting.Internal.Host[11]
      Hosting failed to start
      Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'WITH'.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
         at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
         at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
         at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
         at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
         at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
         at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteReaderAsync>b__201_1(IAsyncResult asyncResult)
         at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
      --- End of stack trace from previous location ---
         at Dapper.SqlMapper.ExecuteScalarImplAsync[T](IDbConnection cnn, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 1241
         at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateDatabaseIfNotExist(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1951
         at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateDatabaseIfNotExist(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1959
         at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateDatabaseIfNotExist(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1959
         at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateDatabase(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1870
         at MassTransit.SqlTransport.SqlTransportMigrationHostedService.StartAsync(CancellationToken cancellationToken) in /_/src/MassTransit/SqlTransport/SqlTransport/SqlTransportMigrationHostedService.cs:line 33
         at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token)
         at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation)
      ClientConnectionId:de8999d9-3b4e-41f2-a3a4-c6638a1c7c97
      Error Number:156,State:1,Class:15

问题分析 #

错误提示Incorrect syntax near the keyword 'WITH'的核心原因有两点：

1. LocalDB版本过低：MassTransit创建数据库的脚本使用了SQL Server 2019+的语法特性，旧版本LocalDB（如2017及更早）不支持这些语法。
2. 配置冗余且不匹配：单独配置SqlTransportOptions与迁移服务的配置未对齐，可能导致连接字符串传递异常。

修复方案 #

1. 升级LocalDB版本 #

安装SQL Server Express LocalDB最新版本（推荐2019或以上），确保其支持现代SQL语法特性。

2. 简化并修正配置 #

调整Program.cs配置，直接在MassTransit和迁移服务中明确指定连接字符串，避免配置冗余：

using MassTransit;
var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var connectionString = @"Server=(localdb)\MSSQLLocalDB;Integrated Security=true;Database=MtPlayground;";

// 直接在迁移服务配置中指定连接字符串
builder.Services.AddSqlServerMigrationHostedService(options =>
{
    options.ConnectionString = connectionString;
    options.CreateDatabase = true;
    options.CreateInfrastructure = true;
});

builder.Services.AddMassTransit(x =>
{
    x.AddSqlMessageScheduler();
    x.UsingSqlServer((context, cfg) =>
    {
        cfg.Host(connectionString); // 直接配置SQL Server连接
        cfg.UseSqlMessageScheduler();
        cfg.ConfigureEndpoints(context);
    });
});

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

3. 手动创建数据库（可选） #

如果升级LocalDB后仍有问题，可先手动在LocalDB中创建MtPlayground数据库，再修改迁移服务配置：

builder.Services.AddSqlServerMigrationHostedService(options =>
{
    options.ConnectionString = connectionString;
    options.CreateDatabase = false; // 手动创建数据库后禁用自动创建
    options.CreateInfrastructure = true;
});

验证 #

启动应用后，MassTransit会自动创建消息队列、调度器所需的基础设施表，不再出现语法错误，基础消息总线即可正常工作。

内容的提问来源于stack exchange，提问作者Y0rk1e