如何在Azure Mobile Services项目中初始化代码优先的实体框架数据库 [英] How to initializing the code-first Entity Framework database in an Azure Mobile Services project

问题描述

我正在关注使用实体框架的Azure移动服务中的代码优先迁移。我正在关注文章如何使数据模型更改到.NET后端移动服务，并提供Stack Overflow答案的其他帮助，并且还阅读了代码优先迁移和团队环境中的代码优先迁移并观看了 Migrations - Under the Hood 。

在Visual Studio 2015中，我创建了一个新的Azure Mobile服务，然后通过转到nuget软件包管理器控制台（PMC）启用首次进行代码迁移，并运行启用迁移。然后我建立并运行该项目。然后使数据库使用PMC命令 Add-Migration Initial 创建初始迁移，并使用PMC命令 Update-Database -Verbose - TargetMigration初始。

失败，错误消息

无法在表
'MobileService1.TodoItems'上创建多个聚集索引。在创建另一个之前删除现有的聚集索引
'PK_MobileService1.TodoItems'。

当我使用详细标志我可以看到自动生成的SQL，并确实将其插入到查询中，并对新鲜的数据库运行它会产生相同的错误，因为主键已经提供了一个聚簇索引。 >

  CREATE TABLE [MobileService1]。[TodoItems]（
 [Id] [nvarchar]（128 ）NOT NULL，
 [Text] [nvarchar]（max），
 [Complete] [bit] NOT NULL，
 [版本] rowversion NOT NULL，
 [CreatedAt] [ datetimeoffset]（7）NOT NULL，
 [已更新] [datetimeoffset]（7），
 [已删除] [位] NOT NULL，
 CONSTRAINT [PK_MobileService1.TodoItems] PRIMARY KEY ] 
）
 CREATE CLUSTERED INDEX [IX_CreatedAt] ON [MobileService1]。[TodoItems]（[CreatedAt]）
  

但是提醒我，请稍后再试。进行更改：在 MobileService1.WebApiConfig.Register 中使用替换 Database.SetInitializer（new MobileServiceInitializer（））; p>

  var migrator = new System.Data.Entity.Migrations.DbMigrator（new Migrations.Configuration（）） ; 
 migrator.Update（）; 
  

但是，在进行此更改后，当我运行更新时，我得到完全相同的错误在PMC上的数据库-Verbose -TargetMigration初始化

另一个建议来自Dominique Alexandre对他的问题的评论在本地运行Azure Mobile Server项目是替代 Database.SetInitializer（new MobileServiceInitializer（））; 在 MobileService1.WebApiConfig.Register 与

  Database.SetInitializer（新的MigrateDatabaseToLatestVersion< MobileServiceContext，Migrations.Configuration>（））; 
  

但是我再次得到完全相同的错误。

我应该怎么使用？有没有一个简单的方法通过实体框架的代码优先迁移在Azure Mobile Services中使用？

解决方案

重新看到是因为EF假设主键也是聚集索引，并且没有办法表明不是这种情况。执行自动迁移时，事情会起作用，因为应用启动时，移动服务/应用程序注册一个自定义的SqlGenerator来删除集群索引（以及其他一些重要的事情）。默认情况下，此迁移不会使用此自定义SqlGenerator。

但是，您可以通过在中指定迁移来使用相同的SqlGenerator， Migrations\Configuration.cs file：

  // Mobile Services命名空间：
 / / using Microsoft.WindowsAzure.Mobile.Service.Tables 
 
 // Mobile Apps命名空间：
 // using Microsoft.Azure.Mobile.Server.Tables; 
 
 ---< snip> --- 
 
 public Configuration（）
 {
 AutomaticMigrationsEnabled = false; 
 SetSqlGenerator（System.Data.SqlClient，新的EntityTableSqlGenerator（））; 
} 
  

尝试一下。生成的数据库应包括移动服务/应用程序期望的CreatedAt触发器和其他特定于SQL的设置。让我知道，如果你碰到这样做的问题，我可以进一步看。

I am utterly confused by the use of Entity Frameworks code-first migrations within Azure Mobile Services.

I'm following the article How to make data model changes to a .NET backend mobile service with additional help from Stack Overflow answers, and have also read Code First Migrations and Code First Migrations in Team Environments and watched Migrations - Under the Hood.

In Visual Studio 2015 I make a new Azure Mobile Service, and then enable code-first migrations by going to the nuget Package Manager Console (PMC) and running Enable-Migrations. I then build and run the project. Then to make the database I create an initial migration with the PMC command Add-Migration Initial and apply it with the PMC command Update-Database -Verbose -TargetMigration Initial.

This fails with the error message

Cannot create more than one clustered index on table 'MobileService1.TodoItems'. Drop the existing clustered index 'PK_MobileService1.TodoItems' before creating another.

As I used the Verbose flag I can see the auto-generated SQL and indeed plugging that into a query and running it against a freshly minted database produces the same error because the primary key already provides a clustered index.

CREATE TABLE [MobileService1].[TodoItems] (
    [Id] [nvarchar](128) NOT NULL,
    [Text] [nvarchar](max),
    [Complete] [bit] NOT NULL,
    [Version] rowversion NOT NULL,
    [CreatedAt] [datetimeoffset](7) NOT NULL,
    [UpdatedAt] [datetimeoffset](7),
    [Deleted] [bit] NOT NULL,
    CONSTRAINT [PK_MobileService1.TodoItems] PRIMARY KEY ([Id])
)
CREATE CLUSTERED INDEX [IX_CreatedAt] ON [MobileService1].[TodoItems]([CreatedAt])

However the article does warn me to make a change: replace Database.SetInitializer(new MobileServiceInitializer()); in MobileService1.WebApiConfig.Register with

var migrator = new System.Data.Entity.Migrations.DbMigrator(new Migrations.Configuration());
migrator.Update();

But after making that change I get exactly the same error when I run Update-Database -Verbose -TargetMigration Initial at the PMC.

Another suggestion, from Dominique Alexandre's comment on his question Running Azure Mobile Server project locally is to instead replace Database.SetInitializer(new MobileServiceInitializer()); in MobileService1.WebApiConfig.Register with

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MobileServiceContext, Migrations.Configuration>());

But again I get exactly the same error.

What should I be using? Is there a simple way through Entity Frameworks code-first migrations as used in Azure Mobile Services?

解决方案

The specific error that you're seeing is due to the fact that EF assumes primary keys are also clustered indexes and there's no way to signal that this isn't the case. Things work when you perform automatic migrations because when the app starts, Mobile Services/Apps registers a custom SqlGenerator that removes the clustered index (along with a few other important things). This custom SqlGenerator isn't used by migrations by default.

However, you can tell your migration to use this same SqlGenerator by specifying it in the Migrations\Configuration.cs file:

// Mobile Services namespace:
// using Microsoft.WindowsAzure.Mobile.Service.Tables

// Mobile Apps namespace:
// using Microsoft.Azure.Mobile.Server.Tables;

---<snip>---

    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator("System.Data.SqlClient", new EntityTableSqlGenerator());
    }

Give that a try. The resulting db should include the CreatedAt trigger and other SQL-specific settings that Mobile Services/Apps expects. Let me know if you bump into issues doing this and I can look further.

这篇关于如何在Azure Mobile Services项目中初始化代码优先的实体框架数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！