Switch EF Core Connection from SQL Server to a Different Database Provider
- 3 minutes to read
The XAF Wizard creates applications that use Microsoft SQL Server. To use a different database provider, modify your XAF solution as follows.
PostgreSQL
Install the Npgsql.EntityFrameworkCore.PostgreSQL package. Note its Microsoft.EntityFrameworkCore package reference. That Entity Framework’s version must match the version that your solution uses (currently 6.0.x).
Change your application’s connection string.
- Blazor (MySolution\MySolution.Blazor.Server\appsettings.json)
- WinForms (MySolution\MySolution.Win\App.config)
"ConnectionStrings": { "ConnectionString": "Host=localhost;Database=my_db;Username=postgres;Password=qwerty",
If you use migrations, change the
MySolutionDesignTimeDbContextFactory.CreateDbContext
method to use PostgreSQL and the updated connection string.MySolution\MySolution.Module\BusinessObjects\MySolutionDbContext.cs:
public class Postgre1DesignTimeDbContextFactory : IDesignTimeDbContextFactory<Postgre1EFCoreDbContext> { public Postgre1EFCoreDbContext CreateDbContext(string[] args) { //throw new InvalidOperationException("Make sure that the database connection string and connection provider are correct. After that, uncomment the code below and remove this exception."); var optionsBuilder = new DbContextOptionsBuilder<Postgre1EFCoreDbContext>(); //optionsBuilder.UseSqlServer("Integrated Security=SSPI;Pooling=false;Data Source=(localdb)\\mssqllocaldb;Initial Catalog=MyDBName"); optionsBuilder.UseNpgsql("Host=localhost;Database=my_db2;Username=postgres;Password=qwerty"); optionsBuilder.UseChangeTrackingProxies(); optionsBuilder.UseObjectSpaceLinkProxies(); return new Postgre1EFCoreDbContext(optionsBuilder.Options); }
Change the
DbContextTypesInfoInitializerBase
descendant to use PostgreSQL.MySolution\MySolution.Module\BusinessObjects\MySolutionDbContext.cs:
public class Postgre1ContextInitializer : DbContextTypesInfoInitializerBase { protected override DbContext CreateDbContext() { var optionsBuilder = new DbContextOptionsBuilder<Postgre1EFCoreDbContext>() //.UseSqlServer(";") .UseNpgsql(";") .UseChangeTrackingProxies() .UseObjectSpaceLinkProxies(); return new Postgre1EFCoreDbContext(optionsBuilder.Options); } }
Change the following files to use PostgreSQL:
public class Startup { public void ConfigureServices(IServiceCollection services) { //... builder.ObjectSpaceProviders .AddSecuredEFCore().WithDbContext<Postgre1.Module.BusinessObjects.Postgre1EFCoreDbContext>((serviceProvider, options) => { // ... // options.UseSqlServer(connectionString); options.UseNpgsql(connectionString); options.UseChangeTrackingProxies(); options.UseObjectSpaceLinkProxies(); })
MySQL
Install the NuGet Gallery | MySql.EntityFrameworkCore package. Note its Microsoft.EntityFrameworkCore package reference. The Entity Framework’s version much match the version that your solution uses (currently 6.0.x).
Change your application’s connection string.
- Blazor (MySolution\MySolution.Blazor.Server\appsettings.json)
- WinForms (MySolution\MySolution.Win\App.config)
"ConnectionStrings": { "ConnectionString": "server=localhost; database=MySQLSolution; user=root; password=qwerty",
If you use migrations, change the
MySolutionDesignTimeDbContextFactory.CreateDbContext
method to use MySQL and the updated connection stringMySolution\MySolution.Module\BusinessObjects\MySolutionDbContext.cs:
public class MySQLSolutionDesignTimeDbContextFactory : IDesignTimeDbContextFactory<MySQLSolutionEFCoreDbContext> { public MySQLSolutionEFCoreDbContext CreateDbContext(string[] args) { var optionsBuilder = new DbContextOptionsBuilder<MySQLSolutionEFCoreDbContext>(); //optionsBuilder.UseSqlServer("Integrated Security=SSPI;Pooling=false;Data Source=(localdb)\\mssqllocaldb;Initial Catalog=MyDb1"); optionsBuilder.UseMySQL("server=localhost; database=MySQLSolution; user=root; password=qwerty"); optionsBuilder.UseChangeTrackingProxies(); optionsBuilder.UseObjectSpaceLinkProxies(); return new MySQLSolutionEFCoreDbContext(optionsBuilder.Options); } }
Change the
DbContextTypesInfoInitializerBase
descendant to use MySQL.MySolution\MySolution.Module\BusinessObjects\MySolutionDbContext.cs:
public class MySQLSolutionContextInitializer : DbContextTypesInfoInitializerBase { protected override DbContext CreateDbContext() { var optionsBuilder = new DbContextOptionsBuilder<MySQLSolutionEFCoreDbContext>() //.UseSqlServer(";") .UseMySQL(";") .UseChangeTrackingProxies() .UseObjectSpaceLinkProxies(); return new MySQLSolutionEFCoreDbContext(optionsBuilder.Options); } }
Change the following files to use MySQL:
public class Startup { public void ConfigureServices(IServiceCollection services) { //... builder.ObjectSpaceProviders .AddSecuredEFCore().WithDbContext<Postgre1.Module.BusinessObjects.Postgre1EFCoreDbContext>((serviceProvider, options) => { // ... // options.UseSqlServer(connectionString); options.UseMySQL(connectionString); options.UseChangeTrackingProxies(); options.UseObjectSpaceLinkProxies(); })
For XPO-based applications, refer to the following topic to learn how to connect these applications to different database providers: Database Systems Supported by XPO.