EF Core Concurrency, what is the equivalent of SQL Addition (+=) in Entity Framework?

.net-core c# entity-framework entity-framework-core

Question

I am thinking that I will face a issue with concurrency. All my models has concurrency rowversion timestamp.

A example if I buy a item from a shop I will add the money to the shop in SQL I can do "money=money+value" but with EF Core I cannot do that.

Current logic:

  1. I fetch the "PropertyId, Money, RowVersion" as "DTO"
  2. Validation
  3. Money = Money+Cost (This value can be changed by another process/request?)
  4. Using EF Core Plus Updating only the column if RowVersion is same.
  5. If update return 0 I throw DbUpdateConcurrencyException
  6. Rerun the method again.

Different code example:

    var charIdsQuery = Query.Select(x => x.Id);
            //Todo change the count so it only reflect all users logged in on current day.
            var qty = await _db.Users.CountAsync() * 10;

            var query = _db.Items.
                Where(x => charIdsQuery.Contains(x.CharacterId) && x.State == Models.Areas.Game.Enums.ItemState.ShopSale)
                .Select(x => new ItemRefillViewModel {
                    Id = x.Id,
                    Quantity = x.Quantity,
                    CharacterId = x.CharacterId,
                    Cost = x.Detail.Properties.Where(z => z.Key == ItemConstant.KEY_STATS_COST).Select(z => z.ValueDecimal).FirstOrDefault() ?? 0,
                    RowVersion = x.RowVersion
                }).OrderBy(x => x.CharacterId).ThenBy(x => x.Quantity);

            int currentShopId = -1;
            int updates = 0;
            var helper = await BatchHelper<ItemRefillViewModel>.CreateAsync(query,1,200);

            var shopData = new Dictionary<int, ShopRefillData>();
            var itemData = new Dictionary<long, ItemRefillData>();
            for (int i = 1; i <= helper.TotalPages; i++)
            {
                var items = await helper.GetBatch(i).ToArrayAsync();
                shopData.Clear();
                itemData.Clear();

                foreach (var item in items)
                {
                    int qtyToBuy = qty - item.Quantity;
                    if (qtyToBuy < 1) continue; //has enough qty
                    decimal cost = qtyToBuy * item.Cost * 0.70m;

                    if (currentShopId != item.CharacterId)
                    {
                        currentShopId = item.CharacterId;
                        var info = await _db.CharacterProperties.Where(x => x.CharacterId == currentShopId && x.Key == CharacterConstant.KEY_MONEY)
                            .Select(x => new { ShopMoney = x.ValueDecimal, x.RowVersion })
                            .FirstOrDefaultAsync();

                        if(info != null && !shopData.ContainsKey(currentShopId))
                        {
                            shopData[currentShopId] = new ShopRefillData(info.ShopMoney ?? 0,info.RowVersion);
                        }
                    }

                    if (cost > shopData[currentShopId].Money) continue; // cannot buy batch

                    shopData[currentShopId].Money -= cost;
                    itemData[item.Id] = new ItemRefillData(qtyToBuy + item.Quantity,item.RowVersion);
                }

                //Begin saving the batch
                using var transaction = await _db.Database.BeginTransactionAsync();
                try
                {
                    var updated = 0;
                    foreach (var sItem in shopData)
                    {
                        updated = await _db.CharacterProperties.Where(x => x.CharacterId == sItem.Key && 
                        x.Key == CharacterConstant.KEY_MONEY && x.RowVersion == sItem.Value.Version)
                            .UpdateAsync(x => new CharacterProperty() { ValueDecimal = sItem.Value.Money });
                        if(updated == 0)
                        {
                            //Could not update, conflict error.
                            break;
                        }
                    }

                    if(updated != 0)
                    {
                        foreach (var iItem in itemData)
                        {
                            updated = await _db.Items
                                .Where(x => x.Id == iItem.Key && x.RowVersion == iItem.Value.Version)
                                .UpdateAsync(x => new Item { Quantity = iItem.Value.Qty });

                            if (updated == 0)
                            {
                                //Could not update, conflict error.
                                await transaction.RollbackAsync();
                                break;
                            }
                        }
                    }

                    if(updated != 0)
                    {
                        await transaction.CommitAsync();
                        updates++;
                    }
                }
                catch
                {
                    await transaction.RollbackAsync();
                }
            }

            return updates;

I am using Entity Framework Plus and UpdateAsync function which return int of updated rows with EF Core transaction.

Is it wrong or is there a better way to do it?

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/add-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/ef/core/saving/concurrency will this only work if I fetch the whole entity?

1
0
1/17/2020 9:33:21 PM

Accepted Answer

I think I solved it this way but it seems to be lot of code for a small thing.

   private class ShopRefillData
    {
        public ShopRefillData(decimal money, byte[] version)
        {
            CurrentMoney = money;
            Version = version;
        }

        public decimal CurrentMoney { get; set; }
        public byte[] Version { get; set; }
        public decimal Cost { get; set; }
        public decimal NewMoney => CurrentMoney - Cost;
    }

    private class ItemRefillData
    {
        public ItemRefillData(int currentQty, int qtyToRemove, byte[] version)
        {
            CurrentQty = currentQty;
            QtyToRemove = qtyToRemove;
            Version = version;
        }

        public int CurrentQty { get; set; }
        public int QtyToRemove { get; private set; }
        public byte[] Version { get; set; }

        public int NewQty => CurrentQty - QtyToRemove;
    }

    public async Task<int> RefillStockForAllAsync()
    {
        var charIdsQuery = Query.Select(x => x.Id);

        var DbF = EF.Functions;
        var currentDay = (DateTime?)DateTime.UtcNow.Date;
        //Reflect all users logged in on current day, the shop will only refill to match the demand of active users.
        var qty = await _db.Users.Where(x => DbF.DateDiffDay(x.LastLoginAt,currentDay) == 0).CountAsync() * 10;

        var query = _db.Items.
            Where(x => charIdsQuery.Contains(x.CharacterId) && x.State == Models.Areas.Game.Enums.ItemState.ShopSale)
            .Select(x => new ItemRefillViewModel {
                Id = x.Id,
                Quantity = x.Quantity,
                CharacterId = x.CharacterId,
                Cost = x.Detail.Properties.Where(z => z.Key == ItemConstant.KEY_STATS_COST).Select(z => z.ValueDecimal).FirstOrDefault() ?? 0,
                RowVersion = x.RowVersion
            }).OrderBy(x => x.CharacterId).ThenBy(x => x.Quantity);

        int currentShopId = -1;
        int updates = 0;
        var helper = await BatchHelper<ItemRefillViewModel>.CreateAsync(query,1,200);

        var shopData = new Dictionary<int, ShopRefillData>();
        var itemData = new Dictionary<long, ItemRefillData>();
        for (int i = 1; i <= helper.TotalPages; i++)
        {
            var items = await helper.GetBatch(i).ToArrayAsync();
            shopData.Clear();
            itemData.Clear();

            foreach (var item in items)
            {
                int qtyToBuy = qty - item.Quantity;
                if (qtyToBuy < 1) continue; //has enough qty
                decimal cost = qtyToBuy * item.Cost * 0.70m;

                if (currentShopId != item.CharacterId)
                {
                    currentShopId = item.CharacterId;
                    var info = await _db.CharacterProperties.Where(x => x.CharacterId == currentShopId && x.Key == CharacterConstant.KEY_MONEY)
                        .Select(x => new { ShopMoney = x.ValueDecimal, x.RowVersion })
                        .FirstOrDefaultAsync();

                    if(info != null && !shopData.ContainsKey(currentShopId))
                    {
                        shopData[currentShopId] = new ShopRefillData(info.ShopMoney ?? 0,info.RowVersion);
                    }
                }

                if (cost > shopData[currentShopId].CurrentMoney) continue; // cannot buy batch

                shopData[currentShopId].Cost += cost;
                itemData[item.Id] = new ItemRefillData(item.Quantity, qtyToBuy, item.RowVersion);
            }

            //Begin saving the batch
            using var transaction = await _db.Database.BeginTransactionAsync();
            try
            {
                var updated = 0;
                foreach (var sItem in shopData)
                {
                    updated = await _db.CharacterProperties.Where(x => x.CharacterId == sItem.Key && 
                    x.Key == CharacterConstant.KEY_MONEY && x.RowVersion == sItem.Value.Version)
                        .UpdateAsync(x => new CharacterProperty() { ValueDecimal = sItem.Value.NewMoney });
                    if(updated == 0)
                    {
                        //concurrency check
                        var info = await _db.CharacterProperties.Where(x => x.CharacterId == currentShopId && x.Key == CharacterConstant.KEY_MONEY)
                            .Select(x => new { ShopMoney = x.ValueDecimal, x.RowVersion })
                            .FirstOrDefaultAsync();

                        if(info != null)
                        {
                            sItem.Value.CurrentMoney = info.ShopMoney ?? 0; //Refreshing money from db.
                            updated = await _db.CharacterProperties.Where(x => x.CharacterId == sItem.Key &&
                            x.Key == CharacterConstant.KEY_MONEY && x.RowVersion == info.RowVersion)
                                .UpdateAsync(x => new CharacterProperty() { ValueDecimal = sItem.Value.NewMoney });
                        }

                        if (updated == 0)
                        {
                            //Could not update, concurrency error after another try. Will try again on next cronjob.
                            break;
                        }
                    }
                }

                if(updated != 0)
                {
                    foreach (var iItem in itemData)
                    {
                        updated = await _db.Items
                            .Where(x => x.Id == iItem.Key && x.RowVersion == iItem.Value.Version)
                            .UpdateAsync(x => new Item { Quantity = iItem.Value.NewQty });

                        if (updated == 0)
                        {
                            //concurrency check
                            var info = await _db.Items.Where(x => x.Id == iItem.Key)
                                .Select(x => new { x.Quantity, x.RowVersion })
                                .FirstOrDefaultAsync();
                            if (info != null)
                            {
                                iItem.Value.CurrentQty = info.Quantity; //refreshing qty from database
                                updated = await _db.Items
                                    .Where(x => x.Id == iItem.Key && x.RowVersion == info.RowVersion)
                                    .UpdateAsync(x => new Item { Quantity = iItem.Value.NewQty });
                            }

                            if (updated == 0)
                            {
                                //Could not update, concurrency error after second try. Will try again on next cronjob.
                                await transaction.RollbackAsync();
                                break;
                            }
                        }
                    }
                }

                if(updated != 0)
                {
                    await transaction.CommitAsync();
                    updates++;
                }
            }
            catch
            {
                await transaction.RollbackAsync();
            }
        }

        return updates;
    }
0
1/24/2020 11:12:15 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow