EFCore.MigrationExtensions.PostgreSQL 8.0.0

EFCore.MigrationExtensions

Adds views, synonyms, stored procedures, etc. (so-called SQL objects) to the EF model. Creates migrations, when those objects are changed. SQL objects are defined as raw SQL in C#-code or in embedded resources. They can be even generated at runtime.

All EF Core model-tracking and application features are supported:

• When SQL-objects change, migrations are generated.
• SQL-objects are applied on Database.Migrate() or Database.EnsureCreated().
• Correct script is generated on dotnet ef migrations script.
• Database is updated on dotnet ef database update.

How to use

• Call UseSqlObjects:
  • either in builder.Services.AddDbContext and DesignTimeDbContextFactory
  • or in DbContext.OnConfiguring
• Create an empty DbDesignTimeServices in your entry point. The class should inherit from CustomNpgsqlDesignTimeServices
• Add SqlObjects to your context in DbContext.OnModelCreating
• Generate migrations / scripts / update DB as usual

Ways to add SqlObjects to the model

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //...

    // Add SqlObject directly
    // Order is used to define the order in which objects are created / updated in DB
    const string Sql = "create or replace view migr_ext_tests.v_view_10 as select * from migr_ext_tests.my_table;";
    modelBuilder.AddSqlObjects(new SqlObject(Name: "v_view_10", SqlCode: Sql) { Order = 10 });

    // Add all embedded resources, placed in assembly's "Sql" folder
    // Only *.sql resources are added
    // There is no way to define order for embedded objects
    // Use resources' names if you need to sort objects
    modelBuilder.AddSqlObjects(assembly: typeof(Class1).Assembly, folder: "Sql");
}

Known limitations

• Doesn't drop deleted objects (generates non compilable code, so the developer should write drop-code himself).
• Only C# is supported
• PostgreSQL and SqlServer are supported, but any other DB can be easily added (use EFCore.MigrationExtensions.PostgreSQL as an example)

Testing

1. Open TestDataAccessLayer folder in terminal
2. Set connection string at TestEntryPoint/appsettings.json
3. Install dotnet-ef

• dotnet tool restore

4. Use the following commands to add migrations / update DB

• dotnet dotnet-ef migrations add MyMigr --context TestContext --project TestDataAccessLayer.csproj --startup-project ../TestEntryPoint/TestEntryPoint.csproj
• dotnet dotnet-ef database update --context TestContext --project TestDataAccessLayer.csproj --startup-project ../TestEntryPoint/TestEntryPoint.csproj
• dotnet dotnet-ef migrations script TestMigr Meetup1 --context TestContext --project TestDataAccessLayer.csproj --startup-project ../TestEntryPoint/TestEntryPoint.csproj