在Entity Framework和Sql Server中创建对象计数器 [英] Creating object counters in Entity Framework and Sql Server

问题描述

注意1：我修改了这个问题。它现在由供应商和订单组成，而不是汽车和零件。

注2：这个问题是假的。
我的目标是了解如何创建对象计数器。

对于监管要求，我需要为每个供应商按顺序排列每个订单。

我正在使用'Entity Framework` 与Sql Server。

在我的假设示例中，我有一个供应商类和订单类。

每个供应商都有订单。每个订单都有一个产品和数量。意思是说，它指出哪个产品是从供应商订购的，还有多少个。

我需要能够创建计数器，如自动递增的数字，以计数订单 FOR EACH供应商。

出于监管的原因，每个供应商必须按创建顺序依次编号，并仅使用整数。

当我们检查订单时，我们应该通过其 OrderCountForSupplier 列，监管机构的创建顺序（一个 DateTime / TimeStamp 列是不够的，他们需要这样一个计数器）。

为了简化此问题，无法删除订单（状态可能会更改，但无法删除）。

对于我来说，包含技术/编程方式的解决方案非常重要，不仅仅是理论的方式。

我已经制作了一个图表，以便以最明确的方式解释我的问题：

< hr>

我有一种方式可以工作，很高兴听到反馈。

我在想外部表/表，用于保存计数器。如下所示：

 供应商订单表
 
 | SupplierId | OrderCountForSupplier 
 ------------------------ 
 | 54654 | 3 
 | 78787 | 2 
 | 99666 | 4 
  

我需要一个触发器来增加 OrderCountForSupplier 每个插件的计数器，对于每个供应商？

如果没有 - 这种增量如何以安全的方式进行？ （例如，在竞争条件下，获得下一个计数器并增加两个进程，这可能最终导致重复的订单计数）。

另一个说明：
可以这样做吗？实体框架明智吗？如果没有 - 一个 Sql Server 解决方案将被尊重。

解决方案

之后调查一些可能的方法（参见底部的链接），我在@Vladimir Baranov的帮助下，提出了一个非常基本的解决方案。

我已经裁定使用

我确实使用了一个计数器表，从中可以为每个供应商提供最新的柜台。

 供应商订单表
 
 | SupplierId | OrderCountForSupplier 
 ------------------------ 
 | 54654 | 3 
 | 78787 | 2 
 | 99666 | 4 
  

以下步骤：

1. 获取当前的供应商订单计数器。


2. 尝试使用当前的计数器+ 1为供应商插入新的订单。 li>
   

3. 如果插入正常=>在供应商柜台表上增加此供应商的订单计数器。

   
   
   

   如果插入出错，我们收到一条错误，指出已经违反约束（已经存在的相同的订单数）：
   再次尝试2次以获取当前的计数器，然后再次尝试插入订单。 p>

代码

  public class SupplierRepository 
 {
 private MyContext _context; 
私人供应商_供应商; 
 
 public SupplierRepository（int supplierId）
 {
 _context = new MyContext（）; 
 _supplier = context.Suppliers.Single（x => x.SupplierId == supplierId）; 
} 
 
 //检索供应商的最新计数器
 public SupplierCounter GetCounter（）
 {
 var counterEntity = _context.SupplierCounters.Single（x => x.SupplierId == _supplier.SupplierId）; 
 return counterEntity; 
} 
 
 //添加供应商
 public void AddSupplier（Order order）
 {
 int retries = 3; 
 
 while（retries> 0）
 {
 SupplierCounter currentCounter = GetCounter（）; 
 try 
 {
 //将当前计数器设置为订单对象
 _order.OrderCountForSupplier = currentCounter.OrderCountForSupplier; 
 _context.Add（order）; 
 //成功！更新计数器（+1），然后突破while循环。 
 currentCounter.OrderCountForSupplier + = 1; 
 //我在添加订单后增加保存并增加计数器，因此他们将进入同一个交易。 
 //这将防止添加订单的场景，并且计数器不会增加。 
 _context.SaveChanges（）; 
 break; 
} 
 catch（SqlException ex）
 {
 if（ex.Number == 2627）//违反唯一约束
 {
 --retries; 
} 
} 
} 
} 
} 
  

一些有用的链接

• 具有第二个字段自动增量的两个字段的SQL Server唯一复合密钥


• 使用实体框架的原子增量


• 如何在实体框架中使用多重用户安全5

Note 1: I REPHRASED THE QUESTION. It now consists of Suppliers and Orders, instead of Cars and Parts.

Note 2: THIS PROBLEM IS HYPOTHETICAL. My goal is to understand how to create object counters.

For regulatory requirements, I need TO SEQUENTIALLY NUMBER EACH Order for each of the suppliers.

I'm Using 'Entity Framework` with Sql Server.

In my hypothetical example, I have a Supplier class and an Order class.

Each supplier has Orders. Each order has a product and a quantity. Meaning, it states which product was ordered from the supplier and how many of it.

I need to be able to create counters, like an auto incremented number, to count the orders FOR EACH supplier.

For regulatory reasons, each supplier must sequentially number its orders, in the order of creation, and using an integer only.

When we examine an Order, We should know by its OrderCountForSupplier column, what was its order of creation (a DateTime / TimeStamp column is insufficient by the regulatory authorities. They require such a counter).

For simplicity of this question, an order cannot be deleted (it's status can change, but it cannot be deleted).

It's very important for me to have a solution which includes the technical/programming way, not only theoretic way.

I've made a diagram in order to explain my problem in the most clear way possible:

---

I have a way that might work, and would be glad to hear feedback.

I'm thinking of an external table/tables, to hold the counters. Something like:

Supplier Order Counters Table

| SupplierId | OrderCountForSupplier
------------------------
| 54654         | 3
| 78787         | 2
| 99666         | 4

Would I need a trigger in order to increment the OrderCountForSupplier counter on each insertion, for each supplier?

If not - how can this incremental be done in a safe way ? (without for example, two processes in a race condition to get the next counter and increment it, which could eventually result in a duplicate Order Count).

And another note: Can this be done Entity Framework wise? if not - a Sql Server solution will be respected.

解决方案

After investigating some possible approaches (see links at the bottom), I've came out with a very basic solution, with the help of @Vladimir Baranov.

I've ruled out using SqlServer triggers / Stored Procedures. They seemed hard to implement in conjunction with Entity Framework, and they seem to me like an Over-Kill in this scenario.

I've also ruled out the Optimistic Concurrency approach (using a concurrency token), because in this scenario, the counters cannot be updated simultaneously. They only get updated after a successful insertion to the orders table.

My orders table looks like that. I've added a unique constraint on the OrderId, SupplierId and OrderCountForSupplier trio, so insertion of the same order count for a supplier would fail.

I've indeed used a counters table, from which I can take the latest counter - for each of the suppliers.

Supplier Order Counters Table

| SupplierId | OrderCountForSupplier
------------------------
| 54654         | 3
| 78787         | 2
| 99666         | 4

These are the steps:

1. Get the current supplier orders counter.
2. Try insert a new order for the supplier, using the current counter + 1.

3. If the insertion goes ok => Increase the orders counter for this supplier, on the supplier counters table.

   If insertion goes wrong, and we get an error stating the has been a violation of the constraint (same order count, which already exists): Try 2 more times to get the current counter, and try inserting the order again.

The Code:

   public class SupplierRepository
   {
        private MyContext _context;
        private Supplier _supplier;

        public SupplierRepository(int supplierId)
        {
            _context = new MyContext();
            _supplier = context.Suppliers.Single(x => x.SupplierId == supplierId);
        }

        // Retrieve the latest counter for a supplier
        public SupplierCounter GetCounter()
        {
            var counterEntity = _context.SupplierCounters.Single(x => x.SupplierId == _supplier.SupplierId);
            return counterEntity;
        }

        // Adding a supplier
        public void AddSupplier(Order order)
        {
            int retries = 3;

            while (retries > 0)
            {
                SupplierCounter currentCounter = GetCounter();
                try
                {
                    // Set the current counter into the order object
                    _order.OrderCountForSupplier = currentCounter.OrderCountForSupplier;
                    _context.Add(order);                        
                    // Success! update the counter (+1) and then break out of the while loop.
                    currentCounter.OrderCountForSupplier += 1;  
                    // I'M CALLING `SAVECHANGES` AFTER ADDING AN ORDER AND INCREASING THE COUNTER, SO THEY WOULD  BE IN THE SAME TRANSACTION. 
                    // THIS WOULD PREVENT A SCENARIO WHERE THE ORDER IS ADDED AND THE COUNTER IS NOT INCREMENTED.
                    _context.SaveChanges();
                    break;
                }
                catch (SqlException ex)
                {
                    if (ex.Number == 2627) // Violating unique constraint
                    {
                        --retries;
                    }
                }
            }
        }
    }

Some useful links:

• SQL Server Unique Composite Key of Two Field With Second Field Auto-Increment
• Atomic Increment with Entity Framework
• how to inc/dec multi user safe in entity framework 5

这篇关于在Entity Framework和Sql Server中创建对象计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！