Efcore: 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

``` c#
modelBuilder.Entity(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)

``` c#
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:

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

which is database agnostic.

[deivyd321]

@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/

[deivyd321]

@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.