力的EntityFramework使用输出功率不SCOPE_IDENTITY检索键值 [英] Force EntityFramework to use OUTPUT not SCOPE_IDENTITY to retrieve key value

问题描述

我使用实体框架代码首先访问一组表的按键由一个int基于序列中的默认约束进行设置。 EF英孚教育似乎有麻烦处理这一点，就坚持使用SCOPE_IDENTITY插入后填充整数重点领域

挖掘到的代码，它看起来有点硬编码：

的 http://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework.SqlServer/SqlGen/DmlSqlGenerator.cs

查看IsValidScopeIdentityColumnType方法略超过一半下来的页面。如果这个方法返回true，插入Key值与SCOPE_IDENTITY（）检索，否则会产生一个OUTPUT子句。 （GUID /唯一标识符是典型的使用情况下，有）。

  //确保它是一种最原始的
如果（ ！typeUsage.EdmType.BuiltInTypeKind = BuiltInTypeKind.PrimitiveType）
 {
返回FALSE; 
} 
 
 //检查，如果这是一个支持的基本类型（按名称比较）
变种的typeName = typeUsage.EdmType.Name; 
 
 //整数类型
如果（typeName的==TINYINT
 ||的typeName ==SMALLINT
 || 
的typeName == INT
 ||的typeName ==BIGINT）
 {
返回真; 
} 
  

有什么办法来欺骗这种方法进入返回false为一体场？一旦我开始看到的东西像'EDMType我超越了我真正了解关于EF映射如何工作。也许有一些方法来使用用户定义类型愚弄呢？但它确实在需要某种更新.NET端的配置。

又见UseGeneratedValues​​Variable方法在哪里这是使用的同一个文件... < ？为什么输出不是对面这里的主板使用/ p>

目前还不清楚，我 - 也许性能

解决方案

更新 - 唯一的身份支持DB GENERATED PK

您可以创建标志着一个键列计算，其中有一个 DataseGeneratedOption.Computed 。 （请参见 DataseGeneratedOption枚举）。

为了说明这一点，你可以用装饰的 DatabaseGeneratedAttribute ，或使用流利的API，在 OnModelCreating 您的DbContext的方法，像这样：

  modelBuilder.Entity<的EntityType>（）
 .Property（C => c.KeyColumn）
 .HasDatabaseGeneratedOption（DatabaseGeneratedOption.Computed）
  

此示例代码工作完全与EF6.1

 公共类MyDbContext：的DbContext 
 {
公共ID​​bSet< ; ComputedKey> ComputedKeys {搞定;组; } 
 
保护覆盖无效OnModelCreating（DbModelBuilder模型构建器）
 {
 base.OnModelCreating（模型构建器）; 
 //已计算的关键：
 modelBuilder.Entity< ComputedKey>（）
 .HasKey（C => c.Id）
 .Property（C => c.Id ）
 .HasDatabaseGeneratedOption（DatabaseGeneratedOption.Computed）; 
} 
} 
 
公共类ComputedKey 
 {
公众诠释标识{搞定;组; } 
} 
  

当你运行应用程序，该表正确创建。

当您尝试的第一个实体添加到实体收集和保存更改，就会出现问题。你得到：修改的表，其中一个主键列有物业StoreGeneratedPattern设置为计算不被支持。使用身份而不是模式。键列：ID。表：CodeFirstDatabaseSchema.ComputedKey

这是EF（直到6.1）的限制，只允许有整型或GUID作为。DB的PK生成的值

WORKAROUNDS

第一：
一个是使用在DB产生的替代键的列。

从EF 6.1你可以创建AK，装潢该AK柱像这样的属性：

  [指数（MyIndex，唯一的：真）] 
  

二：
之所以使用序列被确定种子和增量。如果这是你需要什么，你可以修改的身份是这样的：

  DBCC CHECKIDENT（'SchemaName.TableName'，RESEED，10 ）; 
  

这意味着未来生成的标识值将是11，并且增量为1。

如果您需要使用不同的增量你需要删除并重新创建标识列，说明种子和增量。但这样做，你还需要删除并创建关联的foreingk键，所以这是太难以落实

第三：
。您可以使用触发器。触发器内，您可以使用 SET IDENTITY_INSERT tableName值ON / OFF ，但话又说回来，你可以有问题，因为 @@身份，将不匹配

的注意：如果你需要运行自定义SQL命令将此定制，你需要实现您的数据库初始化种子法

结论

这场景只是部分支持，因此你'宁愿找一个替代的解决方案，除非你以前的变通工作之一。

请求此功能

如果你有兴趣在此功能，请进入实体框架特性的建议，并投票支持这一点：的允许使用SQL Server 2012的序列生成主键

I'm using Entity Framework code first to access a set of tables whose key will be set by an int based SEQUENCE in a default constraint. EF seems to have trouble handling this, it insists on using SCOPE_IDENTITY after an insert to populate integer key fields.

Digging into the code, it looks kind of hard-coded:

http://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework.SqlServer/SqlGen/DmlSqlGenerator.cs

See the IsValidScopeIdentityColumnType method a little over halfway down the page. If this method returns true the inserted Key value is retrieved with SCOPE_IDENTITY(), otherwise an OUTPUT clause is generated. (Guid/uniqueidentifier is the typical use case there).

        // make sure it's a primitive type
        if (typeUsage.EdmType.BuiltInTypeKind != BuiltInTypeKind.PrimitiveType)
        {
            return false;
        }

        // check if this is a supported primitive type (compare by name)
        var typeName = typeUsage.EdmType.Name;

        // integer types
        if (typeName == "tinyint"
            || typeName == "smallint"
            ||
            typeName == "int"
            || typeName == "bigint")
        {
            return true;
        }

Is there any way to fool this method into returning false for an integral field? Once I start seeing things like 'EDMType' I'm beyond what I really understand about how the EF mapping really works. Maybe there's some way to use a user defined type to fool it? But it's really the configuration on the .NET side that needs some sort of update.

See also the UseGeneratedValuesVariable method in that same file for where this is used...

It's not clear to me why OUTPUT isn't just used across the board here -- maybe performance?

解决方案

UPDATE - ONLY IDENTITY SUPPORTED FOR DB GENERATED PK

You can create a key column marked as computed, which has a DataseGeneratedOption.Computed. (See DataseGeneratedOption enum).

To indicate this you can decorate the column with DatabaseGeneratedAttribute, or use the fluent API, in the OnModelCreating method of your DbContext, like so:

        modelBuilder.Entity<EntityType>()
            .Property(c => c.KeyColumn)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)

This sample code works perfectly with EF6.1

public class MyDbContext : DbContext
{
    public IDbSet<ComputedKey> ComputedKeys { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        // Computed Key:
        modelBuilder.Entity<ComputedKey>()
            .HasKey(c => c.Id)
            .Property(c => c.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
    }
}

public class ComputedKey
{
    public int Id { get; set; }
}

When you run the app, the table is created correctly.

The problem arises when you try to add the first entity to the entity collection and save changes. You get: Modifications to tables where a primary key column has property 'StoreGeneratedPattern' set to 'Computed' are not supported. Use 'Identity' pattern instead. Key column: 'Id'. Table: 'CodeFirstDatabaseSchema.ComputedKey'.

This is a limitation of EF (until 6.1) that only allows to have integer type or GUID as DB generated value for a PK.

WORKAROUNDS

First: One would be to use the column generated on the DB as an alternative key.

From EF 6.1 on you can create the AK, decorating the AK column with an attribute like this:

[Index("MyIndex", unique: true)]

Second: The reason for using a sequence is defining the seed and the increment. If this is what you need you can modify the identity like this:

DBCC CHECKIDENT ('SchemaName.TableName', RESEED, 10);

This means that the next generated identity value will be 11, and the increment will be 1.

If you need to use a different increment you'd need to drop and re-create the identity column, indicating the seed and increment. But for doing this you also need to drop and create the associated foreingk keys, so that's too hard to implement.

Third: You could use a trigger. Inside the trigger you can use SET IDENTITY_INSERT tableName ON/OFF, but then again you can have problems because the @@identity will mismatch.

NOTE: if you need to run custom SQL commands to apply this customizations, you need to implement the Seed method of your db initializer

CONCLUSION

This scenario is only partially supported, so you'd rather find an alternative solution, unless one of the previous work arounds work for you.

REQUEST THIS FUNCTIONALITY

if you're interested in this functionality, go to the Entity Framework Feature Suggestions, and vote for this: Allow using SQL Server 2012 sequence for generating primary key

这篇关于力的EntityFramework使用输出功率不SCOPE_IDENTITY检索键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！