Integration test sqlite in memory database give error in .NET Core

Question

I setup integration testing in my .NET Core application but in line number 53 (i.e appContext.Database.EnsureCreated()) I get an error:

$exception {"SQLite Error 1: 'near "''": syntax error'."}
Microsoft.Data.Sqlite.SqliteException

Can anyone please tell me what is wrong?

using Asp.Versioning;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Options;
using System.Data.Common;
using System.Xml.Linq;
using World.Persistence.DBContext;


namespace World.Integration.Tests
{
    public abstract class CustomWebApplicationFactory<TProgram>
    : WebApplicationFactory<TProgram> where TProgram : class
    {
        protected override void ConfigureWebHost(IWebHostBuilder builder)
        {
            builder.ConfigureServices(services =>
            {
                var dbContextDescriptor = services.SingleOrDefault(
                    d => d.ServiceType ==
                        typeof(DbContextOptions<WorldDBContext>));

                services.Remove(dbContextDescriptor!);

                var dbConnectionDescriptor = services.SingleOrDefault(
                    d => d.ServiceType ==
                        typeof(DbConnection));

                _ = services.Remove(dbConnectionDescriptor!);

                // Create open SqliteConnection so EF won't automatically close it.
                services.AddSingleton<DbConnection>(container =>
                {
                    var connection = new SqliteConnection("DataSource=:memory:");
                    connection.Open();

                    return connection;
                });

                services.AddDbContext<WorldDBContext>((container, options) =>
                {
                    var connection = container.GetRequiredService<DbConnection>();
                    options.UseSqlite(connection);
                });

                var sp = services.BuildServiceProvider();

                using var scope = sp.CreateScope();
                using var appContext = scope.ServiceProvider.GetRequiredService<WorldDBContext>();

                try
                {
                    appContext.Database.EnsureCreated();
                }
                catch (Exception)
                {
                    throw;
                }
            });

            builder.UseEnvironment("Development");
        }
    }
}

Answer 1

I reproduced your issue with your codes,when EnsureCreated() method is called,it would create db and tables accroding the configuration in your case,and the error was caused by invalid Sql in your .HasDefaultValueSql() method in WorldDBContext.