SQLite Error 19: 'NOT NULL constraint failed: Test.Id #4802

I have tried to create PK to a different column other than the default Id column with the help of [this](<a class="postlink" href="https://stackoverflow.com/questions/46153399/how-to-use-a-varchar-column-other-than-id-for-pk/48604164#48604164">https://stackoverflow.com/questions/461 ... 4#48604164</a>) solution. Its working fine for every scenario, but my test cases are failing. I have created TestId as PK,Id as autoincrement identity.

It's failing when I'm running test case to create the record and giving the following exception.

SQLite Error 19: 'NOT NULL constraint failed: Test.Id'.

Testcase:

public async Task Should_Create_Test_With_Valid_Arguments()
{
    var Test = await CreateNewTest(K_TESTCode1);
    Test.Code = await _TestAppService.CreateTest(Test);

    UsingDbContext((System.Action<EntityFrameworkCore.MyProjectDbContext>)(context =>
    {
        context.Test.FirstOrDefault(
            u => u.Code == Test.Code
        ).ShouldNotBeNull();
    }));
}

CreateNewTest Method

public async Task<TestDetailsDto> CreateNewTest(string Code)
{
    return CreateTestEntity(Code);
}

public TestDetailsDto CreateTestEntity(string Code)
{
    var Test = new TestDetailsDto
    {
        Code = Code,
    };
    return Test;
}

CreateTest Method

public async Task<string> CreateTest(TestDetailsDto input)
{
    try
    {
        int TestId = await InsertAndGetIdAsync(ObjectMapper.Map<Test>(input));
        return input.Code;
    }
    catch (Exception ex)
    {
        throw new UserFriendlyException(ex.Message);
    }
}

StackTrace

Starting: MyCompany.MyProject.Tests [3/6/2018 5:49:05 AM Error] [xUnit.net 00:00:26.6288060] MyCompany.MyProject.Tests.Classifications.TestAppService_Test.Should_Create_Classification_With_Valid_Arguments [FAIL] [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6308959] Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while updating the entries. See the inner exception for details. [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6309648] ---- Microsoft.Data.Sqlite.SqliteException : SQLite Error 19: 'NOT NULL constraint failed: Test.Id'. [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6323228] Stack Trace: [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6335736] at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__32.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6336454] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6336945] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6337390] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6337813] at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__10.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6338186] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6338566] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6338982] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6339344] at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6339755] at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__61.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6340070] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6340441] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6340850] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6341236] at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6341641] at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__59.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6341947] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6342311] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6342694] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6343065] at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6343431] at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__48.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6343752] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6344115] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6344511] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6345061] D:\Github\aspnetboilerplate\src\Abp.EntityFrameworkCore\EntityFrameworkCore\AbpDbContext.cs(215,0): at Abp.EntityFrameworkCore.AbpDbContext.<SaveChangesAsync>d__49.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6348796] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6349402] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6349858] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6350373] D:\Github\aspnetboilerplate\src\Abp.EntityFrameworkCore\EntityFrameworkCore\Uow\EfCoreUnitOfWork.cs(163,0): at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.<SaveChangesInDbContextAsync>d__20.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6350804] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6351201] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6351665] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6352134] D:\Github\aspnetboilerplate\src\Abp.EntityFrameworkCore\EntityFrameworkCore\Uow\EfCoreUnitOfWork.cs(68,0): at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.<SaveChangesAsync>d__12.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6352535] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6352935] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6353330] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6353808] D:\Github\aspnetboilerplate\src\Abp.EntityFrameworkCore\EntityFrameworkCore\Uow\EfCoreUnitOfWork.cs(83,0): at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.<CompleteUowAsync>d__14.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6354207] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6354685] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6355115] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6355558] D:\Github\aspnetboilerplate\src\Abp\Domain\Uow\UnitOfWorkBase.cs(276,0): at Abp.Domain.Uow.UnitOfWorkBase.<CompleteAsync>d__57.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6355921] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6356306] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6356695] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6357135] D:\Github\aspnetboilerplate\src\Abp\Domain\Uow\UnitOfWorkInterceptor.cs(90,0): at Abp.Domain.Uow.UnitOfWorkInterceptor.<>c__DisplayClass6_0.<<PerformAsyncUow>b__0>d.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6357587] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6359838] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6360322] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6360807] D:\Github\aspnetboilerplate\src\Abp\Threading\InternalAsyncHelper.cs(40,0): at Abp.Threading.InternalAsyncHelper.<AwaitTaskWithPostActionAndFinally>d__1.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6361199] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6361583] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6362001] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6362425] D:\Github\aspnetboilerplate\src\Abp\Threading\InternalAsyncHelper.cs(20,0): at Abp.Threading.InternalAsyncHelper.<AwaitTaskWithFinally>d__0.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6362869] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6363327] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6363782] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6364332] C:\Users\pooja.dhadse\Source\MyProject\aspnet-core\test\MyCompany.MyProject.Tests\Classifications\TestAppService_Test.cs(20,0): at MyCompany.MyProject.Tests.Classifications.TestAppService_Test.<Should_Create_Classification_With_Valid_Arguments>d__1.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6364752] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6365185] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6365609] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6365946] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6366305] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6366710] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6367020] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6367403] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6367790] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6368122] ----- Inner Stack Trace ----- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6368492] at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6368883] at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6369347] at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6369741] at Microsoft.Data.Sqlite.SqliteCommand.<ExecuteDbDataReaderAsync>d__52.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6370066] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6370420] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6370829] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6371183] at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6371579] at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6371886] --- End of stack trace from previous location where exception was thrown --- [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6372250] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6372632] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6372995] at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() [3/6/2018 5:49:06 AM Informational] [xUnit.net 00:00:26.6373387] at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.

Code:

public class Test: FullAuditedEntity
{
    // PK
    [MaxLength(NVarcharLength14), DataType(DataType.Text)]
    public virtual string Code { get; set; }

    // Unique constraint
    public int MyUniqueId { get; set; }
}

public class AbpProjectNameDbContext : AbpZeroDbContext<Tenant, Role, User, AbpProjectNameDbContext>
{
    /* Define a DbSet for each entity of the application */    
    public DbSet<Test> Tests { get; set; }

    public AbpProjectNameDbContext(DbContextOptions<AbpProjectNameDbContext> options) : base(options) {}

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Test>().Property(t => t.Id).ValueGeneratedOnAdd(); // Auto-increment
        modelBuilder.Entity<Test>().HasAlternateKey(t => t.Id);                // Auto-increment, closed-wont-fix: https://github.com/aspnet/EntityFrameworkCore/issues/7380
        modelBuilder.Entity<Test>().HasKey(t => t.Code);                       // PK
        modelBuilder.Entity<Test>().HasIndex(t => t.MyUniqueId).IsUnique();    // Unique constraint
    }
}

Generated migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "Tests",
        columns: table => new
        {
            Code = table.Column<string>(nullable: false),
            Id = table.Column<int>(nullable: false)
                .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
            MyUniqueId = table.Column<int>(nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Tests", x => x.Code);
        });

    migrationBuilder.CreateIndex(
        name: "IX_Tests_MyUniqueId",
        table: "Tests",
        column: "MyUniqueId",
        unique: true);
}

Usage:

public async Task MyMethod()
{
    await _repository.InsertAndGetIdAsync(new Test
    {
        Code = "One",
        MyUniqueId = 1
    });

    // Valid
    await _repository.InsertAndGetIdAsync(new Test
    {
        Code = "Two",
        MyUniqueId = 2
    });

    try
    {
        await _repository.InsertAndGetIdAsync(new Test
        {
            Code = "One", // PK conflict
            MyUniqueId = 3
        });
    }
    catch (Exception e)
    {
    }

    try
    {
        await _repository.InsertAndGetIdAsync(new Test
        {
            Code = "Three",
            MyUniqueId = 1 // Unique constraint conflict
        });
    }
    catch (Exception e)
    {
        throw;
    }

    return null;
}