通过使用npgsql的代码优先模型创建表的错误

问题描述

使用npgsql通过代码优先模型创建表的问题

Problem with tables creation by code first model with npgsql

数据库创建应从2.2.0-beta1版本开始:«DavidKarlaš添加了对EF6 +中的EFMigration和数据库创建的支持现在可以启动Code First项目，而无需先创建数据库.EntityFramework和Npgsql会处理它.Emil Lenngren添加了对EntityFramework许多缺少功能的支持.» https://www.nuget.org/packages/Npgsql/2.2.0-beta1

Database creation should work from version 2.2.0-beta1:«David Karlaš added support for EFMigration and Database creation in EF6+ Now it is possible to start Code First projects without needing to create a database upfront. EntityFramework and Npgsql will take care of it. Emil Lenngren added support for a lot of missing features of EntityFramework.»https://www.nuget.org/packages/Npgsql/2.2.0-beta1

但是，当我尝试执行此操作时，我遇到了问题.

But, when I tried to do this I faced with problems.

首先，我做了一个简单的项目，该项目可与SqlServer一起使用:我创建了Asp.Net MVC项目(VS2013)，并添加了一些代码:

Firstly, I did the simple project, that works with SqlServer:I created Asp.Net MVC project (VS2013) and add some code:

public class Movie
{
    public int ID { get; set; }
    public string Title { get; set; }
}

public class MovieDBContext : DbContext
{
    public DbSet<Movie> Movies { get; set; }
}

public class MovieDBInitializer : DropCreateDatabaseIfModelChanges<MovieDBContext>
{
}

public class HomeController : Controller
{
    private MovieDBContext db = new MovieDBContext();

    public ActionResult Index()
    {
        List<Movie> objs = db.Movies.ToList();
        return View(objs);
    }

WebConfig:

WebConfig:

  <connectionStrings>
    <add name="MovieDBContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=MovieCreateDbInSql;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\MovieDB.mdf" providerName="System.Data.SqlClient" />  
  </connectionStrings>

  <entityFramework>
    <contexts>
      <context type="MovieCreateDbInSql.Models.MovieDBContext, MovieCreateDbInSql">
        <databaseInitializer type="MovieCreateDbInSql.Models.MovieDBInitializer, MovieCreateDbInSql" />
      </context>
    </contexts>

    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>

启动项目时，将创建数据库MovieDB.

When starting the project the database MovieDB is created.

一切正常.

我们可以在项目的App_Data文件夹中看到此数据库.好.

We can see this database in App_Data folder in the project.Good.

然后我尝试使用npgsql进行同样的操作.

Then I tried to do the same with npgsql.

1. 添加库:

EntityFramework6.Npgsql.dll(版本3.1.0.0)

EntityFramework6.Npgsql.dll (version 3.1.0.0)

Npgsql.dll(版本3.1.2.0)

Npgsql.dll (version 3.1.2.0)

2.

比更改WebConfig:

Than change WebConfig:

  <connectionStrings><add name="MovieDBContext" connectionString="Server=127.0.0.1;Port=5432;Database=postgres;User Id=postgres;Password=postgres000;" providerName="Npgsql" /></connectionStrings>

  <entityFramework>
    <contexts>
      <context type="MovieCreateDbInSql.Models.MovieDBContext, MovieCreateDbInSql">
        <databaseInitializer type="MovieCreateDbInSql.Models.MovieDBInitializer, MovieCreateDbInSql" />
      </context>
    </contexts>
    <defaultConnectionFactory type="Npgsql.NpgsqlFactory, Npgsql" />
    <providers>
      <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql"></provider>
    </providers>
  </entityFramework>

  <system.data>
    <DbProviderFactories>
      <remove invariant="Npgsql" />
      <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql"
           type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>

开始.错误:

System.NotSupportedException was unhandled by user code
  HResult=-2146233067
  Message=Model compatibility cannot be checked because the database does not contain model metadata. Model compatibility can only be checked for databases created using Code First or Code First Migrations.
  Source=EntityFramework

但是此配置对于SqlServer足够了！

But this configuration was enough for SqlServer!

好的.试试这个:

EF5获取此错误消息:无法检查模型兼容性，因为数据库不包含模型元数据

1.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
}

不是真的有帮助.错误是一样的

Don't really help. The error is the same

1. 公共类MovieDBInitializer:DropCreateDatabaseAlways{}

1. public class MovieDBInitializer : DropCreateDatabaseAlways{}

新错误:

Npgsql.PostgresException was unhandled by user code
  HResult=-2147467259
  Message=55006: база данных "postgres" занята другими пользователями
  Source=Npgsql
  ErrorCode=-2147467259
  BaseMessage=база данных "postgres" занята другими пользователями
  Code=55006
  Detail=Эта база данных используется ещё в 1 сеансе.
(error 55006 database is being accessed by other user)

此错误也不是很好.据我了解，此错误是因为与原始的localdb.sql不同，我们拥有严重的数据库posgresql.而且在postgresql中删除db操作并不像在localdb.sql中那样简单.

This error is not good too.As far as I understand this error is because we have serious database posgresql unlike primitive localdb.sql.And dropping db operation in postgresql is not so easy as in localdb.sql.

我发现了有关该错误的几个链接:

I found several links on that error:

https://groups.google.com/forum/#！topic/npgsql-help/1f5niOiHpGg

将数据库删除为其他用户正在访问的数据库?

1. npgsql和实体框架代码优先设置的问题

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    Database.SetInitializer<MovieDBContext>(null);
}

再次出现相同错误:

System.NotSupportedException was unhandled by user code
  HResult=-2146233067
  Message=Model compatibility cannot be checked because the database does not contain model metadata. Model compatibility can only be checked for databases created using Code First or Code First Migrations.
  Source=EntityFramework

我该怎么做才能有机会通过代码优先模型创建表?

What should I do to have the opportunity to create tables by the code first model?

我当然可以在SqlServer中生成数据库，然后将脚本转换为postgresql，但是我想使用npgsql来实现.

Of course I can generate the database in SqlServer and than convert scripts to postgresql, but I want do this with npgsql.

推荐答案

我使用的库:

• EntityFramework.6.1.3
• EntityFramework6.Npgsql.3.1.0
• Npgsql.3.1.3

我的数据库上下文:

public class CustomContext : DbContext
{

    public CustomContext()
        : base("name=CustomContext")
    {
        Database.SetInitializer(
            new MigrateDatabaseToLatestVersion<CustomContext, Configuration>());
    }

    public DbSet<Movie> Movies { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Change schema name from "dbo" => "public" for all entities (except MigrationHistory).
        // MigrationHistory schema name is modified in custom HistoryContext 
        modelBuilder.Types().Configure(c => c.ToTable(c.ClrType.Name, "public"));
    }

}

迁移历史记录表的默认架构设置为"dbo".如果要在其他架构(例如公共)中使用它，则必须创建一个自定义的HistoryContext.
当前，无法使用 modelBuilder.HasDefaultSchema("public")设置默认架构.使用自动迁移(AFAIK)时.

Migration history table has a default schema set to "dbo". If you want to have it in a different schema (ex. public) you have to create a custom HistoryContext.
Currently it's impossible to set a default schema with modelBuilder.HasDefaultSchema("public") while using automatic migrations (AFAIK).

public class CustomHistoryContext : HistoryContext
{
    public CustomHistoryContext(DbConnection existingConnection, string defaultSchema) 
       : base(existingConnection, defaultSchema)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Change schema from "dbo" to "public"
        modelBuilder.Entity<HistoryRow>()
                    .ToTable(tableName: "__MigrationHistory", 
                             schemaName: "public");
    }
}

最后，我的迁移配置类如下:

Finally, my migration configuration class looks like this:

internal sealed class Configuration : DbMigrationsConfiguration<CustomContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;

        // Setting custom historyContext factory.
        // Used for modify __MigrationHistory table schema name from "dbo" to "public"
        this.SetHistoryContextFactory("Npgsql",
            (connection, defaultSchema) => 
                 new CustomHistoryContext(connection, defaultSchema));

    }

    protected override void Seed(CustomContext context)
    {
        //  This method will be called after migrating to the latest version.
    }
}

完成这些步骤后，自动执行代码优先迁移即可正常工作.

After these steps automatic code first migrations work correctly.

这篇关于通过使用npgsql的代码优先模型创建表的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！