EF Core / Sqlite one-to-many relationship failing on Unique Index Constraint

Question

The context is each Car has a corresponding CarBrand. Now my classes are as shown below:

public class Car
{
    public int CarId { get; set; }
    public int CarBrandId { get; set; }
    public CarBrand CarBrand { get; set; }
}

public class CarBrand
{
    public int CarBrandId { get; set; }
    public string Name { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Car> Cars { get; set; }
    public DbSet<CarBrand> CarBrands { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(@"Data Source = MyDatabase.sqlite");
    }
}

Here's a sample execution of my code...

class Program
{
    static void Main(string[] args)
    {
        AlwaysCreateNewDatabase();

        //1st transaction
        using (var context = new MyContext())
        {
            var honda = new CarBrand() { Name = "Honda" };
            var car1 = new Car() { CarBrand = honda };
            context.Cars.Add(car1);
            context.SaveChanges();
        }

        //2nd transaction
        using (var context = new MyContext())
        {
            var honda = GetCarBrand(1);
            var car2 = new Car() { CarBrand = honda };
            context.Cars.Add(car2);
            context.SaveChanges(); // exception happens here...
        }
    }

    static void AlwaysCreateNewDatabase()
    {
        using (var context = new MyContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
        }
    }

    static CarBrand GetCarBrand(int Id)
    {
        using (var context = new MyContext())
        {
            return context.CarBrands.Find(Id);
        }
    }
}

The problem is I get 'UNIQUE constraint failed: CarBrands.CarBrandId' exception when car2 is being added to the database with the same CarBrand honda.

What I expect it to do is during 2nd transaction's context.SaveChanges(), it will add car2 and set it's relationship with CarBrand appropriately but I get an exception instead.

EDIT: I really need to get my CarBrand instance in a different context/transaction.

        //really need to get CarBrand instance from different context/transaction
        CarBrand hondaDb = null;
        using (var context = new MyContext())
        {
            hondaDb = context.CarBrands.First(x => x.Name == "Honda");
        }

        //2nd transaction
        using (var context = new MyContext())
        {
            var car2 = new Car() { CarBrand = hondaDb };
            context.Cars.Add(car2);
            context.SaveChanges(); // exception happens here...
        }

Answer 1

You shouldn't set the CarBrand var car2 = new Car() { CarBrand = honda };

You should set the CarBrandId: var car2 = new Car() { CarBrandId = honda.CarBrandId };

And you "can" build the model like so:

            modelBuilder
                .Entity<Car>()
                .HasOne(c => c.CarBrand)
                .WithMany(cb => cb.Cars)
                .HasForeignKey(c => c.CarBrandId)
                .HasConstraintName("FK_Car_CarBrand")
                .IsRequired();

Answer 2

The problem is that Add method cascades:

Begins tracking the given entity, and any other reachable entities that are not already being tracked, in the Added state such that they will be inserted into the database when SaveChanges is called.

There are many ways to achieve the goal, but the most flexible (and I guess the preferred) is to replace the Add method call with the ChangeTracker.TrackGraph method:

Begins tracking an entity and any entities that are reachable by traversing it's navigation properties. Traversal is recursive so the navigation properties of any discovered entities will also be scanned. The specified callback is called for each discovered entity and must set the State that each entity should be tracked in. If no state is set, the entity remains untracked. This method is designed for use in disconnected scenarios where entities are retrieved using one instance of the context and then changes are saved using a different instance of the context. An example of this is a web service where one service call retrieves entities from the database and another service call persists any changes to the entities. Each service call uses a new instance of the context that is disposed when the call is complete. If an entity is discovered that is already tracked by the context, that entity is not processed (and it's navigation properties are not traversed).

So instead of context.Cars.Add(car2); you could use the following (it's pretty generic and should work in almost all scenarios):

context.ChangeTracker.TrackGraph(car2, node =>
    node.Entry.State = !node.Entry.IsKeySet ? EntityState.Added : EntityState.Unchanged);

Answer 3

Quickfix:

EntityFramework Core has problem with your code using new Context inside using of another one. You're mixing states of entities between the two of them. Just use the same context for fetching and creating. If you go with:

using (var context = new MyContext())
{
    var honda = context.CarBrands.Find(1);
    var car2 = new Car() { CarBrand = honda };
    context.Cars.Add(car2);
    context.SaveChanges(); //fine now
    var cars = context.Cars.ToList(); //will get two
}

it should be fine.

---

If you really want to got for two contexts

    static void Main(string[] args)
    {
        AlwaysCreateNewDatabase();

        CarBrand hondaDb = null;
        using (var context = new MyContext())
        {
            hondaDb = context.CarBrands.Add(new CarBrand {Name = "Honda"}).Entity;
            context.SaveChanges();
        }

        using (var context = new MyContext())
        {
            var car2 = new Car() { CarBrandId = hondaDb.CarBrandId };
            context.Cars.Add(car2);
            context.SaveChanges();
        }
    }

You do not depend on change tracking mechanics now but rather on plain FOREIGN KEY relation based on CarBrandId.

---

Reason: Change tracking

Error might be misleading but if you look inside StateManager of your context, you'll see the reason. Because CarBrand you fetched from another Context is the one you're using, for another instance of Context it looks like a new one. You can clearly see it in debug with property EntityState of that particular entity in this particular DB context:

It says that CarBrand is now being Added to database with Id: 1 and that's the UNIQUE constrain of PRIMARY KEY of CarBrand table being broken.

Error says its CarBrandId breaking the constrain because you forced creation of new record in database through relation Car-CarBrand that is backed by CarBrandId.

What would be the state of CarBrand with Id: 1 in context you used to query database? Unchanged of course. But that's the only Context that knows about it:

If you want to go deeper into the topic, read about concept of Change Tracking in EF Core here: https://learn.microsoft.com/en-us/ef/core/querying/tracking