How to config Identity for SQLite

[Rick-Anderson]

There are a couple steps to use SQLite with Identity.

@tdykstra can you provide notes?

@danroth27 can you or @divega set the priority.

Should this go in in Custom storage providers for ASP.NET Core Identity

[tdykstra]

I ran into a couple of difficulties, and it turned out that what I thought was an identity-specific issue was more generally about using SQLite with EF. With non-Identity EF I got an error message that told me I needed to create a ContextFactory class. When I ran add-migration on the Identity context I didn't get that helpful error message. Add-migration succeeded but update-database failed on an AddForeignKey operation. I added the ContextFactory class, and migrations and update-database worked flawlessly.

I spent a fair amount of time trying to figure out how to specify the SQLite file location, until I found a great summary of how the connection string works in a comment by @natemcmaster in this issue. If that's in a doc somewhere I didn't run across it. There is a connection string wiki page but it doesn't have this info.

[Rick-Anderson]

@tdykstra the default for dotnet new webapp -au is SQLite. Doesn't that mean SQLite works with Identity?

[tdykstra]

It does work with Identity, but there are (or were when I tried to use it with Identity last January) gotchas that you have to guess and search to find out how to fix. The best place for this information is an introductory tutorial but the only thing I see like that in the TOC is the Learn module https://docs.microsoft.com/en-us/learn/modules/secure-aspnet-core-identity/?view=aspnetcore-3.1

[dagilleland]

Since the main docs point here, can we get a summary here of the steps to switch from SqlServer to Sqlite? I'm going in circles following the links and not finding a good summary. All I read is "I spent a lot of time to get it working" but no summary of how...

[frankhale]

@dagilleland did you figure out how to configure Identity to use SQLite? I am trying to do the same thing and would love to not reinvent the wheel here if it's already known.

[frankhale]

Actually I got this working really easily. I didn't realize that if I deleted the auto generated migration and create a new one it would generate for SQLite. It's working perfectly fine with SQLite and .NET Core 5 RC1.

[ss173b]

Actually I got this working really easily. I didn't realize that if I deleted the auto generated migration and create a new one it would generate for SQLite. It's working perfectly fine with SQLite and .NET Core 5 RC1.

Yes, True.

I just deleted the Migration folder and then run the following command from NuGet Manager Commend

1) Install-Package Microsoft.EntityFrameworkCore.Tools 2) Add-Migration InitialCreate 3) Update-Database

However, you do need to update dotnet to 5.0 (to solve error Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore 5.0.0 is not compatible with netcoreapp3.0 (.NETCoreApp,Version=v3.0). Package Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore 5.0.0 supports: net5.0 (.NETCoreApp,Version=v5.0) )

I hope this helps. Give me a review :-)

[crowcoder]

The process described does not appear to be generating Sqlite syntax

PM> Add-Migration "Initial" -o Persistence/Data/Migrations
Build started...
Build succeeded.
To undo this action, use Remove-Migration.
PM> update-database
Build started...
Build succeeded.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetRoles" (
          "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY,
          "Name" nvarchar(256) NULL,
          "NormalizedName" nvarchar(256) NULL,
          "ConcurrencyStamp" nvarchar(max) NULL
      );
Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "AspNetRoles" (
    "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY,
    "Name" nvarchar(256) NULL,
    "NormalizedName" nvarchar(256) NULL,
    "ConcurrencyStamp" nvarchar(max) NULL
);
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "max": syntax error'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
SQLite Error 1: 'near "max": syntax error'.

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlite(
                    Configuration.GetConnectionString("DefaultConnection")));
            services.AddDefaultIdentity<IdentityUser>(options => options.SignIn.RequireConfirmedAccount = true)
                .AddEntityFrameworkStores<ApplicationDbContext>();
            services.AddRazorPages();
            services.AddServerSideBlazor();
            services.AddScoped<AuthenticationStateProvider, RevalidatingIdentityAuthenticationStateProvider<IdentityUser>>();
            services.AddDatabaseDeveloperPageExceptionFilter();
            services.AddSingleton<WeatherForecastService>();
        }

[luwq168]

The process described does not appear to be generating Sqlite syntax

PM> Add-Migration "Initial" -o Persistence/Data/Migrations
Build started...
Build succeeded.
To undo this action, use Remove-Migration.
PM> update-database
Build started...
Build succeeded.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetRoles" (
          "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY,
          "Name" nvarchar(256) NULL,
          "NormalizedName" nvarchar(256) NULL,
          "ConcurrencyStamp" nvarchar(max) NULL
      );
Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "AspNetRoles" (
    "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY,
    "Name" nvarchar(256) NULL,
    "NormalizedName" nvarchar(256) NULL,
    "ConcurrencyStamp" nvarchar(max) NULL
);
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "max": syntax error'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
SQLite Error 1: 'near "max": syntax error'.

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlite(
                    Configuration.GetConnectionString("DefaultConnection")));
            services.AddDefaultIdentity<IdentityUser>(options => options.SignIn.RequireConfirmedAccount = true)
                .AddEntityFrameworkStores<ApplicationDbContext>();
            services.AddRazorPages();
            services.AddServerSideBlazor();
            services.AddScoped<AuthenticationStateProvider, RevalidatingIdentityAuthenticationStateProvider<IdentityUser>>();
            services.AddDatabaseDeveloperPageExceptionFilter();
            services.AddSingleton<WeatherForecastService>();
        }

Change All [MaxLength(...)] to [StringLength(...)]

[neonleo]

I just try ASP.NET core 6 and EF Core 6.0.7 , it still doesn't work

fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE "AspNetRoles" ( "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY, "Name" nvarchar(256) NULL, "NormalizedName" nvarchar(256) NULL, "ConcurrencyStamp" nvarchar(max) NULL ); Failed executing DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE "AspNetRoles" ( "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY, "Name" nvarchar(256) NULL, "NormalizedName" nvarchar(256) NULL, "ConcurrencyStamp" nvarchar(max) NULL ); Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "max": syntax error'.