EF Core调用SQL Server函数优化：如何避免额外SELECT查询？

问题背景 #

需要将以下SQL存储过程的INSERT逻辑转换为C#代码：

INSERT mytable WITH (ROWLOCK) (created_by, date_started)
VALUES (USER_NAME(), GETDATE())

最初的实现是在DbContext中新增两个异步方法分别获取数据库时间和当前用户，但会产生两次额外的SELECT查询，想要更优的实现方式。

最初的实现代码 #

DbContext中的方法

public async Task<DateTime> GetDatabaseDateTimeAsync()
{
    var dateTimeParameter = new SqlParameter("@date", SqlDbType.DateTime) { Direction = ParameterDirection.Output };
    await Database.ExecuteSqlRawAsync("SELECT @date = GETDATE()",dateTimeParameter);
    DateTime.TryParse( dateTimeParameter.Value.ToString(), out var returnDateTime);
    return returnDateTime;
}

public async Task<string> GetDatabaseUserAsync()
{
    var userNameParameter = new SqlParameter("@user", SqlDbType.VarChar,30) { Direction = ParameterDirection.Output };
    await Database.ExecuteSqlRawAsync("SELECT @user = USER_NAME()",userNameParameter);
    return userNameParameter.Value.ToString();
}

调用代码

var mytable = new MyTable
              {
                  CreatedBy = await _dbContext.GetDatabaseUserAsync(),
                  DateStarted = await _dbContext.GetDatabaseDateTimeAsync()
              };

_dbContext.MyTable.Add(mytable);
await _dbContext.SaveChangesAsync();

优化方案 #

方案1：一次性获取两个值，减少查询次数 #

在DbContext中新增一个方法，一次查询同时获取USER_NAME()和GETDATE()，仅产生一次SQL查询：

public async Task<(string UserName, DateTime CurrentDate)> GetDbUserAndDateAsync()
{
    var userParam = new SqlParameter("@user", SqlDbType.VarChar, 30) { Direction = ParameterDirection.Output };
    var dateParam = new SqlParameter("@date", SqlDbType.DateTime) { Direction = ParameterDirection.Output };
    
    await Database.ExecuteSqlRawAsync(@"
        SELECT @user = USER_NAME(), @date = GETDATE()", userParam, dateParam);
    
    var userName = userParam.Value.ToString();
    DateTime.TryParse(dateParam.Value.ToString(), out var currentDate);
    
    return (userName, currentDate);
}

调用示例：

var (user, date) = await _dbContext.GetDbUserAndDateAsync();
var mytable = new MyTable
              {
                  CreatedBy = user,
                  DateStarted = date
              };

_dbContext.MyTable.Add(mytable);
await _dbContext.SaveChangesAsync();

方案2：直接执行原始INSERT语句，绕过EF实体跟踪 #

直接用ExecuteSqlRawAsync执行原始INSERT语句，仅产生一次INSERT查询，完全避免额外SELECT：

await _dbContext.Database.ExecuteSqlRawAsync(@"
    INSERT mytable WITH (ROWLOCK) (created_by, date_started)
    VALUES (USER_NAME(), GETDATE())");

该方案适合不需要后续使用该实体对象的场景，性能最优。

方案3：使用EF Core数据库函数映射（EF Core 3.0+） #

通过HasDbFunction将SQL内置函数映射到DbContext方法，让EF Core生成INSERT语句时直接调用这些函数，无额外查询且保留EF实体跟踪优势：

1. 在DbContext中定义映射方法：

[DbFunction("USER_NAME", "")]
public static string UserName() => throw new NotImplementedException("此方法仅用于EF Core映射");

[DbFunction("GETDATE", "")]
public static DateTime GetDate() => throw new NotImplementedException("此方法仅用于EF Core映射");

2. 在OnModelCreating中配置映射：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(typeof(YourDbContext).GetMethod(nameof(UserName))!);
    modelBuilder.HasDbFunction(typeof(YourDbContext).GetMethod(nameof(GetDate))!);
}

3. 调用示例：

var mytable = new MyTable
              {
                  CreatedBy = YourDbContext.UserName(),
                  DateStarted = YourDbContext.GetDate()
              };

_dbContext.MyTable.Add(mytable);
await _dbContext.SaveChangesAsync();

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