Using EF Core 5, imagine a simple data model with a Car table that has a navigation property of Manufacturer:
public class Car
{
public int CarId { get; set; }
public string ModelName { get; set; }
public Manufacturer Manufacturer { get; set; }
}
public class Manufacturer
{
public int ManufacturerId { get; set; }
public string Name { get; set; }
public string Country { get; set; }
}
Imagine we want to load 2 cars, both manufactured by the same manufacturer with id 1. So our goal is the tables filled like this:
Now imagine we have to load this data in batches. There is one batch for car 1, and one for car 2. In the sample code below, data is saved after each batch and the database context is recreated.
Here is sample code that works just fine:
//-----------------------------------------------------------
// LOAD BATCH 01
//-----------------------------------------------------------
// Create entry for Mini car, manufactured by Leyland
using (var db = new EfDbContext(CONNECTION_STRING))
{
var manuf = new Manufacturer() { ManufacturerId = 1, Name = "Leyland", Country = "UK" };
db.Cars.Add(
new Car()
{
CarId = 1,
ModelName = "Mini",
Manufacturer = manuf,
});
db.SaveChanges();
}
//-----------------------------------------------------------
// LOAD BATCH 02
//-----------------------------------------------------------
// Create entry for Daimler car, also manufactured by Leyland
using (var db = new EfDbContext(CONNECTION_STRING))
{
// Retrieve the existing Leyland manufacturer record
var manuf = db.Manufacturers
.AsNoTracking()
.Where(t => t.ManufacturerId == 1)
.FirstOrDefault();
if (manuf == null) throw new Exception("Should never happen, test is messed up");
var car = new Car()
{
CarId = 2,
ModelName = "Daimler",
Manufacturer = manuf,
};
// Tell EF not to try to update Manufacturer table
db.Entry(car.Manufacturer).State = EntityState.Unchanged; // [1] commenting out this line causes error
db.Cars.Add(car);
db.SaveChanges();
}
See the line above with comment [1]. If that line is commented out, then the loading process errors saying 'UNIQUE constraint failed: Manufacturers.ManufacturerId because EF is trying to insert into Manufacturer table.
All the above works, but it is ugly. Imagine a table with hundreds of navigation properties. Having to manage all of those, working out if we need to set EntityStage.Unchanged for every field and every record feels clumsy.
Is there a better way? Is this where we say this process is not well suited to an ORM and instead we revert to pure SQL or one of the bulk loading tools that there are?
