ExecuteUpdateAsync with query on the new value

Question

I'm trying to migrate some old code that is still using foreach to update N values. I have been reading a lot about ExecuteUpdateAsync but i can't seem to understand if my use case is actually supported or not, every attempt i do seems to generate the same error

The LINQ expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync

This is my Update method, i'm trying to pay special attention to the performance.

public async Task Update(IEnumerable<StockDTO> dtos)
{
    var ids = dtos.Select(d => d.ProductStockIdentiferId);
    var StockTypes = dtos.Select(d => (int)d.StockType);
    
    await _dbcontext.Stocks
        .Where(s => 
                ids.Contains(s.ProductStockIdentiferId) &&
                StockTypes.Contains(s.StockTypeId))
        .ExecuteUpdateAsync(setters =>
            setters.SetProperty(
                stock => stock.Value,
                stock => dtos.First(x =>
                    x.ProductStockIdentiferId == stock.ProductStockIdentiferId).Value));
}

public record StockDTO
{
    public StockType StockType { get; set; }
    public long Value { get; set; }
    public int ProductStockIdentiferId { get; set; }
}

public partial class Stock
{
    public StockType StockType { get; set; }
    public int StockTypeId { get; set; }
    public long Value { get; set; }
    public int ProductStockIdentiferId { get; set; }
}

This code works if i just set the new Value to a static number, and i understand the EF error, since i had to change from Where().Any() to do Selects() in the beginning, but i don't know how can i get the corresponding dto.Value without using a foreach

Answer 1

The logic you are trying to achieve doesn't seem like it's going to work with two separate lists. You have three values per DTO, and these need to be corresponded.

You need a Table Valued Parameter here, in order to pass a pair of values per row to join on.

First define a Table Type. For efficiency sake, it's worthwhile to define a primary key on the join columns, which you need to do inline.

CREATE TYPE dbo.StockDTO (
    ProductStockIdentiferId int NOT NULL,
    StockType int NOT NULL,
    Value bigint NOT NULL,
    PRIMARY KEY (ProductStockIdentiferId, StockType)
);

Then create a function which can turn your DTO list into a TVP, and return an IQueryable so you can compose it.

public IQueryable<StockDTO> GetStockTvp(YourDbContext dbContext, IEnumerable<StockDTO> dtos, string paramName)
{
    var table = new DataTable { Columns = {
        { "ProductStockIdentiferId", typeof(int) },
        { "StockType", typeof(int) },
        { "Value", typeof(long) },
    } };
    foreach (var dto in dtos)
    {
        table.Rows.Add(dto.ProductStockIdentiferId, dto.StockType, dto.Value);
    }
    var parm = SqlParameter(paramName, SqlDbType.Structured) { TypeName = "dbo.StockDTO", Value = table };
    var query = dbContext.Set<StockDTO>.FromSqlRaw("SELECT * FROM @" + paramName, parm);
    return query;
}

For EF Core 7.0 or below, ensure the StockDTO class has been mapped in your DbContext configuration.

For EF Core 8.0+, you can now use an unmapped type

var query = dbContext.Database.SqlQueryRaw<StockDTO>("SELECT * FROM @" + paramName, parm);

---

Now you can compose that IQueryable

public async Task Update(IEnumerable<StockDTO> dtos)
{
    var tvp = GetStockTvp(_dbContext, dtos, "@dtos"); // make sure this is a separate step or it won't compose
    
    await _dbcontext.Stocks
        .Join(tvp, (stock, dto) => 
                stock.ProductStockIdentiferId == dto.ProductStockIdentiferId
                && stock.StockTypeId == dto.StockType,
              (stock, dto) => new {stock, dto })
        .ExecuteUpdateAsync(setters => setters
            .SetProperty(
                stockAndDto => stockAndDto.stock.Value,
                stockAndDto => stockAndDto.dtoValue
             )
         );
}