无法将StoreGeneratedPattern.Identity与数据库触发器生成的主键值一起使用? [英] Using StoreGeneratedPattern.Identity with database-trigger-generated primarykey values not possible?

问题描述

这个问题与我在这里问的另一个问题(

This question is related to another question i asked here ( Entity Framework 4.2 - How to realize TPT-Inheritance with Database-generated Primarykey Value? ) and should simply clarify, if my assumptions, regarding the problem stated in the topic, are right or not.

问题(详细):

• 我想使用EF(4.1)访问已经存在的数据库
• 数据库对其表的主键值的生成有一些限制(存在一个采用表名并返回下一个可用ID的UDF)
• 为了使自己尽可能容易，我的第一种方法是定义数据库触发器(在插入之前)，这些触发器将调用生成ID的UDF，以在插入新的数据行时设置新的ID.
• 然后，我将我的EDM的csdl中相应实体的StoreGeneratedPattern属性设置为"Identity"，以便在将新生成的ID保存到数据库之后，在实体对象中设置新的ID.

其结果是:

当我创建一个新的实体对象，将其添加到DbContext并在其上调用SaveChanges时，相应的数据行已插入数据库中，但是该实体未使用新的数据库生成的ID更新.当我尝试一次保存更多彼此关联(父-子)的邮件时，我意识到了这一点，因为子实体的外键属性无法正确设置，因为父级的新ID不为人所知. DbContext.

When I created a new entity object, added it to the DbContext and called SaveChanges on it, the corresponding datarow was inserted in the database, but the entity was not updated with the new database-generated ID. I realized this when i tried to save more at once, that have associations to each other (parent-child), because the foreignkey properties of the child entities could not be set correctly, since the new ID of the parent was not known to the DbContext.

这就是我问上述有关TPT继承的问题的原因.

This is the reason I asked the above mentioned question concerning TPT inheritance.

经过几天的研究，并尝试了解决这个问题的所有想法，我想我意识到，这根本行不通.尽管MSDN上StoreGeneratedPattern枚举的文档和博客中的一些解释都提出了建议，但应该将StoreGeneratedPattern.Identity设置为检索生成的值，但是当DB在插入新行时生成值时，对于主键结合使用则不是这样使用数据库触发器.

After several days of research and trying everything that came to my mind to solve this problem, i think i realized, that this simply cannot work. Although the documentation of the StoreGeneratedPattern enum at MSDN and several explanations in blogs suggests, that StoreGeneratedPattern.Identity should be set to retrieve the generated value, when the DB generates a value on inserting a new row, this is not true for primary keys in conjunction with database triggers.

考虑了很长时间之后，对我来说这完全合乎逻辑，因为EF需要一些标准来检索数据库生成的值，而且我认为在大多数情况下这将是实体的身份.对于设置为自动递增的数据库列(或标识列，...)，可能没有问题，因为DBMS提供了一些功能来检索最后插入的标识值(例如，MSSQL中的@@ identity).但是，当使用触发器生成新的标识值时，EF显然不知道如何查询新插入的行(而且我也无法想象有任何良好的独立于数据库的方式来执行此操作).

After thinking about that a long time, that seems perfectly logical to me, since the EF needs some criterium to retrieve database-generated values, and i think that would be in most cases the identity of an entity. For databasecolumns that are set to autoincrement (or identity-column, ...) that might be no problem, because the DBMS provides some functionality to retrieve the last inserted identity-value (e.g. @@identity in MSSQL). But when using a trigger to generate a new identity-value, the EF obviously doesnt know how to query the newly inserted row (and i cant imagine any good db-independent way to do this either).

所以我的实际问题是:以上假设是否正确，或者我在这里忽略了重要的事情?

So my actual question is: are the assumptions above correct or am I overlooking something important here?

在此先感谢您的澄清/启发.

Thanks in advance for any clarification/inspiration on this.

编辑(后续问题):

在阅读了拉迪斯拉夫的回答后，又出现了一个问题:

After reading the answer from Ladislav another question arises:

如果我在CSDL中设置了StoreGeneratedPattern，是否必须在SSDL中将其设置为相同的值(反之亦然)?用于edm设计器的补丁程序就是这种情况，因为当您在CSDL中更改(通过设计器)它会自动同步SSDL中的StoreGeneratedPattern.

If I set StoreGeneratedPattern in CSDL, do I have to set it to the same value in SSDL (and vice versa)? The patch for the edm designer implies that this is the case, because it automatically synchronizes the StoreGeneratedPattern in SSDL when you change it in CSDL (through the designer).

推荐答案

StoreGeneratedPattern.Identity应该可以工作.如果您在EF Designer中进行了设置，请确保已在EDMX文件的SSDL和CSDL部分中对其进行了正确配置(以XML格式打开以进行检查). EF设计器中存在一个错误，该错误仅导致CSDL中的正确设置，因此SQL部分不知道插入后必须从数据库中选择新ID.在某些罕见的情况下，此错误已通过安装VS 2010 SP1 ，并且应该通过特殊补丁解决.

The StoreGeneratedPattern.Identity should work. If you set it in EF Designer make sure that it was correctly configured in both SSDL and CSDL parts of EDMX file (open it as XML to check it). There was a bug in EF designer which caused correct setting only in CSDL so SQL part didn't know that new ID must be selected from the database after insert. This bug was in some rare cases solved by installing VS 2010 SP1 and it should be definitely solved by special patch.

这篇关于无法将StoreGeneratedPattern.Identity与数据库触发器生成的主键值一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！