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

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);