Switch EF Core Connection from SQL Server to a Different Database Provider

• Mar 27, 2023
• 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

1. 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).

2. Change your application’s connection string.

   Blazor (MySolution\MySolution.Blazor.Server\appsettings.json)

   "ConnectionStrings": {
       "ConnectionString": "Host=localhost;Database=my_db;Username=postgres;Password=qwerty",
   

   WinForms (MySolution\MySolution.Win\App.config)

       <add name="ConnectionString" connectionString="Host=localhost;Database=my_db;Username=postgres;Password=qwerty" />
   

3. 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);
       }
   

4. 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);
       }
   }
   

5. Change the following files to use PostgreSQL:

   Blazor (MySolution.Blazor.Server\Startup.cs)

   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();
               })
   

   WinForms (MySolution.Win\Startup.cs)

   public class ApplicationBuilder : IDesignTimeApplicationFactory {
       public static WinApplication BuildApplication(string connectionString) {
           //...
           builder.ObjectSpaceProviders
               .AddSecuredEFCore().WithDbContext<Postgre1.Module.BusinessObjects.Postgre1EFCoreDbContext>((application, options) => {
                   // ...
                   // options.UseSqlServer(connectionString);
                   options.UseNpgsql(connectionString);
                   options.UseChangeTrackingProxies();
                   options.UseObjectSpaceLinkProxies();
               })
   

MySQL

1. 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).

2. Change your application’s connection string.

   Blazor (MySolution\MySolution.Blazor.Server\appsettings.json)

   "ConnectionStrings": {
       "ConnectionString": "server=localhost; database=MySQLSolution; user=root; password=qwerty",
   

   WinForms (MySolution\MySolution.Win\App.config)

       <add name="ConnectionString" connectionString="server=localhost; database=MySQLSolution; user=root; password=qwerty" />
   

3. If you use migrations, change the MySolutionDesignTimeDbContextFactory.CreateDbContext method to use MySQL and the updated connection string

   MySolution\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);
       }
   }
   

4. 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);
       }
   }
   

5. Change the following files to use MySQL:

   Blazor (MySolution.Blazor.Server\Startup.cs)

   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();
               })
   

   WinForms (MySolution.Win\Startup.cs)

   public class ApplicationBuilder : IDesignTimeApplicationFactory {
       public static WinApplication BuildApplication(string connectionString) {
           //...
           builder.ObjectSpaceProviders
               .AddSecuredEFCore().WithDbContext<Postgre1.Module.BusinessObjects.Postgre1EFCoreDbContext>((application, 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.