代码首先从int迁移到Guid主键问题

本文介绍了代码首先从int迁移到Guid主键问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我正在尝试将代码第一个ID列从int更改为Guid，当尝试运行迁移时，我会收到以下消息： 我正在定义列： 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的答案部分： 更改主键 您要更改的列是一个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确定这将是一个因素：当上述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会失败。 最终解决方案 确保将下面的代码中的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 classI 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-MigrationAlter the class using code similar to aboveRun the migration using Update-DatabaseThe Sql() method sample in the answer looks like:Sql("UPDATE dbo.YourTable SET Column1 = 'VALUE1' ");Changing the column type - generic stepsI 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 typeUse Sql() to take over the data from the original column using an update statementRemove the old columnRename the new columnSequential GUIDsThe 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 nullTake note of privacy concerns by 'Johan' in the comment section of the answer by 'Icarus':Alter primary keyThe 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: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.COLUMNSWHERE [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.The final solutionMake 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主键问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！