logo标签列表

在Entity Framework和SQL Server中创建对象计数器

c#sql-serverentity-frameworkdatabase-design
3

注意1: 我重新表述了问题。现在它由供应商和订单组成，而不是汽车和零件。

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

为了符合监管要求，我需要为每个供应商逐个编号每个订单。

我正在使用带有Sql Server的'Entity Framework'。

在我的假设性示例中，我有一个Supplier类和一个Order类。

每个供应商都有订单。每个订单都有一个产品和一个数量。这意味着它说明了从供应商订购了哪个产品以及订购了多少个。

我需要能够创建计数器，就像自动递增的数字一样，来计算每个供应商的订单。

出于监管原因，每个供应商必须按照创造顺序顺序编号其订单，并仅使用整数。

当我们检查一个订单时，通过其 OrderCountForSupplier 列，我们应该知道它的创建顺序（仅使用 DateTime / TimeStamp 列是不够的，监管机构要求这样的计数器）。
为了简化这个问题，订单不能被删除（状态可以改变，但不能删除）。
对我来说，拥有包含技术/编程方式的解决方案非常重要，而不仅仅是理论方式。
我制作了一张图表，以最清晰的方式解释我的问题：

enter image description here

我有一个可能有效的方法，并且很乐意听取反馈意见。
我在考虑使用外部表来保存计数器。类似于：
Supplier Order Counters Table

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

我需要一个触发器来在每次插入时为每个供应商递增OrderCountForSupplier计数器吗？

如果不需要-如何以安全的方式进行递增？（例如，避免两个进程竞争获取下一个计数器并将其递增，这可能最终导致重复的订单计数）。

另外一个注意点： 这是否可以通过Entity Framework完成？如果不能-则可以使用Sql Server解决方案。

你是否熟悉“identity”类型字段？https://technet.microsoft.com/zh-cn/library/aa933196(v=sql.80).aspx - Kosala W
是的 - 在我的例子中，“Cars”表可以有一个标识自动递增的“int”，每次插入后将自动计数1、2、3...。但这并不能帮助我解决“Parts”表的问题 - 这才是真正的问题。 - ilans
1
你的零件表不正确。零件表应该只有 Part_Id 和 PartName 两个字段。还需要另一个名为 CarParts 的表，其中应该有 Car_Id 和 Part_Id 两个字段。 - Kosala W
@KosalaW - 我的零件表应该包含所有汽车的零件。但是每辆汽车都有自己的零件。A车可能有20个零件，B车可能有30个零件。但是每个零件都是具体的，并且只与一辆汽车相关联。如果有一个名为CarParts的连接表来保存Car_Id和Part_id，那么Parts Table的PK将是什么？请记住，每个零件都必须与其所属的特定汽车相关联。 - ilans
请看下面我的答案。 - Kosala W
我已经重新表述了问题，并使用了供应商-订单类代替汽车零件。现在问题更清晰了。 - ilans
4个回答
2

首先回答，问题中的示例在撰写后已更改。

您说，在零件编号中有间隙是可以的，因为“某些零件可能会在途中被删除”。

那么，您的示例与下列示例有何区别：

Car      PartID
54654    1
54654    2
54654    3
78787    1
78787    2
99666    1
99666    2
99666    5
99666    7

还有这个变体：

Car      PartID
54654    1
54654    2
54654    3
78787    4
78787    5
99666    6
99666    7
99666    8
99666    9

在第二个变量中，每个零件都有一个唯一的ID（它也是全局唯一的，但这并不重要）。在第二个变量中，PartID指定了部件插入表中的顺序，与第一个变量相同。
因此，我会使用一个简单的IDENTITY列： 零件 
PartID int IDENTITY NOT NULL (PRIMARY KEY)
CarLicenseNum int NOT NULL (FOREIGN KEY)
PartName varchar(255)

供应商订单示例更新

更新问题中最重要的部分是"监管原因"。它回答了为什么要做这种不自然的事情的问题。"监管"和效率通常是相反的。

基本上，这意味着在插入新行并计算序列中下一个数字时，必须使用可序列化事务隔离级别。这将影响并发性/吞吐量，但它将保证一致性并在多用户环境中“安全”。

我不知道如何在Entity Framework中实现，但应该是可能的。但是，出于“监管原因”，我会将此逻辑放在DB中的存储过程中，并确保普通用户没有直接写入Orders表的权限，而只有执行此专用存储过程的权限。您可以在EF代码中复制此存储过程的逻辑，但数据库本身将对通过其他应用程序进行的更改开放，这些更改可能不遵循监管要求。

您可以使用单独的表来实现它，该表为每个供应商存储最新的序列号，也可以动态读取最后的最大序列号。如果每个供应商只有少量订单，则具有计数器最新值的单独表将与Orders表相当，并且您不会获得太多收益。无论如何，拥有适当的索引是关键。获取最新计数器值将是索引中的一个查找。

这是一个不使用额外表的存储过程示例。

确保Orders表在(SupplierId, OrderCountForSupplier)上有唯一索引。实际上，即使您使用额外的表来强制执行约束，也必须拥有此索引。

CREATE PROCEDURE [dbo].[AddOrder]
    @ParamSupplierID int, 
    @ParamProductSerial varchar(10),
    @ParamQuantity int,
    @NewOrderID int OUTPUT
AS
BEGIN
    SET NOCOUNT ON; 
    SET XACT_ABORT ON;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRANSACTION;
    BEGIN TRY

        DECLARE @VarMaxCounter int;

        SELECT TOP(1) @VarMaxCounter = OrderCountForSupplier
        FROM dbo.Orders
        WHERE SupplierID = @ParamSupplierID
        ORDER BY OrderCountForSupplier DESC;

        SET @VarMaxCounter = ISNULL(@VarMaxCounter, 0) + 1;

        INSERT INTO dbo.Orders
            (SupplierID
            ,OrderCountForSupplier
            ,ProductSerial
            ,Quantity)
        VALUES
            (@ParamSupplierID
            ,@VarMaxCounter
            ,@ParamProductSerial
            ,@ParamQuantity);

        SET @NewOrderID = SCOPE_IDENTITY();

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- TODO: handle the error
        SET @NewOrderID = 0;
        ROLLBACK TRANSACTION;
    END CATCH;

END
GO
我做不到。要求是为每个汽车零件都有一个计数器。在现实世界中，这是当局的监管要求，要对每个供应商的订单进行计数。而且必须按顺序计数。 - ilans
我已经重新表述了问题，并使用了供应商-订单类代替汽车零件。如果您的答案参考了我的重新措辞的问题，我会很高兴。谢谢。 - ilans
@ilans，我更新了答案，并展示了如何满足“监管要求”。 - Vladimir Baranov
最后，一个真正详细的答案似乎触及了痛点。我认为那是我需要走的方向。只有另一个问题 - 如果您必须使用另一个表来计数器，您将如何防止两个并行插入使用相同的计数器？ 您将如何锁定对单个进程的访问？ - ilans
1
实际上，不需要可序列化事务也是可能的。您可以使用乐观的方法。只需不显式锁定任何内容并尝试生成下一个值。如果由于唯一约束冲突而导致插入失败，则处理错误并重试直到成功为止。这种类型的问题已经被问过很多次，例如：https://dev59.com/_YDba4cB1Zd3GeqPF5PE - Vladimir Baranov
显示剩余4条评论
1

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

我排除了使用SqlServer triggers / Stored Procedures。它们似乎与Entity Framework难以结合实现，并且在这种情况下，它们对我来说好像是过度设计。

我还排除了乐观并发方法（使用并发标记），因为在此场景中，计数器不能同时更新。只有在成功插入订单表后才会更新它们。

我的订单表看起来像这样。我在OrderId，SupplierId和OrderCountForSupplier三元组上添加了一个唯一约束，因此对于同一供应商的相同订单数量的插入将失败。

enter image description here

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

Supplier Order Counters Table

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

这是步骤：
1. 获取当前的供应商订单计数器。 2. 尝试使用当前计数器 + 1为供应商插入新订单。 3. 如果插入成功 => 在供应商计数器表中增加该供应商的订单计数器。 如果插入失败，并且我们收到一个错误消息，说明已经违反了约束条件（相同的订单计数已存在）： 再尝试2次获取当前计数器，并再次尝试插入订单。
代码：
   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;
                    }
                }
            }
        }
    }

一些有用的链接：

1
只是一般性的提示，请确保对您的解决方案进行压力测试。启动几十个应用程序实例，并让它们同时插入几千或几百万个订单，然后检查结果。我无法从您的代码中判断，但您应该确保两个数据修改步骤在同一个事务中执行：插入新订单和增加计数器。您不希望只有其中一个步骤发生而没有另一个步骤。 - Vladimir Baranov
关于交易的评论很好...步骤2和3应该在同一笔交易中完成 - 我非常确定确实会发生这种情况，因为SaveChanges在这两个步骤运行后发生。如果需要，我会检查并相应地更新答案...谢谢@VladimirBaranov。 - ilans
只有在添加订单并增加计数器之后，才会调用“SaveChanges”。这强制“EF”将它们包装在同一事务中。 - ilans
0

首先，完全改变你的问题是不可以的！请删除此问题并创建一个新的。话虽如此...

回答当前问题：

对于假设性问题的回答仅仅基于个人意见和/或过于宽泛（实际上有一个标志可以用于此 - 许多好问题会根据专家经验产生某种程度的基于意见的回答，但对于这个问题的回答往往几乎完全基于意见，而不是事实、参考或特定专业知识。）！

我对当前问题的回答是：我没有看到在数据库中创建OrderCountForSupplier的任何好处（或优势或用途）！在数据库中创建这样的计数器会使得在多线程环境下进行添加和维护非常复杂和容易出错。

我认为可以通过EF的帮助（将计数器的创建移动到代码中）和不同的数据库设计更轻松地解决这个问题：

为了允许并发添加订单，创建两个列 - 一个GUID作为Order主键和一个CreationDate类型为DateTime。从多个线程填充这两个列不是问题。
当检索特定SupplierId的所有Orders时，按CreationDate升序排序结果列表。
当使用（例如）for循环迭代结果列表时，计数器是所需的顺序计数器。
作为EF解决方案的替代方案，可以将顺序计数器的创建保留在SQL中 - 为Order项创建视图或存储过程，并使用ROW_NUMBER在对SupplierId进行分组并按CreationDate排序后创建人工顺序计数。
再次从多个线程读取数据库（并在每个线程中创建计数器）也不再是问题。

第一个问题的答案：

你已经接近成功了。你需要更加规范化你的数据模型。这是一个常见的情况，你想要最小化数据的冗余，同时仍然保持有意义的关系（不使用触发器）。

一种可能的解决方案是创建一个Car_has_Part表来表示Car和Part实体之间的关系：

| Car_has_Part |
----------------
| PartId       |
| CarId        |

Car_has_Part表的主键是由CarId + PartId组成的复合主键，它既是唯一的，同时避免了数据重复。

在您的示例中，在Parts表中，每个Car都会重复出现Doors部件。使用这个中间表，数据不会重复，而且您还有一个适当的关系。

您的新数据模型可能如下所示：

| Car  |    | Car_has_Part |    | Part   |
-------     ----------------    ----------
|CarId |    | PartId       |    | PartId |
|Model |    |              |    | Descr  |
| etc. |    | CarId        |    | etc.   |

该模型允许或涵盖指定的要求：

我需要能够创建一个计数器，就像自动递增的数字一样，来计算每辆汽车的零件。例如，汽车1可能有零件1、2、3...而汽车2也会有零件1、2、5、7...（沿途可能会删除一些零件）。

从Car_has_Part表中通过CarId选择所有PartId。

每个零件必须分别计算其相关的汽车。这是基本要求。

与上述相同（不像您的示例那样重复数据）。添加、删除关系或修改零件名称也变得更加容易 - 您只需要更新Parts表中的一行即可，更改将反映在每辆汽车上。

关于触发器的问题 - 您只能使用EF（使用代码优先方法）创建触发器。关于执行 - 触发器始终在数据库中执行，EF无法控制触发器执行（您可以使用原始SQL查询启用/禁用触发器，但如果我正确理解您的问题，这不是您想要的）。
谢谢，但这并不能解决我的问题...在我的例子中，每个部分，比如门，都与其他部分无关。每扇门都是不同的制造商，寿命也不同... - ilans
我建议你制作例子，画出模型的图像，并使用纸笔填写表格并创建关系，并查看哪种模型最符合你的要求。 - keenthinker
1
那么你的问题或要求不够清晰。什么是“一系列零件”？我的解决方案确实涵盖了两个指定的问题 - 对于每辆汽车分别计算每个零件的数量。 - keenthinker
让我们在聊天中继续这个讨论。点击此处进入聊天室。 - keenthinker
让我们在聊天中继续这个讨论：http://chat.stackoverflow.com/rooms/105508/discussion-between-ilans-and-pasty。 - ilans
显示剩余9条评论
0

这不是一个真实的例子，所以你感到困惑。例如，一个真实的零件实体比这复杂得多。一个真实的零件将有一个制造商ID（宝马、奥迪等）、零件号（B4-773284-YT）、车型ID（奥迪A4等）、描述、制造年份等等。通常在涉及零件实体时，我们使用制造商ID和零件号的连接主键。

与您的汽车表相同。这也不是一个真实的例子。汽车实体应该有一个唯一的VIN号码。当您说每个部分都是特定的时，您并不是在谈论Part实体。您正在谈论PartInventory实体。PartInventory为每个零件提供了一个唯一的序列号（条形码）。因此，每个零件都可以被唯一地识别。当您将零件附加到车辆上时，您不仅仅是附加一个零件，实际上您正在附加一个可通过唯一序列号识别的PartInventory项目。

一旦PartInventory项目附加到车辆上，它就成为车辆的配件项目。这意味着该零件被转移到VehicleParts表中。

很不幸，我发现您在车辆行业领域知识方面存在许多空白。我们开发系统来解决现实问题。当您试图解决假设性问题时，就会遇到这种问题。这导致许多其他人试图帮助您，并浪费了他们的时间。
我并不打算提供一个真实世界的例子，这只是一个示例。目标是了解如何创建计数器。我在问题中添加了第二个注释以澄清这一点。 - ilans
通过查看您的“OrdersTable”，我可以理解您想要做什么。通常我们称这种编号为“行号”或“行ID”。但使用其他名称也完全可以。因此，您需要了解的最重要的部分是，行号不应该通过数据库递增。您必须在前端中有一些逻辑来计算行号。因此，在您的数据库中，“OrderCountForSupplier”只是一个整数。您必须在“orderId，SupplierId，OrderCountForSupplier”字段上有一个连接的主键。 - Kosala W
我似乎找不到任何与前端相关的关系……这可能也是一个底层服务。当然，它必须在数据库中递增....无论如何，是的-我本可以使用复合键而不是OrderId。我没有这样做-所以聚光灯不会集中在复合键上，就像我用Cars-Parts时那样。但是......我仍然找不到完整的答案...... - ilans
我不建议任何人在触发器中编写业务规则。你必须在业务逻辑层中编写它们。 - Kosala W
我需要对我的问题得到完整的答案。我仍然没有得到完整的答案。不是评论... - ilans
显示剩余9条评论
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接