SQLite Error 19: 'UNIQUE constraint failed' when using same DbConnection different DbContext instances

[wtorricos]

Sqlite throws Microsoft.Data.Sqlite.SqliteException : SQLite Error 19: 'UNIQUE constraint failed:
when you use the same connection in different instances of DbContext and you have a relationship of 0 to 1.

Here is a sample project with 3 unit tests where the problem can be reproduced:
https://github.com/tico321/SQLiteUniqueErrorSeparateUsings/blob/master/SQLiteUniqueError/UnitTest1.cs

Also note that the problem only happens when there are already stored entities in this case BEntities.

We can see that if we run the test using the same DbContext it will pass:

        [Fact]
        public void SuccessRunningInSameUsing()
        {
            var connection = new SqliteConnection("DataSource=:memory:");
            connection.Open();

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

                using (var context = new MyContext(options))
                {
                    context.Database.EnsureCreated();
                    //init data
                    context.BEntities.Add(new EntityB { EntityBId = 1 });
                    context.SaveChanges();

                    context.Add(new EntityA { EntityAId = 1, EntityB = new EntityB { EntityBId = 1 } });
                    context.SaveChanges();
                }
            }
            finally
            {
                connection.Close();
            }
        }

Also weird but a workaround is that if we load the BEntities in the second using the test will pass:

       [Fact]
       public void SuccessLoadingBEntities()
       {
           var connection = new SqliteConnection("DataSource=:memory:");
           connection.Open();

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

               using (var context = new MyContext(options))
               {
                   context.Database.EnsureCreated();
                   //init data
                   context.BEntities.Add(new EntityB { EntityBId = 1 });
                   context.SaveChanges();
               }

               using (var context = new MyContext(options))
               {
                   context.BEntities.ToList();//workaround to avoid Unique error

                   context.Add(new EntityA { EntityAId = 1, EntityB = new EntityB { EntityBId = 1 } });
                   context.SaveChanges();
               }
           }
           finally
           {
               connection.Close();
           }
       }

The issue is that the test will fail without the workaround where we list BEntities and will throw the Unique Constraint failed error.

Further technical details

EF Core version: 2.2.
Microsoft.EntityFrameworkCore.Sqlite: 2.2.4
Operating system: Windows 10
IDE: Visual Studio 2019

[ajcvickers]

@tico321 Given that the code above creates two EntityB instances with the same ID, I would expect either EF or the database to throw here indicating that two entities with the same primary key cannot be inserted. Can you provide some more details as to why you don't expect this?

Note for triage: full repro code below. Somehow EF ends up using the tracked entity instance instead of the new entity instance.

public class EntityB
{
    public int EntityBId { get; set; }
    public EntityA EntityA { get; set; }
}

public class EntityA
{
    public int EntityAId { get; set; }
    public int? EntityBId { get; set; }
    public EntityB EntityB { get; set; }
}

public class BloggingContext : DbContext
{
    public BloggingContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<EntityA> AEntities { get; set; }
    public DbSet<EntityB> BEntities { get; set; }
}

public class Program
{
    public static void Main()
    {
        var connection = new SqliteConnection("DataSource=:memory:");
        connection.Open();

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

            using (var context = new BloggingContext(options))
            {
                context.Database.EnsureCreated();
                context.BEntities.Add(new EntityB { EntityBId = 1 });
                context.SaveChanges();
            }

            using (var context = new BloggingContext(options))
            {
                var b1 = context.BEntities.ToList().Single();
                var b2 = new EntityB { EntityBId = 1 };
                
                context.Add(new EntityA { EntityAId = 1, EntityB = b2 });
                context.SaveChanges();

                var bs = context.BEntities.Local.ToList(); // Only one b, and it's b1!
            }
        }
        finally
        {
            connection.Close();
        }
    }
}

[wtorricos]

@ajcvickers Thank you for your response.

You made me realize it was my mistake. Basically I was expecting this to work:

            using (var context = new BloggingContext(options))
            {
                var b2 = new EntityB { EntityBId = 1 };
                
                context.Add(new EntityA { EntityAId = 1, EntityB = b2 });
                context.SaveChanges();

                var bs = context.BEntities.Local.ToList(); // Only one b, and it's b1!
            }

As equivalent to this:

            using (var context = new BloggingContext(options))
            {
                var b2 = new EntityB { EntityBId = 1 };
                // I realized it's needed to attach it so EF won't try to create a new one
                context.Bentities.Attach(b2);                 

                context.Add(new EntityA { EntityAId = 1, EntityB = b2 });
                context.SaveChanges();
            }

And after your example we can see that we can accomplish the same result by loading the entity, and EF will use the tracked one....

Thanks again you for your time and help!