EF Core SQLite in memory exception: SQLite Error 1: 'near "MAX": syntax error'

Question

I am creating SQLite In Memory database for unit testing:

        var connection = new SqliteConnection("DataSource=:memory:");
        connection.Open();

        try
        {
            var options = new DbContextOptionsBuilder<BloggingContext>()
                .UseSqlite(connection)
                .Options;

            // Create the schema in the database
            using (var context = new BloggingContext(options))
            {
                context.Database.EnsureCreated();
            }

            // Run the test against one instance of the context
            using (var context = new BloggingContext(options))
            {
                var service = new BlogService(context);
                service.Add("http://sample.com");
            }

            // Use a separate instance of the context to verify correct data was saved to database
            using (var context = new BloggingContext(options))
            {
                Assert.AreEqual(1, context.Blogs.Count());
                Assert.AreEqual("http://sample.com", context.Blogs.Single().Url);
            }
        }

context.Database.EnsureCreated(); fails with with exception: Message: Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'near "MAX": syntax error'.

There is github issue saying: The issue here is varchar(max) is SqlServer specific type. The scaffolding should not add it as relational type which gets passed to migration in other providers which is prone to generate invalid sql at migration.

But how then can I use SQLite in Memory for unit tests if my database contains many varchar(max) columns?

Escarp

If you're using EF Core, why not use the In Memory provider instead of SQLite? – Maudmaude

@Maudmaude I am using "In Memory SQLite Database" link because I need to test transactions. – Escarp

If you use 'Migrate' instead of 'EnsureCreated'? – Zenger

@Zenger I tried this but then I am getting another exception. When I call _offerContext.Set<Aggregate>().FindAsync(id); I am getting exception: typeof(Microsoft.Data.Sqlite.SqliteException): SQLite Error 1: 'no such table: Offers'. ---- Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such table: Offers'. instead of null. If am passing id with existing entity it return correct object. But I want to test behaviour when I don't find an entity. – Escarp

Answer 1

In the project properties window, go to the "Build" tab of the project where you have the configuration for your table. Where you have this :

 builder.Property<string>(nameof(BigTextColumn))
               .HasColumnType("nvarchar(max)");

In the "Conditional compilation symbols" field, add the symbol SQLITE and save.

In the table configuration file, put this instead.

#if SQLITE
            builder.Property<string>(nameof(BigTextColumn))
               .HasColumnType("text");
#else
            builder.Property<string>(nameof(BigTextColumn))
               .HasColumnType("nvarchar(max)");
#endif

This should solve the type incompatibility.

Champagne

Answer 2

My workaround: define a flag in the AppDbContext as true (in my case, meaning this is for my SqlServer). Whenever the AppDbContext gets initialized from the Test project, set the flag to false (since for tests we use SqlLite).

Finally, in the OnModelCreating for those entities with nvarchar(max) check the flag and if false (meaning I am running the tests) set the ColumnTypes for those nvarchar(max) properties to Text.

In AppDbContext:

public static bool IsSqlServer = true;

In AppDbContext.OnModelCreating for the entities with those nvarchar properties do the following to change the type from nvarchar(max) to text:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<YourType>( entity => 
   {
      if (!IsSqlServer)
         entity.Property(YourPropertyName).HasColumnType("text");
   }
}

Finally, in your test project initialize, set the flag to false to change the types so it doesn't fail.

AppDbContext.IsSqlServer = false;

Callipash

Answer 3

As can be found in my other answer for Oracle databases https://mcmap.net/q/821417/-how-to-customize-migration-generation-in-ef-core-code-first, if you're using migrations, you can create a method to fix your migration operations for Sqlite.

using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using System.Linq;

public static class MigrationBuilderExtensions
{
    public static void ConfigForSqlite(this MigrationBuilder migrationBuilder)
    {
        //For each table registered in the builder, let's change the type of nvarchar to TEXT
        foreach (CreateTableOperation createTableOperation in migrationBuilder.Operations.ToArray().OfType<CreateTableOperation>())
        {
            foreach (var column in createTableOperation.Columns.Where(x => x.ColumnType.StartsWith("nvarchar", StringComparison.OrdinalIgnoreCase)))
            {
                if (column.ColumnType.Contains('(') && !column.ColumnType.Contains("MAX", StringComparison.OrdinalIgnoreCase))
                    column.MaxLength = int.Parse(column.ColumnType.Substring("nvarchar".Length + 1).Replace(")", ""));
                column.ColumnType = "TEXT";
           }
        }
    }
}

Just call the extension method at the end of the Migration.Up() method. This way you won't need to recreate migrations for every database provider and can create a multi-database-provider design with proper fixes and conditions. Or...
If you're not using migrations however, you can use the same approach to fix your database model at runtime.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //Entity Configurations...
    //Continue if db provider is Sqlite
    
    foreach (var entity in modelBuilder.Model.GetEntityTypes())
    {
        foreach (var property in entity.GetProperties().Where(p => p.GetColumnType().StartsWith("nvarchar", StringComparison.OrdinalIgnoreCase)))
        {
            var columnType = property.GetColumnType();
            if (columnType.Contains('(') && !columnType.Contains("MAX", StringComparison.OrdinalIgnoreCase))
                property.SetMaxLength(int.Parse(columnType.Substring("nvarchar".Length + 1).Replace(")", "")));
            property.SetColumnType("TEXT");
        }
    }
}

Bunnell

I wish this worked, but EF Core doesn't support retrieving the model metadata until the model has been fully built anymore. See the following issue for details: github.com/dotnet/efcore/issues/8034 – Cyanamide

@AsbjørnUlsberg I couldn't figure out the point in the issue. There would be a call to modelBuilder.ApplyConfiguration() or modelBuilder.ApplyConfigurationsFromAssembly() in the OnModelCreating method, which may be called before the manipulation and set the relations and settings for our model, so what's the problem here? I've done this approach many times recently using older versions of EF Core and 7.0.2 with no issues. – Bunnell

The issue may have been that I didn't know about the need to invoke modelBuilder.ApplyConfiguration() before retrieving the model metadata. I can't quite remember, but I think I figured out a different solution to this problem in the end and won't revisit this anytime soon. – Cyanamide

@AsbjørnUlsberg Glad to know that. Be sure to let us know how you managed to make it work, there is no limit for knowing EF Core workarounds these days. – Bunnell

Answer 4

Instead of working around the EF bug (which should get fixed in a new NuGet package later hopefully) you could also just manually create the schema for now.

I think this is the fastest and least intrusive workaround, and you will not have to search and remove the old workaround code when the actual bug finally gets fixed.

This is an example for the ASP.Net authentication tables, but you can export a script with DB Browser for your specific schema...

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "AspNetRoles" (
    "Id"    nvarchar(450) NOT NULL,
    "Name"  nvarchar(256),
    "NormalizedName"    nvarchar(256),
    "ConcurrencyStamp"  nvarchar,
    CONSTRAINT "PK_AspNetRoles" PRIMARY KEY("Id")
);
CREATE TABLE IF NOT EXISTS "AspNetUsers" (
    "Id"    nvarchar(450) NOT NULL,
    "UserName"  nvarchar(256),
    "NormalizedUserName"    nvarchar(256),
    "Email" nvarchar(256),
    "NormalizedEmail"   nvarchar(256),
    "EmailConfirmed"    bit NOT NULL,
    "PasswordHash"  nvarchar,
    "SecurityStamp" nvarchar,
    "ConcurrencyStamp"  nvarchar,
    "PhoneNumber"   nvarchar,
    "PhoneNumberConfirmed"  bit NOT NULL,
    "TwoFactorEnabled"  bit NOT NULL,
    "LockoutEnd"    datetimeoffset,
    "LockoutEnabled"    bit NOT NULL,
    "AccessFailedCount" int NOT NULL,
    CONSTRAINT "PK_AspNetUsers" PRIMARY KEY("Id")
);
CREATE TABLE IF NOT EXISTS "AspNetRoleClaims" (
    "Id"    int NOT NULL,
    "RoleId"    nvarchar(450) NOT NULL,
    "ClaimType" nvarchar,
    "ClaimValue"    nvarchar,
    CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY("Id"),
    CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY("RoleId") REFERENCES "AspNetRoles"("Id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "AspNetUserClaims" (
    "Id"    int NOT NULL,
    "UserId"    nvarchar(450) NOT NULL,
    "ClaimType" nvarchar,
    "ClaimValue"    nvarchar,
    CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY("Id"),
    CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY("UserId") REFERENCES "AspNetUsers"("Id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "AspNetUserLogins" (
    "LoginProvider" nvarchar(128) NOT NULL,
    "ProviderKey"   nvarchar(128) NOT NULL,
    "ProviderDisplayName"   nvarchar,
    "UserId"    nvarchar(450) NOT NULL,
    CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY("LoginProvider","ProviderKey"),
    CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY("UserId") REFERENCES "AspNetUsers"("Id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "AspNetUserTokens" (
    "UserId"    nvarchar(450) NOT NULL,
    "LoginProvider" nvarchar(128) NOT NULL,
    "Name"  nvarchar(128) NOT NULL,
    "Value" nvarchar,
    CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY("UserId","LoginProvider","Name"),
    CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY("UserId") REFERENCES "AspNetUsers"("Id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "AspNetUserRoles" (
    "UserId"    nvarchar(450) NOT NULL,
    "RoleId"    nvarchar(450) NOT NULL,
    CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY("UserId","RoleId"),
    CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY("RoleId") REFERENCES "AspNetRoles"("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY("UserId") REFERENCES "AspNetUsers"("Id") ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" (
    "RoleId"
);
CREATE UNIQUE INDEX IF NOT EXISTS "RoleNameIndex" ON "AspNetRoles" (
    "NormalizedName"
) WHERE "NormalizedName" IS NOT NULL;
CREATE INDEX IF NOT EXISTS "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" (
    "UserId"
);
CREATE INDEX IF NOT EXISTS "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" (
    "UserId"
);
CREATE INDEX IF NOT EXISTS "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" (
    "RoleId"
);
CREATE INDEX IF NOT EXISTS "EmailIndex" ON "AspNetUsers" (
    "NormalizedEmail"
);
CREATE UNIQUE INDEX IF NOT EXISTS "UserNameIndex" ON "AspNetUsers" (
    "NormalizedUserName"
) WHERE "NormalizedUserName" IS NOT NULL;
COMMIT;

Hemicellulose

Answer 5

I didn't find a direct solution but have started using a Configuration files workaround. You didn't indicate if you're using EF Configurations so excuse the basics if not needed.

In your DbContext place the following:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfigurationsFromAssembly(typeof(ChildrensSsiContext).Assembly);
}

Now create a static class like below. It's going to take your property and handle the configuration.

internal static class ConfigurationHelper
{
    internal static void ConfigureVarcharMax(PropertyBuilder<string> propertyBuilder, bool isRequired = true)
    {
        propertyBuilder
            .IsRequired(isRequired)
            //.HasColumnType("varchar(max)");
            .HasColumnType("text");
    }
}

Create a Configuration class for each entity that you want to configure

public class MyEntityWithVarcharMaxConfiguration
    : IEntityTypeConfiguration<MyEntityWithVarcharMax>
{
    public void Configure(EntityTypeBuilder<MyEntityWithVarcharMax> builder)
    {
        ConfigurationHelper.ConfigureVarcharMax(builder.Property(e => e.MyVarcharMaxProperty));
    }
}

Leave the HasColumnType("text") uncommented for testing. Then comment that line and uncomment the HasColumnType("varchar(max)") when you add the migration.

It's a pain that you need to remember to do this but it's a fairly simple workaround.

Ceceliacecil