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

• Jul 25, 2024
• 4 minutes to read

The XAF Wizard creates applications that use the 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 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": "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();
               })
   

SQLite and Other File-Based Databases

We do not recommend using SQLite with XAF in production scenarios with large amounts of data. SQLite is a file-based database that, due to its design, is inferior in performance and other features to powerful distributed server-based RDBMSs such as SQL Server, Oracle, MySQL, or PostgreSQL (used by the majority of our customers). If you experience performance issues with SQLite and other file-based databases (even with our Server Mode data sources), we cannot offer any suitable solutions other than switching to another RDBMS.

We can only recommend that our customers use SQLite for demo/testing purposes or simple applications that do not have many records. NOTE: as a developer or tester, you can also use the databases that can be installed with Visual Studio: Microsoft SQL Server Express or LocalDB databases (a free edition of SQL Server, ideal for development and production for desktop, web, and small server applications).

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.