SQLite Error 1: near 'max' syntax error

[The-MAZZTer]

Steps to reproduce

Create a model that results in a varchar(max) type in the migration/model snapshot classes. Migrate an empty/non-existent database file and this error is thrown.

The issue

SQLite does not support the max keyword. EF Core needs to hard-code the maximum value when passing it to SQLite (8000 is the default, I think SQLite can be compiled with an arbitrary value).

Further technical details

EF Core version: 1.0.1
Operating system: Windows 7 SP1
Visual Studio version: VS2015 Web Express Update 3

Changing varchar(max) in the model snapshot and migration classes to varchar(8000) results in the error no longer occurring.

[smitpatel]

Create a model that results in a varchar(max) type in the migration/model snapshot classes

Can you elaborate your model configuration? By default you would not be getting varchar(max) as store type.

[The-MAZZTer]

Oops, that's rather important. I imported a SQL Server database to serve as my model using the steps at https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db, specifically the Scaffold-DbContext command.

There was indeed a varchar(max) as a column in one of the tables.

[smitpatel]

That is actually interesting. Can you share the code for the table with varchar(max) generated by Scaffold-DbContext - the generated entity type & its model configuration from OnModelConfiguring method in dbcontext?

[The-MAZZTer]

I left work so I can't right now, but I will try and make a minimal reproduction case project tomorrow.

[smitpatel]

@The-MAZZTer - Thanks. I tried repro myself and hit the issue.

Following is the configuration code we generated

modelBuilder.Entity<Blog>(entity =>
{
    entity.Property(e => e.Id).ValueGeneratedNever();

    entity.Property(e => e.Property).HasColumnType("varchar(max)");
});

Generated migration for SQLite (which contains sql server specific type)

migrationBuilder.CreateTable(
    name: "Blog",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false),
        Property = table.Column<string>(type: "varchar(max)", nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Blog", x => x.Id);
    });

And Migration Sql

CREATE TABLE "Blog" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Blog" PRIMARY KEY,
    "Property" varchar(max)
);

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.

[ajcvickers]

@smitpatel This is by design. Reverse engineering favors the common case and simple code, so we don't do SQL Server specific calls but rather the cleaner relational calls. It is expected that if you reverse engineer from one database provider and then try to use the model with another that you might have to make some edits.

Note for triage: I think this could be considered a dupe of #5410. We now support IsUnicode and hence for SQL Server a varchar(max) column should scaffold as:

entity.Property(e => e.Property).IsUnicode(false);

which is database agnostic.

[deivydas321]

@ajcvickers this is created as duplicate but there is still a problem with varchar(max). Now it is not possible to use in memory SQLite database for testing because of this bug. Is there any progress with this?

[ajcvickers]

@deivyd321 varchar(max) is meaningless to SQLite since it has its own unique type system. For testing, you can only use one database as a substitute for the other when they have common functionality for what is being tested. This is not the case for SQL Server and SQLite when it comes to the handling of types. See https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/

[deivydas321]

@ajcvickers thanks for quick response. But if I am using MS SQL Server database is there any option to use "In Memory SQL Server Database" to test for example transactions logic?

[ajcvickers]

@deivyd321 I don't believe SQL Server has an in-memory mode.

[xyfy]

I got the err and fixed ,you will not
Explicit declaration [Column(TypeName = "nvarchar(max)")] in the entity property ,which is default value of string columns in ef core,
when I remove it from property , then unit test works fine.

[jingliancui]

@ajcvickers Maybe the best solution work for integration test with docker and jenkins is setup a separate db which should be same to the production db, right?

[roji]

@jingliancui Yes - our general recommendation is to test on your production whenever possible, as that ensures 100% accurate testing and removes surprises when deploying to production that stem from differences between databases. This doc page provides more info.

[gmagana]

Just for people stumbling into this link, I have a fix that worked for me: Set a maximum data length or set the data type of the column to "text". Use one of these two annotations in the model (or use equivalent fluent API):

[Column(TypeName = "text")]

or

[MaxLength(100)] (Whatever value is appropriate)

[mhamri]

for those who land here, just make sure to create different migrations for the different sqlprovider, the type of columsn and everything is inside the migration that you are applying

[Steedalion]

When you use inheritance within the same table a Discriminator is auto-generated like this "Discriminator" nvarchar(max) NOT NULL,. I'm not sure I can specify the Discriminator Length.

Are there no options in DbContextOptionsBuilder that can be used to disable nvchar(max)?

[danielscatigno-ncpc]

I Tried creating a Migration specifying a provider, but the results are the same:
dotnet ef migrations add MyMigration --project ../SqliteMigrations -- --provider Sqlite

https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/providers?tabs=dotnet-core-cli

[foggerty]

This is a work-around that I came up with for the problem of testing on SqLite for a setup that's geared towards SqServer:

public static void FixForSqLite(this DbContext ctx)
{
    // Entities share the same instance of a given mapping, so change one and change them all.

    var entities = ctx.Model.GetEntityTypes();

    var mapping = entities
        .SelectMany(x => x.GetProperties())
        .Where(x => x.GetTypeMapping() is StringTypeMapping)
        .Select(x => x.GetTypeMapping() as StringTypeMapping)
        .Where(x => x.StoreType == "nvarchar(max)")
        .FirstOrDefault();

    if (mapping is null)
        return;

    var storeTypeField = typeof(RelationalTypeMapping)
        .GetRuntimeFields()
        .Where(x => x.Name == "<StoreType>k__BackingField")
        .FirstOrDefault();

    storeTypeField.SetValue(mapping, "text");
}

Yes this is a rather horrifying hack, I make no apologies! Run this on your context before calling EnsureCreated(). Note that you may run into other problems like [DatabaseGenerated()] not being supported; hooray for trying to support large legacy projects with newer tools....

[JPasterkampRotec]

@foggerty I tried your work-around in 8.0.4, but the mapping variable is always null for me. On furher inspecting the StoreType is already "TEXT" in all uppercase.

Still getting the 'SQLite Error 1: 'near "max": syntax error'.
Any advice?

[foggerty]

@JPasterkampRotec none, sorry :(

[mykauskas]

My app was calling MigrateAsync and getting the error, changed that to call EnsureCreatedAsync only when doing integration testing with SQLite, and it fixed it.