在Azure上使用EntityFramework CodeFirst迁移更改PK数据类型的问题 [英] Problems changing PK datatype using EntityFramework CodeFirst migrations on Azure

问题描述

我在Azure上托管了一个代码优先的EntityFramework项目。目前，我通过

1. 从包管理器控制台运行添加迁移
   
   
2. 运行更新数据库从程序包管理器控制台获取一个.sql脚本的脚本


3. 在我的开发环境中测试后，在Azure上手动运行脚本

到目前为止，这一切都很顺利。但是我遇到一些严重的问题，试图将一个表的主键从一个int更改为一个长的。

这里是从EntityFramework

  ALTER TABLE [ico]。[AccountFeedbacks] ALTER COLUMN [GameId] [bigint] 
 ALTER TABLE [ico ] [游戏] ALTER COLUMN [Id] [bigint] NOT NULL 
 ALTER TABLE [ico]。[Accounts] ALTER COLUMN [LastWarningGameId] [bigint] 
 ALTER TABLE [ico]。[AccountGameRecords] ALTER COLUMN [GameId] [bigint] NOT NULL 
 ALTER TABLE [ico]。[GameFeedbacks] ALTER COLUMN [GameId] [bigint] NOT NULL 
 
 --__迁移历史排除
  

由于INDEX和FK CONSTRAINTS，运行此脚本将无法正常运行。所以首先我手动修改了脚本：

  DROP INDEX [IX_GameId] ON [ico]。[GameFeedbacks] 
 DROP INDEX [IX_GameId] ON [ico]。[AccountGameRecords] 
 DROP INDEX [IX_GameId] ON [ico]。[AccountFeedbacks] 
 DROP INDEX [IX_LastWarningGameId] ON [ico]。[Accounts] 
 ALTER TABLE [ico]。[AccountFeedbacks] DROP CONSTRAINT [FK_ico.AccountFeedbacks_ico.Games_GameId] 
 ALTER TABLE [ico]。[Accounts] DROP CONSTRAINT [FK_ico.Accounts_ico.Games_LastWarningGameId] 
 ALTER TABLE [ico ] [GameFeedbacks] DROP CONSTRAINT [FK_ico.GameFeedbacks_ico.Games_GameId] 
 ALTER TABLE [ico]。[AccountGameRecords] DROP CONSTRAINT [FK_ico.AccountGameRecords_ico.Games_GameId] 
 ALTER TABLE [ico]。[游戏] DROP CONSTRAINT [PK_ico.Games] 
 
 ALTER TABLE [ico]。[AccountFeedbacks] ALTER COLUMN [GameId] [bigint] 
 ALTER TABLE [ico]。[游戏] ALTER COLUMN [Id] [ bigint] NOT NULL 
 ALTER TABLE [ico]。[Accounts] ALTER COLUMN [LastWarningGameId] [bigint] 
 ALTER TABLE [ico]。 [AccountGameRecords] ALTER COLUMN [GameId] [bigint] NOT NULL 
 ALTER TABLE [ico]。[GameFeedbacks] ALTER COLUMN [GameId] [bigint] NOT NULL 
 
 ALTER TABLE [ico]。 [游戏] ADD CONSTRAINT [PK_ico.Games]主键（[Id]）
 ALTER TABLE [ico]。[AccountGameRecords] ADD CONSTRAINT [FK_ico.AccountGameRecords_ico.Games_GameId] FOREIGN KEY（[GameId]）REFERENCES [ico] [游戏]（[Id]）ON DELETE CASCADE 
 ALTER TABLE [ico]。[GameFeedbacks] ADD CONSTRAINT [FK_ico.GameFeedbacks_ico.Games_GameId] FOREIGN KEY（[GameId]）参考[ico]。[游戏] [Id]）ON DELETE CASCADE 
 ALTER TABLE [ico]。[Accounts] ADD CONSTRAINT [FK_ico.Accounts_ico.Games_LastWarningGameId] FOREIGN KEY（[LastWarningGameId]）REFERENCES [ico]。[游戏]（[Id]）
 ALTER TABLE [ico]。[AccountFeedbacks] ADD CONSTRAINT [FK_ico.AccountFeedbacks_ico.Games_GameId] FOREIGN KEY（[GameId]）REFERENCES [ico]。[游戏]（[Id]）
 CREATE INDEX [IX_LastWarningGameId] ON [ico]。[Accounts]（[LastWarningGameId]）
 CREATE INDEX [IX_GameId] ON [ico]。[AccountFeedbacks]（[GameId]）
 CREATE INDEX [IX_GameId] ON [ico]。[AccountGameRecords]（[GameId]）
 CREATE INDEX [IX_GameId] ON [ico]。[GameFeedbacks ]（[GameId]）
 
 --__迁移历史排除
  

不知道这是否是最好的解决方案。但它似乎在我的开发机器上工作。不幸的是，它不会在Azure上工作，因为在这个版本的SQL Server中不支持没有聚集索引的表，请创建一个聚簇索引，然后再试一次。这是由于ALTER TABLE [ico]。[游戏] DROP CONSTRAINT [PK_ico.Games]。任何建议？

解决方案

使用Azure，我刚刚结束了删除表（从我的上下文中删除），然后重新脚手架它。当然，如果您能承受损失游戏桌中的所有数据，这只能起作用。对我来说，我决定在部署之前更改我的主键，因此我只在表中有测试数据。

I have a code-first EntityFramework project hosted on Azure. Currently I handle migrations by

1. Run Add-Migration from Package Manager Console
2. Run Update-Database -script from Package Manager Console getting me an .sql script
3. Manually run the script on Azure after testing on my dev environment

This has been working pretty smoothly so far. But I am running into some serious issues trying to change a primary key of one of my tables from an int to a long.

Here is the migration script that got generated from EntityFramework

ALTER TABLE [ico].[AccountFeedbacks] ALTER COLUMN [GameId] [bigint]
ALTER TABLE [ico].[Games] ALTER COLUMN [Id] [bigint] NOT NULL
ALTER TABLE [ico].[Accounts] ALTER COLUMN [LastWarningGameId] [bigint]
ALTER TABLE [ico].[AccountGameRecords] ALTER COLUMN [GameId] [bigint] NOT NULL
ALTER TABLE [ico].[GameFeedbacks] ALTER COLUMN [GameId] [bigint] NOT NULL

--__MigrationHistory excluded

Running this script won't work at all due to INDEX and FK CONSTRAINTS. So firstly I manually changed the script as so:

DROP INDEX [IX_GameId] ON [ico].[GameFeedbacks]
DROP INDEX [IX_GameId] ON [ico].[AccountGameRecords]
DROP INDEX [IX_GameId] ON [ico].[AccountFeedbacks]
DROP INDEX [IX_LastWarningGameId] ON [ico].[Accounts]
ALTER TABLE [ico].[AccountFeedbacks] DROP CONSTRAINT [FK_ico.AccountFeedbacks_ico.Games_GameId]
ALTER TABLE [ico].[Accounts] DROP CONSTRAINT [FK_ico.Accounts_ico.Games_LastWarningGameId]
ALTER TABLE [ico].[GameFeedbacks] DROP CONSTRAINT [FK_ico.GameFeedbacks_ico.Games_GameId]
ALTER TABLE [ico].[AccountGameRecords] DROP CONSTRAINT [FK_ico.AccountGameRecords_ico.Games_GameId]
ALTER TABLE [ico].[Games] DROP CONSTRAINT [PK_ico.Games]

ALTER TABLE [ico].[AccountFeedbacks] ALTER COLUMN [GameId] [bigint]
ALTER TABLE [ico].[Games] ALTER COLUMN [Id] [bigint] NOT NULL
ALTER TABLE [ico].[Accounts] ALTER COLUMN [LastWarningGameId] [bigint]
ALTER TABLE [ico].[AccountGameRecords] ALTER COLUMN [GameId] [bigint] NOT NULL
ALTER TABLE [ico].[GameFeedbacks] ALTER COLUMN [GameId] [bigint] NOT NULL

ALTER TABLE [ico].[Games] ADD CONSTRAINT [PK_ico.Games] PRIMARY KEY ([Id])
ALTER TABLE [ico].[AccountGameRecords] ADD CONSTRAINT [FK_ico.AccountGameRecords_ico.Games_GameId] FOREIGN KEY ([GameId]) REFERENCES [ico].[Games] ([Id]) ON DELETE CASCADE
ALTER TABLE [ico].[GameFeedbacks] ADD CONSTRAINT [FK_ico.GameFeedbacks_ico.Games_GameId] FOREIGN KEY ([GameId]) REFERENCES [ico].[Games] ([Id]) ON DELETE CASCADE
ALTER TABLE [ico].[Accounts] ADD CONSTRAINT [FK_ico.Accounts_ico.Games_LastWarningGameId] FOREIGN KEY ([LastWarningGameId]) REFERENCES [ico].[Games] ([Id])
ALTER TABLE [ico].[AccountFeedbacks] ADD CONSTRAINT [FK_ico.AccountFeedbacks_ico.Games_GameId] FOREIGN KEY ([GameId]) REFERENCES [ico].[Games] ([Id])
CREATE INDEX [IX_LastWarningGameId] ON [ico].[Accounts]([LastWarningGameId])
CREATE INDEX [IX_GameId] ON [ico].[AccountFeedbacks]([GameId])
CREATE INDEX [IX_GameId] ON [ico].[AccountGameRecords]([GameId])
CREATE INDEX [IX_GameId] ON [ico].[GameFeedbacks]([GameId])

--__MigrationHistory excluded

I'm not sure if this is the best solution. But it does seem to work on my dev machine. Unfortunately it will not work on Azure due to "Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.". This is due to ALTER TABLE [ico].[Games] DROP CONSTRAINT [PK_ico.Games]. Any suggestions?

解决方案

With Azure, I just ended up dropping the table (removing it from my Context) and then re-scaffolding it. This only works if you can afford to lose all the data in your Games table, of course. For me, I decided to change my Primary Key before deployment and thus I only had test data in my table.

这篇关于在Azure上使用EntityFramework CodeFirst迁移更改PK数据类型的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！