代码首先从int迁移到Guid主键问题 [英] Code First migration from int to Guid primary key issue

查看：140 发布时间：2017/8/22 22:33:44 entity-framework-6

本文介绍了代码首先从int迁移到Guid主键问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试将代码第一个ID列从int更改为Guid，当尝试运行迁移时，我会收到以下消息：

<$标识列'CustomFieldId'必须是数据类型为int，bigint，smallint，tinyint或小数或数字，数字为0，并且被限制为不可用。

我正在定义列：

  public partial class CustomField：BaseEntity 
 {
 
 [Key，DatabaseGenerated（DatabaseGeneratedOption.Identity）] 
 public Guid CustomFieldId {get ;组;

在CustomFieldMapping.cs中将其映射如下：

  public CustomFieldMapping（）
 {
 //主键
 HasKey（t => t.CustomFieldId）; 
 
 //限制
属性（t => t.CustomFieldId）.HasDatabaseGeneratedOption（DatabaseGeneratedOption.Identity）;

生成的迁移是尝试这样做：

  public override void Up（）
 {
 DropForeignKey（dbo.CustomField，CustomFormId，dbo.CustomForm）; 
 DropForeignKey（dbo.CustomData，CustomFieldId，dbo.CustomField）; 
 DropForeignKey（dbo.CustomForm，ParentFormId，dbo.CustomForm）; 
 DropIndex（dbo.CustomField，new [] {CustomFormId}）; 
 DropIndex（dbo.CustomForm，new [] {ParentFormId}）; 
 DropIndex（dbo.CustomData，new [] {CustomFieldId}）; 
 DropPrimaryKey（dbo.CustomField）; 
 DropPrimaryKey（dbo.CustomForm）; 
 AlterColumn（dbo.CustomField，CustomFieldId，c => c.Guid（nullable：false））; 
 AlterColumn（dbo.CustomField，SortOrder，c => c.Int（nullable：false））; 
 AlterColumn（dbo.CustomForm，CustomFormId，c => c.Guid（nullable：false））; 
 AlterColumn（dbo.CustomForm，ParentFormId，c => c.Guid（））; 
 AddPrimaryKey（dbo.CustomField，CustomFieldId）; 
 AddPrimaryKey（dbo.CustomForm，CustomFormId）; 
 CreateIndex（dbo.CustomField，CustomForm_CustomFormId）; 
 CreateIndex（dbo.CustomForm，ParentFormId）; 
 CreateIndex（dbo.CustomData，CustomField_CustomFieldId）; 
 AddForeignKey（dbo.CustomField，CustomForm_CustomFormId，dbo.CustomForm，CustomFormId）; 
 AddForeignKey（dbo.CustomData，CustomField_CustomFieldId，dbo.CustomField，CustomFieldId）; 
 AddForeignKey（dbo.CustomForm，ParentFormId，dbo.CustomForm，CustomFormId）;

我希望它是顺序增加的Guid。我做错了什么？

解决方案

为了解决这个问题，我在Up（）和Down ）方法的迁移类。 Up（）方法中的SQL命令字符串将删除ID列上的主键约束，删除int类型的ID列，然后添加一个类型为Guid的新ID列。 Down（）方法执行相同的操作，但删除Guid列并添加一个新的int列。

我在Stack Overflow中找到了一些解决方案键入，方法是在查询窗口中运行SQL命令。为了解决你的评论我们只是想保持干净/清晰的迁移路径跟踪，当我们做了什么并不总是容易的SQL在你的问题，我使用Up（）和Down（）迁移方法中的SQL命令。对于我来说，这个解决方案在我的项目中运行良好。

这个答案底部的解决方案是由几个Stack Overflow问题/答案构成的。跳到那个代码。以下是长长的细节。

在迁移类中使用SQL命令

我找不到一种使用Entity Framework迁移方法的解决方案，如AlterColumn（），DropColumn（）ONLY。

而不是在 Sql（）'方法我使用'Sql（）'迁移方法中的字符串中的所有SQL命令。使用所有SQL命令可以更容易地在Visual Studio或SQL Server Management Studio中的查询窗口中进行测试。

answer 给出了在所需的迁移类中添加Sql（）方法的起始步骤。

使用Add-Migration生成迁移类

使用与上述相似的代码更改类

使用Update-Database运行迁移

答案中的Sql（）方法示例如下所示：

  Sql（UPDATE dbo.YourTable SET Column1 ='VALUE1'）;

更改列类型 - 通用步骤

我使用JustAnotherUserYouMayKnow的答案开始更改列类型的步骤。我没有明确地遵循，但它只是提供了一个基本框架，需要放一个列并重新创建它。

添加一个新的列与您的新类型

使用Sql（）使用更新语句从原始列中接管数据

删除旧列
重命名新列

顺序GUID

回答从'Icarus'提供了ALTER TABLE语句，使用 newsequentialid（）根据您的语句生成顺序GUID我希望它是一个顺序增加的Guid。

  ALTER TABLE your_table 
添加您的列UNIQUEIDENTIFIER DEFAULT newsequentialid（）NOT null

在评论中注意Johan的隐私问题Icarus的答案部分：

如果隐私是一个问题，不要使用newsequentialid（）。可以猜测下一个生成的GUID的值，因此访问与该GUID相关联的数据

更改主键< h2>

您要更改的列是一个ID列，您将其设置为主键。因此，在删除现有ID列之前，您需要使用另一个ALTER TABLE SQL命令删除主键。

请参阅所选的 answer 来自'darnir'为如何更改主键约束使用SQL语法？

  ALTER TABLE< Table_Name> 
 DROP CONSTRAINT< constraint_name> 
 
 ALTER TABLE< Table_Name> 
 ADD CONSTRAINT< constraint_name> PRIMARY KEY（< Column1>，< Column2>）

请参见Oleg确定这将是一个因素：

PRIMARY KEY CONSTRAINT不能更改，您只能将其删除并重新创建。对于大数据集，它可能导致长时间运行，从而导致表不可用。

当上述DROP CONSTRAINT命令为执行。结果窗格列出了自动生成的约束，即使我在ALTER TABLE ... ADD COLUMN命令中使用了特定的约束名称。请参阅此问题为什么SQL不断创建DF约束？如果您有经验，此问题类似的东西。

要解决这个问题，删除约束我用'scubaSteve'的答案从这个问题：如何在不知道其名称的情况下删除SQL默认约束？在这里添加七的注释是SQL命令：

  DECLARE @ObjectName NVARCHAR（100）
 SELECT @ObjectName = OBJECT_NAME（[default_object_id]）FROM SYS.COLUMNS 
 WHERE [object_id] = OBJECT_ID（'[tableSchema]。[tableName]'）AND [name] ='columnName'; 
 IF @ObjectName IS NOT NULL EXEC（'ALTER TABLE [tableSchema]。[tableName] DROP CONSTRAINT'+ @ObjectName）

ScubaSteve的答案中的七的评论。我添加了'if'条件，因为当没有发现约束时EXEC会失败。

要使此脚本幂等添加 IF @ObjectName IS NOT NULL before EXEC command

最终解决方案

确保将下面的代码中的MyTableName，MyColumnName和dbo替换为表名，列名（例如将列名设置为Id）和表模式分别。

  public override void Up（）
 {
 Sql（@
 DECLARE @ObjectName NVARCHAR（100）
 SELECT @ObjectName = OBJECT_NAME（[default_object_id]）FROM SYS.COLUMNS 
 WHERE [object_id] = OBJECT_ID（'[dbo]。[MyTableName]'）AND [name] ='MyColumnName'; 
 IF @ObjectName IS NOT NULL EXEC（'ALTER TABLE [dbo]。[MyTableName] DROP CONSTRAINT'+ @ObjectName）
 
 ALTER TABLE dbo.MyTableName DROP CONSTRAINT PK_MyTableName哥伦布MN MyColumnName 
 
 ALTER TABLE dbo.MyTableName 
添加ID UNIQUEIDENTIFIER DEFAULT（newsequentialid（））NOT NULL 
 CONSTRAINT PK_MyTableName 
 PRIMARY KEY CLUSTERED（[MyColumnName]）
）; 
} 
 
 public override void Down（）
 {
 Sql（@
 DECLARE @ObjectName NVARCHAR（100）
 SELECT @ObjectName = OBJECT_NAME（[default_object_id]）FROM SYS.COLUMNS 
 WHERE [object_id] = OBJECT_ID（'[dbo]。[MyTableName]'）AND [name] ='MyColumnName'; 
 IF @ObjectName IS NOT NULL EXEC（'ALTER TABLE [dbo]。[MyTableName] DROP CONSTRAINT'+ @ObjectName）
 
 ALTER TABLE dbo.MyTableName DROP CONSTRAINT PK_MyTableName，COLUMN Id 
 
 ALTER TABLE MyTableName 
添加MyColumnName int IDENTITY（1，1）NOT NULL 
 CONSTRAINT PK_MyTableName 
 PRIMARY KEY CLUSTERED（[MyColumnName] ASC）
）; 
}

I'm trying to change my code first ID column from 'int' to 'Guid', and when trying to run the migration, I get the message:

Identity column 'CustomFieldId' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

I'm defining the column like this:

public partial class CustomField : BaseEntity
{

    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid CustomFieldId { get; set; }

Mapping it in CustomFieldMapping.cs like this:

public CustomFieldMapping()
{
    //Primary key
    HasKey(t => t.CustomFieldId);

    //Constraints 
    Property(t => t.CustomFieldId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

And the migration that's generated is trying to do this:

public override void Up()
{
    DropForeignKey("dbo.CustomField", "CustomFormId", "dbo.CustomForm");
    DropForeignKey("dbo.CustomData", "CustomFieldId", "dbo.CustomField");
    DropForeignKey("dbo.CustomForm", "ParentFormId", "dbo.CustomForm");
    DropIndex("dbo.CustomField", new[] { "CustomFormId" });
    DropIndex("dbo.CustomForm", new[] { "ParentFormId" });
    DropIndex("dbo.CustomData", new[] { "CustomFieldId" });
    DropPrimaryKey("dbo.CustomField");
    DropPrimaryKey("dbo.CustomForm");
    AlterColumn("dbo.CustomField", "CustomFieldId", c => c.Guid(nullable: false));
    AlterColumn("dbo.CustomField", "SortOrder", c => c.Int(nullable: false));
    AlterColumn("dbo.CustomForm", "CustomFormId", c => c.Guid(nullable: false));
    AlterColumn("dbo.CustomForm", "ParentFormId", c => c.Guid());
    AddPrimaryKey("dbo.CustomField", "CustomFieldId");
    AddPrimaryKey("dbo.CustomForm", "CustomFormId");
    CreateIndex("dbo.CustomField", "CustomForm_CustomFormId");
    CreateIndex("dbo.CustomForm", "ParentFormId");
    CreateIndex("dbo.CustomData", "CustomField_CustomFieldId");
    AddForeignKey("dbo.CustomField", "CustomForm_CustomFormId", "dbo.CustomForm", "CustomFormId");
    AddForeignKey("dbo.CustomData", "CustomField_CustomFieldId", "dbo.CustomField", "CustomFieldId");
    AddForeignKey("dbo.CustomForm", "ParentFormId", "dbo.CustomForm", "CustomFormId");

I would like it to be a sequentially incremented Guid. What am I doing wrong?

解决方案

To solve this problem I used a Sql() method in the Up() and Down() methods of the migration class. The SQL command string in the Up() method removes the primary key constraint on the ID column, drops the ID column of type int and then adds a new ID column with of type Guid. The Down() method does the same thing but drops the Guid column and adds a new int column.

I found a few solutions on Stack Overflow that resolve the "change column type" by running a SQL command in a query window. To address your comment "we're just trying to keep a clean/clear migration path to trace when we did what which is not always easy with SQL" in your question I used SQL commands within the Up() and Down() migration methods. For me this solution works well in my projects.

The solution at the bottom of this answer was constructed from several Stack Overflow questions/answers. Skip to that for just the code. Here are the long-winded details.

Using SQL commands in a migration class

I couldn't find a solution that used Entity Framework migration methods like 'AlterColumn()', 'DropColumn()' ONLY.

Rather than using a mix of migration methods and commands in the 'Sql()' method I used all SQL commands within a string in the 'Sql()' migration method. Using all SQL commands made it easier to test in a query window in Visual Studio or SQL Server Management Studio.

The answer by 'Uchitha' gave the starting steps for adding the Sql() "method within the desired migration class."

Generate migration class using Add-Migration
Alter the class using code similar to above
Run the migration using Update-Database

The Sql() method sample in the answer looks like:

Sql("UPDATE dbo.YourTable SET Column1 = 'VALUE1' ");

Changing the column type - generic steps

I used the answer by 'JustAnotherUserYouMayKnow' to get started on the steps to change the column type. I didn't follow this explicitly but it provided just the basic framework of the need to drop a column and recreating it.

Add a new column with your new type
Use Sql() to take over the data from the original column using an update statement
Remove the old column
Rename the new column

Sequential GUIDs

The answer from 'Icarus' provided the ALTER TABLE statement with the use of newsequentialid() to generate sequential GUIDs as per your statement "I would like it to be a sequentially incremented Guid."

ALTER TABLE your_table
    ADD your_column UNIQUEIDENTIFIER DEFAULT newsequentialid() NOT null

Take note of privacy concerns by 'Johan' in the comment section of the answer by 'Icarus':

If privacy is a concern, do not use newsequentialid(). It is possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID

Alter primary key

The column you want to change is an ID column and you've set it as the primary key. Therefore, before dropping the existing ID column you'll need to remove the primary key using another ALTER TABLE SQL command.

See the selected answer from 'darnir' for "How can I alter a primary key constraint using SQL syntax?"

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

See the note by 'Oleg' to determine if this will be a factor:

PRIMARY KEY CONSTRAINT cannot be altered, you may only drop it and create again. For big datasets it can cause a long run time and thus - table inavailability.

I had problems when the command with DROP CONSTRAINT above was executed. The results pane listed a constraint that was auto-generated even though I'd used a specific constraint name in the ALTER TABLE ... ADD COLUMN command. See this question "Why does SQL keep creating a DF constraint?" and this question if you experience something similar.

To fix the problem with dropping the constraint I used the answer by 'ScubaSteve' from this question: "How to drop SQL default constraint without knowing its name?" With the addition of the note by 'Seven' here are the SQL commands:

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[tableSchema].[tableName]') AND [name] = 'columnName';
IF @ObjectName IS NOT NULL EXEC('ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT ' + @ObjectName)

The comment by 'Seven' in 'ScubaSteve's answer. I added the 'if' condition as at times the EXEC would fail when no constraint was found.

To make this script idempotent add IF @ObjectName IS NOT NULL before EXEC command

The final solution

Make sure to replace 'MyTableName', 'MyColumnName', and 'dbo' in the code below to your table name, column name (e.g. set column name to 'Id') and table schema respectively.

public override void Up()
{
    Sql(@"
        DECLARE @ObjectName NVARCHAR(100)
        SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
        WHERE [object_id] = OBJECT_ID('[dbo].[MyTableName]') AND [name] = 'MyColumnName';
        IF @ObjectName IS NOT NULL EXEC('ALTER TABLE [dbo].[MyTableName] DROP CONSTRAINT ' + @ObjectName)

        ALTER TABLE dbo.MyTableName DROP CONSTRAINT PK_MyTableName, COLUMN MyColumnName

        ALTER TABLE dbo.MyTableName
        ADD Id UNIQUEIDENTIFIER DEFAULT (newsequentialid()) NOT NULL
        CONSTRAINT PK_MyTableName
        PRIMARY KEY CLUSTERED ([MyColumnName])
    ");
}

public override void Down()
{
    Sql(@"
        DECLARE @ObjectName NVARCHAR(100)
        SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
        WHERE [object_id] = OBJECT_ID('[dbo].[MyTableName]') AND [name] = 'MyColumnName';
        IF @ObjectName IS NOT NULL EXEC('ALTER TABLE [dbo].[MyTableName] DROP CONSTRAINT ' + @ObjectName)

        ALTER TABLE dbo.MyTableName DROP CONSTRAINT PK_MyTableName, COLUMN Id

        ALTER TABLE MyTableName
        ADD MyColumnName int IDENTITY(1, 1) NOT NULL
        CONSTRAINT PK_MyTableName
        PRIMARY KEY CLUSTERED ([MyColumnName] ASC)        
    ");
}

这篇关于代码首先从int迁移到Guid主键问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

代码首先从int迁移到Guid主键问题 [英] Code First migration from int to Guid primary key issue

问题描述

在迁移类中使用SQL命令

更改列类型 - 通用步骤

顺序GUID

最终解决方案

Using SQL commands in a migration class

Changing the column type - generic steps

Sequential GUIDs

Alter primary key

The final solution

相关文章

.NET组件控件最新文章

热门教程

热门工具

登录关闭

代码首先从int迁移到Guid主键问题 [英] Code First migration from int to Guid primary key issue

问题描述

在迁移类中使用SQL命令

更改列类型 - 通用步骤

顺序GUID

最终解决方案

Using SQL commands in a migration class

Changing the column type - generic steps

Sequential GUIDs

Alter primary key

The final solution

相关文章

.NET组件控件最新文章

热门教程

热门工具

登录 关闭

登录关闭