How do I run EF Core ExecuteUpdateAsync and add a new entity as part of that update?

I'm running the code below but getting an exception on YogabandyFee when running it.

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)
);

Can this be done? Can I create a new entry in this type of update?

Here is the error:

System.InvalidOperationException: The LINQ expression '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 } ))' could not be translated. Additional information: The following lambda argument to 'SetProperty' does not represent a valid property to be set: 'p => p.YogabandyFee'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.ExecuteUpdateAsync[TSource](IQueryable1 source, Expression1 setPropertyCalls, CancellationToken cancellationToken) at Functions.ReconcileTransfers.Run(String myQueueItem) in /Users/charles/yogabandy2020/Functions/ReconcileTransfers.cs:line 119

Solution

ExecuteUpdateAsync will try to create a SQL query based on the c# code you write. In the c# code, you are trying to update two tables at the same time in the same query. It's not possible. Even with writing your own SQL, most DMBS, don't allow it. (with exception of MySQL)

From your error message

The following lambda argument to 'SetProperty' does not represent a valid property to be set: 'p => p.YogabandyFee'

it seems like the parser is looking for a property instead of an object, so, hence, I tried to set the TotalFee of the 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.TotalFee, p => grossAmount - p.NetAmount - platformFee )
                .SetProperty(p => p.ReconciledTransfers, true)
                .SetProperty(p => p.EditedDate, DateTime.UtcNow)
            );

And you'll see this error:

Multiple 'SetProperty' invocations refer to different tables ('p => p.YogabandyFee.TotalFee' and 'p => p.EditedDate'). A single 'ExecuteUpdate' call can only update the columns of a single table.

What I'm trying to say is: ExecuteUpdate can only update one table. I think, the answer to "Can I create a new entry in this type of update?" is no.

My suggestions:

Use stored procedures to update both tables at once (https://stackoverflow.com/a/5154670/17431983); tools like Dapper allow you run a stored procedure.
Use UnitOfWork - this allows you to do multiple _dbContext queries in the same transaction

You can also do two separate ExecuteUpdateAsync, one for each table update, if it's not a performance issue.