C#•2y 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•2y 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)

this_is_pain•2y 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•2y ago

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

this_is_pain•2y ago

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

linqisniceOP•2y 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•2y 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

Did you find this page helpful?