C#•15mo ago

❔ EFCore, help with atomic update along with complex conditions

Basically, I've been trying to do something like this...

var composite = await _dbContext.Inventories.Where(i => requestedQuantity.Select(x => x.AccommodationId).Contains(i.AccommodationId) && nightsInterval.Contains(i.Date)) 
            && requestedQuantity.All(x => x.Value <= i.TotalQuantity - i.TotalReserved).ExecuteUpdateAsync(
            setters => setters.SetProperty(
               i => i.TotalReserved,
               i => i.TotalReserved + 1));

var composite = await _dbContext.Inventories.Where(i => requestedQuantity.Select(x => x.AccommodationId).Contains(i.AccommodationId) && nightsInterval.Contains(i.Date)) 
            && requestedQuantity.All(x => x.Value <= i.TotalQuantity - i.TotalReserved).ExecuteUpdateAsync(
            setters => setters.SetProperty(
               i => i.TotalReserved,
               i => i.TotalReserved + 1));

in one atomic operation, to ensure read and write-lock on the affected items. but ot's not possible, since linq to entities cant deal with: requestedQuantity.All(x => x.Value <= i.TotalQuantity - i.TotalReserved) I've also tried a loop variation, won'tt work properly. i've been looking at transactions, but they would serialize not per accommodation but the entire method, which would lead to a massive performance bottleneck on this m ethod. I don't know any SQL so I wouldn't know where to start. Any advice?

6 Replies

linqisniceOP•15mo ago

basically, we want to avoid overbooking... but allow or at least try to serialize concurrent access (i.e. not throw an exception and break the flow)

Omnissiah•15mo ago

i think there's something wrong with var composite = ... btw second requestedQuantity is outside .Where(...) also, i think a little % of overbooking wouldn't hurt that much, but it depends on your history i guess should it be like this

var composite = await _dbContext.Inventories
    .Where(i =>
        requestedQuantity.Select(_ => _.AccommodationId).Contains(i.AccommodationId)
        && nightsInterval.Contains(i.Date)
        && requestedQuantity.All(_ => _.Value <= i.TotalQuantity - i.TotalReserved)
    )
    .ExecuteUpdateAsync(setters =>
        setters.SetProperty(
            i => i.TotalReserved,
            i => i.TotalReserved + 1));

var composite = await _dbContext.Inventories
    .Where(i =>
        requestedQuantity.Select(_ => _.AccommodationId).Contains(i.AccommodationId)
        && nightsInterval.Contains(i.Date)
        && requestedQuantity.All(_ => _.Value <= i.TotalQuantity - i.TotalReserved)
    )
    .ExecuteUpdateAsync(setters =>
        setters.SetProperty(
            i => i.TotalReserved,
            i => i.TotalReserved + 1));

although i will admit this is not entirely clear

linqisniceOP•15mo ago

@dontI decided to go with a TimeStamp and try to manually serialize the concurrent requests by catching concurrency exception and retrying

Omnissiah•15mo ago

you could consider having an event architecture or an actor architecture, they're made to deal with these cases

linqisniceOP•15mo ago

@dontNot sure how that helps. This is about allowing concurrnecy but avoiding overbookings. Either way, the problem here:

var composite = await _dbContext.Inventories
    .Where(i =>
        requestedQuantity.Select(_ => _.AccommodationId).Contains(i.AccommodationId)
        && nightsInterval.Contains(i.Date)
        && requestedQuantity.All(_ => _.Value <= i.TotalQuantity - i.TotalReserved)
    )
    .ExecuteUpdateAsync(setters =>
        setters.SetProperty(
            i => i.TotalReserved,
            i => i.TotalReserved + 1));

var composite = await _dbContext.Inventories
    .Where(i =>
        requestedQuantity.Select(_ => _.AccommodationId).Contains(i.AccommodationId)
        && nightsInterval.Contains(i.Date)
        && requestedQuantity.All(_ => _.Value <= i.TotalQuantity - i.TotalReserved)
    )
    .ExecuteUpdateAsync(setters =>
        setters.SetProperty(
            i => i.TotalReserved,
            i => i.TotalReserved + 1));

is that 1 ( +1) should be dynamic, so i'd have to select from requestedQuantity there

Accord•15mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ EFCore, help with atomic update along with complex conditions