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
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.
Then create a function which can turn your DTO list into a TVP, and return an
IQueryable
so you can compose it.For EF Core 7.0 or below, ensure the
StockDTO
class has been mapped in yourDbContext
configuration.For EF Core 8.0+, you can now use an unmapped type
Now you can compose that
IQueryable