is it possible to set the property value of SetProperty in ExecuteUpdate() from a collection of values

Question

I am using Entity Framework Core 7, but I am new to this.

My scenario is as follows. Users enter edited information via the UI that is stored in instances of an entity class called Upload.

I could potentially have many edited entities, so I was pleased to find the ExecuteUpdate() method which would only make one DB call rather than one call for each entity that using ChangeTracking and SaveChanges would have done.

However, I cannot seem to find a way in code to set the value for each of the edited properties for each respective entity.

Here is my code so far (not working):

var editedUploads = editViewModel.SelectedIncomingStatements
                                 .Select((d,Index) => new Upload() { Id = d.Id , BankRoyaltiesReceived = d.BankRoyaltiesReceived, ExchangeRate = d.ExchangeRate, Tag = Index});

var editedUploadIds = editedUploads.Select(u => u.Id).ToHashSet();

var erColl = new Collection<double>(editedUploads.Select(u => u.ExchangeRate).ToList()); 
var rrColl = new Collection<double>(editedUploads.Select(u => u.BankRoyaltiesReceived).ToList());

using (var trans = await ctx.Database.BeginTransactionAsync(cancellationToken))
{
    try
    {
        var q = from u in ctx.Uploads 
                let c = editedUploadIds.Contains(u.Id) 
                where c == true 
                select u;

        await q.ExecuteUpdateAsync(setters => setters.SetProperty(p => p.BankRoyaltiesReceived, u => rrColl[(int)u.Tag]).SetProperty(p => p.ExchangeRate, u => erColl[(int)u.Tag]));

        await trans.CommitAsync(cancellationToken);
    }
    catch (Exception)
    {
        await trans.RollbackAsync(cancellationToken);
        throw;
    }
}

The main issue is for me to be able to 'index' out the values from the 2 collections exColl and rrColl in the SetProperty's so that I am applying the correct edited value for the corresponding Upload.

Fortunately, it seems that collections are translated by EF Core so there use seems to be OK. I tested this with collections and hard coded indices which worked. I originally tried with dictionaries, but according to the docs this is not yet permissible.

So where I am at is to supply the collections with indexes that translate ok for EF Core.

As you can see in the code my last attempt was to add a [NotMapped] attribute to the .Tag property on the Upload entity and set this with the index that will be needed from the 2 collections. Sadly, this didn't work since EF translation complains that .Tag is [NotMapped] !

So, I am stuck with how to set multiple properties for multiple entities using ExecuteUpdate().

I feel I must be missing something obvious since I would think that this scenario is exactly what ExecuteUpdate() is for?

Any suggestions?

Thanks in advance for any help.

Answer 1

The below only works in SQL Server. In Postgres, you can probably do similar by using an array and NpgsqlDbType.Composite.

You should use a Table Valued Parameter for this.

First define a Table Type.

CREATE TYPE dbo.Upload AS TABLE (
  Id bigint PRIMARY KEY,
  BankRoyaltiesReceived decimal(19,9),
  ExchangeRate decimal(19,9)
);

In EF Core 8+ you can use ctx.Database.SqlQuery<Upload> on this. But for EF Core 7, you need to define it in your model

modelBuilder.Entity<Upload>().HasNoKey.ToView(null);

Now you can pass it as a parameter

var table = new DataTable { Columns = {
    { "Id", typeof(long) },
    { "BankRoyaltiesReceived", typeof(decimal) },
    { "ExchangeRate", typeof(decimal) },
} };

foreach (var d in editViewModel.SelectedIncomingStatements)
{
    table.Add(d.Id, d.BankRoyaltiesReceived, d.ExchangeRate,);
}

// must do this in a separate step from the rest of the query
var tvp = ctx.Set<Upload>.FromSqlRaw(
    "SELECT * FROM @tmp",
    new SqlParameter("@tmp", table) { TypeName = "dbo.Upload" }
);
//

var q =
    from u in ctx.Uploads
    join t in tvp on u.Id = t.Id
    select new { u, t };

await using var trans = await ctx.Database.BeginTransactionAsync(cancellationToken);

await q.ExecuteUpdateAsync(setters => setters
    .SetProperty(ut => ut.u.BankRoyaltiesReceived, ut => u.t.BankRoyaltiesReceived)
    .SetProperty(ut => ut.u.ExchangeRate, ut => ut.t.ExchangeRate);

await trans.CommitAsync(cancellationToken);

Note that you don't need to manually rollback on error if you have a using. And you don't need a transaction anyway if all you are doing is one big UPDATE.