How to properly save a complex object using EF Core

Question

I have a problem with saving a complex object... The (simplified) objects look like this:

public class Excavator
    {
        public int Id { get; set; }

        public ExcavatorType Type { get; set; } = new();

        public IList<ExcavatorProperty> Properties { get; set; } = new List<ExcavatorProperty>();

        public IList<SparePart> SpareParts { get; set; } = new List<SparePart>();
    }

public class ExcavatorType
    {
        public int Id { get; set; }

        public IList<ExcavatorPropertyType> PropertyTypes { get; set; } = new List<ExcavatorPropertyType>();

        public IList<Excavator> ExcavatorsOfThisType { get; set; } = new List<Excavator>();
    }

public class ExcavatorProperty
    {
        public int Id { get; set; }

        public ExcavatorPropertyType PropertyType { get; set; } = null!;
    }

public class SparePart
    {
        public int Id { get; set; }

        public IList<Excavator> Excavators { get; set; } = new List<Excavator>();
    }

public class ExcavatorPropertyType
    {
        public int Id { get; set; }

        public IList<ExcavatorType> ExcavatorTypesWithThisProperty { get; set; } = new List<ExcavatorType>()!;
    }

What I want to do is save excavator (which is of type Excavator). Instances of ExcavatorType and SpareParts already exist in the database (so are ExcavatorPropertyTypes).

I filled excavator with data from form and after reading the answer to this question I tried the following:

var excavatorTypeTmp = await context.ExcavatorTypes
    .FirstAsync(et => et.Id == excavator.Type.Id);
excavator.Type = excavatorTypeTmp;

var sparePartsIds = excavator.SpareParts.Select(sp => sp.Id);
excavator.SpareParts = await context.SpareParts
    .Where(sp => sparePartsIds.Contains(sp.Id))
    .ToListAsync();

context.Add(excavator);
await context.SaveChangesAsync();

Error I got: "The instance of entity type 'ExcavatorType' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached."

I also tried .Attach() and .AttachRange():

context.Attach(excavator.Type);
context.AttachRange(excavator.SpareParts);

context.Add(excavator);
await context.SaveChangesAsync();

Error I got: "The instance of entity type 'Excavator' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached."

Then I even tried (because of some comments in the previously mentioned question):

var typeTmp = excavator.Type;
excavator.Type = null!;
context.Attach(typeTmp);
excavator.Type = typeTmp;

var sparePartsTmp = excavator.SpareParts;
excavator.SpareParts = null!;
context.AttachRange(sparePartsTmp);
excavator.SpareParts = sparePartsTmp;

context.Add(excavator);
await context.SaveChangesAsync();

Errot I got: The same as the previous one.

If I'm not mistaken the problem occurs when we try to attach spare parts (context.AttachRange(sparePartsTmp);).

Another thing I found out is that when I'm not saving spare parts, there is no problem. The excavator seems to save correctly (but without spare parts of course).

So I thought that maybe the problem is that I reference the same excavator entities (I try to attach the multiple excavators with the same id) both from excavator.Type and excavator.SpareParts. And when I tried to save it this way (I got rid of references to excavators from spare parts by assigning null)...:

context.Attach(excavator.Type);

var excavatorsTmp = new List<IList<Excavator>>(excavator.SpareParts.Count);
for (int i = 0; i < excavator.SpareParts.Count; i++)
{
    var sparePart = excavator.SpareParts[i];
    excavatorsTmp.Add(sparePart.Excavators);
    sparePart.Excavators = null!;
}
context.AttachRange(excavator.SpareParts);

context.Add(excavator);
await context.SaveChangesAsync();

...it seems to work fine- the excavator was saved correctly. But I believe there is some other (more reasonable) way how to save excavator.

Edit (more info):

I am using using statement (using declaration to be precise). I create context like this:

using var context = factory.CreateDbContext();

Where .CreateDbContext() looks like this:

var connectionString = GetConnectionString();
var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
optionsBuilder.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));
return new MyDbContext(optionsBuilder.Options);

I did it this way because it's a Blazor (server side) app and I have seen it done like this here.

Answer 1

You can use context.Update(yourEntity) insted of context.Add(yourEntity).

.Update() will detect if it's an insert or update of existing entity and will act accordingly. Doc

Answer 2

It seems I have found out the cause of the problem. The code here is simplified, the actual code is a bit more complex. And in some part of the code (which is not shown here) I was adding to list property of a tracked entity instances NOT from the db context. And it seems that was causing the problems.

More detailed explanation: Unfortunately, I can't remember exactly which code caused the problem and which code solved the problem as there were multiple iterations of changes... But as I mentioned earlier my problem was solved by saving only instances from the context.

Let's say we have "new" excavator variable from the "outside" (untracked entity) which we want to save and "old" currentExcavator from the context (i.e. from the database), both are of type Excavator. These objects have some list properties (e.g. SpareParts). We can Add and Remove things from these list properties. Now let's say we have figured out somehow that excavator has 1 new spare part in the SpareParts which currentExcavator doesn't have. Thus we want to call currentExcavator.Add(X) where X is the new spare part (and then context.SaveChangesAsync() of course).

The problem was that the X I was adding was NOT from the context. I should have done context.SpareParts.First(sp => sp.Id == newSparePartId) to get X (where newSparePartId is the id of the new spare part a.k.a X).

The method I use for saving Excavator instances can be found here.