如何在EF Core ExecuteUpdateAsync中更新并新增关联实体？

问题描述 #

运行以下代码时，在YogabandyFee处触发异常：

var payoutResult = await _dbContext.StripePayouts
    .Where(p => p.StripePayoutId == stripePayoutId)
    .Select(p => new { p.GrossAmount, p.TotalTokens, p.NetAmount, p.ReconciledTransfers, p.EditedDate, p.YogabandyFee })
.ExecuteUpdateAsync(setters => setters
    .SetProperty(p => p.GrossAmount, grossAmount)
    .SetProperty(p => p.TotalTokens, totalTokens)
    .SetProperty(p => p.YogabandyFee, p => new YogabandyFee { TotalFee = (grossAmount - p.NetAmount - platformFee) } )
    .SetProperty(p => p.ReconciledTransfers, true)
    .SetProperty(p => p.EditedDate, DateTime.UtcNow)
);

报错信息：

System.InvalidOperationException: 无法转换LINQ表达式'DbSet() .Where(s => s.StripePayoutId == __stripePayoutId_0) .LeftJoin( inner: DbSet(), outerKeySelector: s => EF.Property(s, "Id"), innerKeySelector: y => EF.Property(y, "StripePayoutId"), resultSelector: (o, i) => new TransparentIdentifier( Outer = o, Inner = i )) .Select(s => new { GrossAmount = s.Outer.GrossAmount, TotalTokens = s.Outer.TotalTokens, NetAmount = s.Outer.NetAmount, ReconciledTransfers = s.Outer.ReconciledTransfers, EditedDate = s.Outer.EditedDate, YogabandyFee = s.Inner }) .ExecuteUpdate(setters => setters.SetProperty( propertyExpression: p => p.GrossAmount, valueExpression: __grossAmount_1).SetProperty( propertyExpression: p => p.TotalTokens, valueExpression: __totalTokens_2).SetProperty( propertyExpression: p => p.ReconciledTransfers, valueExpression: True).SetProperty( propertyExpression: p => p.EditedDate, valueExpression: DateTime.UtcNow).SetProperty( propertyExpression: p => p.YogabandyFee, valueExpression: p => new YogabandyFee{ TotalFee = __grossAmount_1 - p.NetAmount - __platformFee_3 } ))'。附加信息：传递给'SetProperty'的以下lambda参数不代表有效的可设置属性：'p => p.YogabandyFee'。在Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)在Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)在Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)在Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)在Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)在Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)在Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.b__0()在Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler)在Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)在Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)在Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.ExecuteUpdateAsync[TSource](IQueryable1 source, Expression1 setPropertyCalls, CancellationToken cancellationToken)在Functions.ReconcileTransfers.Run(String myQueueItem) in /Users/charles/yogabandy2020/Functions/ReconcileTransfers.cs:line 119

用户疑问：能否通过这类更新操作创建新实体？

解决方案 #

核心结论 #

不能通过ExecuteUpdateAsync这类批量更新操作创建关联的新实体。

原因分析 #

• ExecuteUpdateAsync是EF Core的批量更新API，仅能生成SQL直接更新数据库中已有行的标量属性，无法处理关联实体的创建逻辑——创建新实体需要插入新的数据库行，这超出了批量更新的能力范围。
• 报错的核心是YogabandyFee作为StripePayout的关联实体（一对一/一对多关系），SetProperty仅支持更新当前实体的标量属性，不能直接设置关联实体对象，更无法通过它创建新的关联实体。

正确实现方式 #

如果需要创建新的YogabandyFee并关联到StripePayout，可采用以下两种方式：

方式1：查询实体后更新（简单直观）

var payout = await _dbContext.StripePayouts
    .Include(p => p.YogabandyFee) // 检查是否已存在关联Fee
    .FirstOrDefaultAsync(p => p.StripePayoutId == stripePayoutId);

if (payout != null)
{
    // 更新StripePayout的标量属性
    payout.GrossAmount = grossAmount;
    payout.TotalTokens = totalTokens;
    payout.ReconciledTransfers = true;
    payout.EditedDate = DateTime.UtcNow;

    // 创建并关联新的YogabandyFee（若不存在）
    if (payout.YogabandyFee == null)
    {
        payout.YogabandyFee = new YogabandyFee 
        { 
            TotalFee = grossAmount - payout.NetAmount - platformFee
            // 如需显式设置外键，可添加 StripePayoutId = payout.Id
        };
    }
    else
    {
        // 若已有关联Fee，直接更新TotalFee
        payout.YogabandyFee.TotalFee = grossAmount - payout.NetAmount - platformFee;
    }

    await _dbContext.SaveChangesAsync();
}

方式2：批量更新+单独插入（性能优先）

若希望避免查询实体，可拆分操作并通过事务保证一致性：

using var transaction = await _dbContext.Database.BeginTransactionAsync();

try
{
    // 批量更新StripePayout的标量属性
    await _dbContext.StripePayouts
        .Where(p => p.StripePayoutId == stripePayoutId)
        .ExecuteUpdateAsync(setters => setters
            .SetProperty(p => p.GrossAmount, grossAmount)
            .SetProperty(p => p.TotalTokens, totalTokens)
            .SetProperty(p => p.ReconciledTransfers, true)
            .SetProperty(p => p.EditedDate, DateTime.UtcNow)
        );

    // 获取目标StripePayout的Id
    var payoutId = await _dbContext.StripePayouts
        .Where(p => p.StripePayoutId == stripePayoutId)
        .Select(p => p.Id)
        .FirstAsync();

    // 获取当前NetAmount用于计算
    var netAmount = await _dbContext.StripePayouts
        .Where(p => p.Id == payoutId)
        .Select(p => p.NetAmount)
        .FirstAsync();

    // 插入新的YogabandyFee
    await _dbContext.YogabandyFees.AddAsync(new YogabandyFee
    {
        StripePayoutId = payoutId,
        TotalFee = grossAmount - netAmount - platformFee
    });

    await _dbContext.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

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