How may I replace LocalDB/SQLServer with SQLite and resolve dependency package version mismatching?

[grassit]

I try to build and run the default project created from https://github.com/JasonGT/CleanArchitecture in .NET Core 3.1.100 in Ubuntu, though my questions are not specific to Ubuntu. It uses LocalDB and SQL Server, and I try to replace them with other database systems, e.g. SQLite (it can also be InMemory provided by EF, PostgreSQL, MySQL), by making the following changes:

(1) I found the following file containing "LocalDB": src/WebUI/appsettings.json, so I replace its LocalDB connection string with "DefaultConnection": "Data Source=Example.db" in the file.

(2) I found the following file containing "sqlserver": src/Infrastructure/DependencyInjection.cs, so I replace options.UseSqlServer() with options.UseSqlite() in the file.

public static IServiceCollection AddInfrastructure(this IServiceCollection services, IConfiguration configuration, IWebHostEnvironment environment)
{
  services.AddDbContext<ApplicationDbContext>(options =>
      options.UseSqlite(              // options.UseSqlServer(
          configuration.GetConnectionString("DefaultConnection"), 
              b => b.MigrationsAssembly(typeof(ApplicationDbContext).Assembly.FullName)));

(3) I then add dotnet add package Microsoft.EntityFrameworkCore.Sqlite and dotnet add package Microsoft.EntityFrameworkCore.Design to src/WebUI/WebUI.csproj. There seems to be some version mismatching problem between the dependency packages. I figure that I either need to downgrade the EF Sqlite package version from 3.1.1 to 3.1.0, or upgrade the other packages' versions from 3.1.0 to 3.1.1 except build error telling that Microsoft.VisualStudio.Web.CodeGeneration.Design should remain at 3.1.0

(4) After either downgrade SQlite package or upgrade the other packages, I got build error

src/Infrastructure/DependencyInjection.cs(26,11): error CS1061: 'DbContextOptionsBuilder' does not contain a definition for 'UseSqlite' and no accessible extension method 'UseSqlite' accepting a first argument of type 'DbContextOptionsBuilder' could be found (are you missing a using directive or an assembly reference?)".

Some link says they added Sqlite.Design package, but when I run dotnet add package Entity.Framework.Core.Sqlite.Design I got error:

Package 'Entity.Framework.Core.Sqlite.Design' is incompatible with 'all' frameworks in project".

My questions are:

• How may I replace LocalDB/SQLServer with some other database systems such as SQLite?
• How may I solve dependency version mismatching problem in this case and in general?

Thanks.

[tomfroehle]

I forked the project and made a few changes so it works with Sqlite.

You can have a look at this commit: https://github.com/tomfroehle/CleanArchitecture/commit/6b243ac283160a01c9ac5f437314b75b70176340

One thing to mind: The migrations that are in the project don't work for Sqlite because they are SQL Server specific. You would have to add custom Sqlite Migrations. My quick fix was to remove the Migrate call with a EnsureCreated. This leads to the database being created but it will never be updated.

[jasontaylordev]

Thanks for your sample solution, @tomfroehle.

Perhaps I should use Sqlite or InMemory as the default? - since both are cross-platform.

Thoughts?

[tomfroehle]

Thanks for your sample solution, @tomfroehle.

Perhaps I should use Sqlite or InMemory as the default? - since both are cross-platform.

Thoughts?

Would be nice if it ran out of the box without any infrastructure. The only thing would be, that you need separate migration steps for Sqlite

[jasontaylordev]

Thanks for raising this issue, I migrated to InMemory as the default (in debug mode) and SQL Server for release. Looked at using Sqlite, but preferred InMemory for ease of use.