Adding new item in One-To-Many relationship throws exception within an One-To-One relationship

[dmoka]

I am trying to add a new entity in a One-To-Many relationship. Normally this works, but in this case I have an One-To-One relationship, where the target entity has a collection of other entities which I want to populate. I am using SQLite in memory database and I want to keep using it.

To Reproduce

I made a simplified version of my problem from the production code in order to make it more understandable. I have a parent entity called WorkEntity. It has an one-to-one relationship with the entity called QuotationEntity. This QuotationEntity has an One-To-Many relationship with QuotationLine. So far so good. Let's see how the classes look like. First the WorkEntity:

    public class WorkEntity : Entity, IAggregateRoot
    {
        public string Name { get; set; }

        public QuotationEntity QuotationEntity { get; set; }


        public WorkEntity()
        {
            QuotationEntity = new QuotationEntity();
        }
    }

Then the QuotationEntity

    public class QuotationEntity : Entity
    {
        public string Name { get; set; }

        public Guid WorkEntityId { get; set; }
        public WorkEntity WorkEntity { get; set; }

        public ICollection<QuotationLine> Lines { get; set; } = new List<QuotationLine>();
    }

Last but not least the QuotationLine:

    public class QuotationLine : Entity
    {
        public string Price { get; set; }

        public Guid QuotationEntityId { get; set; }
        public QuotationEntity QuotationEntity { get; set; }
    }

Let's see how my test code looks like and what the occurred exception is I am struggling with:

        [Fact]
        public async Task TestAddingChildElement()
        {
            var repo = new WorkEntityRepository(DbContext);
            var workEntity = new WorkEntity();
            repo.Insert(workEntity);

            await DbContext.SaveChangesAsync();

            var workFromDb = await repo.GetAsync(workEntity.Id);
            workFromDb.QuotationEntity.Lines.Add(new QuotationLine());

            await DbContext.SaveChangesAsync();
        }

My test is fairly simple. First I create WorkEntity with its corresponding QuotationEntity without no QuotationLine. After persisting this entity, I retrieve it with the corresponding repository, then I try to add a QuotationLine to the QuotationEntity. When the DbContext.SaveChangesAsync() is invoked at the second time, I got the following exception:

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

The WorkRepository class looks like this:

    public class WorkEntityRepository : IWorkEntityRepository
    {
        private readonly DbSet<WorkEntity> _entities;
        private readonly WarehousingDbContext _dbContext;

        public WorkEntityRepository(WarehousingDbContext dbContext)
        {
            if (dbContext == null)
            {
                throw new ArgumentNullException(nameof(dbContext));
            }

            _dbContext = dbContext;
            _entities = dbContext.Set<WorkEntity>();
        }

        public Task<WorkEntity> GetAsync(Guid id)
        {
            return GetWorkEntityIncludingChilds().SingleAsync(p => p.Id == id);
        }


        public WorkEntity Insert(WorkEntity workEntity)
        {

            var entity = _entities.Add(workEntity);

            return entity.Entity;
        }


        private IQueryable<WorkEntity> GetWorkEntityIncludingChilds()
        {
            return _entities
                .Include(c => c.QuotationEntity)
                    .ThenInclude(w => w.Lines);
        }
    }

For configuring the One-To-One relationship between WorkEntity and QuotationEntity I use the following code snippet:

            builder.HasOne(a => a.QuotationEntity)
                .WithOne(b => b.WorkEntity)
                .HasForeignKey<QuotationEntity>(b => b.WorkEntityId);

Last but not least, I use in memory SQLite database and EF Core 3.1 for reproducing this issue:

        private void SetUpInMemorySqlLiteDb(ServiceCollection services)
        {
            services.AddDbContext<WarehousingDbContext>(o => o
                .UseSqlite("Data Source=:memory:")
                .EnableSensitiveDataLogging());
        }

Since I have spent much time on trying to fix the issue, I have some findings:

1. One possible reason for this exception could be that child entities are not included when the WorkEntity is retrieved from the repository. If I would not include them, then it would be logical that the entity is not tracked by the EF ChangeTracker. But as you can see I include all the child entities.

2. What I found out during debugging is that the QuotationLine has not got the correct state. If I set its state explicitly like:

            var quotationLine = new QuotationLine();
            DbContext.Entry(quotationLine).State = EntityState.Added;
            workFromDb.QuotationEntity.Lines.Add(quotationLine);

then everything works as expected. But I think it is not the solution, it is just a workaround. The EFCore should handle the ChangeTracking of the element, at least I would expect it from the framework. Or am I wrong?

Let me know then I can provide more code for my problem if needed. Thanks in advance for help!

Additional context

Microsoft.Data.Sqlite version: Microsoft.EntityFrameworkCore.Sqlite, Version=3.1.0.0
Target framework: 3.1
Operating system: Windows

[ajcvickers]

@mirind4 This code is problematic:

public WorkEntity()
{
    QuotationEntity = new QuotationEntity();
}

Since QuotationEntity is an entity type, this code creates a new child entity instance for every parent, even if there is already an existing entity in the database. See #18007 for more details.

[dmoka]

Hey @ajcvickers!

Many thanks for your answer, I apprecaite it! I am not sure that it is the main cause what you mentioned. Let's say that I do not initialize the QuotationEntity inside the constructor of WorkEntity, and I only create it via DBContext. It results in the same exception...Check this out, please:

[Fact]
public async Task Test()
{
    var repo = new WorkEntityRepository(DbContext);
    var workEntity = new WorkEntity();
    repo.Insert(workEntity);
    await DbContext.SaveChangesAsync();

    DbContext.QuotationEntities.Add(new QuotationEntity() { WorkEntityId = workEntity.Id });
    await DbContext.SaveChangesAsync();

    var workFromDb = await repo.GetAsync(workEntity.Id);
    workFromDb.QuotationEntity.Lines.Add(new QuotationLine() { Price = "30000" });
    await DbContext.SaveChangesAsync();
}

public class WorkEntity : Entity, IAggregateRoot
{
    public string Name { get; set; }

    public QuotationEntity QuotationEntity { get; set; }
}

At the last SaveChangesAsync() the same exception is thrown. What do you think? Thanks in advance!

[ajcvickers]

@mirind4 Guid key properties are configured to use generated values by default. This means that EF can use the key value to determine whether or not an entity instance represents a new or existing row in the database by checking if the key value has been set. However, in this code:

DbContext.QuotationEntities.Add(new QuotationEntity() { WorkEntityId = workEntity.Id });

the key value is explicitly set even though the entity is new. This can be fixed in two ways:

• Don't set the key value explicitly--let EF set it based on the defined relationship.
• Configure key properties with modelBuilder.Entity<QuotationEntity>().Property(e => e.WorkEntityId).ValueGeneratedNever(); and always set key values explicitly. You'll then also need to make sure the correct state is set for each tracked instance.

The first approach is usually preferred.

[brgrz]

Hmm having similar issue with Guid FKs. An item has FK to another item in a one to many relationship and when adding new item to the primary items collection those new items are marked with Modified state - the change tracker says because the FK has changed.

The FK property was of type Guid and obviously had the default value of Empty guid when class was instantiated.

So I figured this is why EF was saying the FK changed and I went and changed this property to nullable, Guid?.

Now when instantiating the property is null.

It only gets its FK value assigned when calling .Add() on the main item collection.

Yet the tracker still says that this new collection item's state is Modified.

Because of this new entity being marked as Modfied, when calling SaveChangesAsync() a DbUpdateConcurrencyException is raised I believe because it is trying to find the entity in the database but it's not there. A TimeStamped Version property on the entity is at 0.

Clearly this has to be a bug or particularly weird design?