Entity Framework 5 adding existing entity to nested collection

Question

I've been trying to take advantage of a new way of creating many-to-many relationships - nice article about EF 5 many-to-many relationships.

The article states that you no longer need to define relation class and the framework does the job for you.

However, for a couple of hours now I've been struggling to add an existing entity to the collection of another entity.

My models

public record Bottle
{
    [Key]
    public int Id { get; set; }

    [Required]   
    public string Username { get; set; }

    // some other properties

    public Collection<User> Owners { get; set; }
}

public record User
{
    [Key]
    public int Id { get; set; }

    // some other properties

    public Collection<Bottle> Bottles { get; set; }
}

Say that I want to add a new bottle to the database. I also know owners of that bottle. I had thought that this bit of code could work:

public async Task<int> AddBottle(BottleForAddition bottle)
{
    var bottleEntity = mapper.Map<Bottle>(bottle);
    bottleEntity.Owners = bottle
        .OwnerIds // List<int>
        .Select(id => new User { Id = id })
        .ToCollection(); // my extension method

    var createdEntity = await context.AddEntityAsync(bottleEntity);
    await context.SaveChangesAsync();

    return createdEntity.Entity.Id;
}

but sadly it does not work (BottleForAddition is DTO with almost the same properties).

I get this error:

Unable to create bottle (error: Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'NOT NULL constraint failed: Users.Username'.

at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at ...

So I came up with this

public async Task<int> AddBottle(BottleForAddition bottle)
{
    var bottleEntity = mapper.Map<Bottle>(bottle);
    bottleEntity.Owners = (await context.Users
        .Where(u => bottle.OwnerIds.Contains(u.Id))
        .ToListAsync())
        .ToCollection();

    var createdEntity = await context.AddEntityAsync(bottleEntity);

    await context.SaveChangesAsync();

    return createdEntity.Entity.Id;
}

That works but I have to fetch Users from the database.

Do you know about a better way how to deal with it?

Answer 1

1. The Users table in the database has a Username field does not allow NULL
2. You are creating new User entities from the OwnerIds which doesn't have Username value set
3. EF is trying to insert a new user to the Users table

Combining the pieces of information above, you'll get a clear picture why the error message says -

SQLite Error 19: 'NOT NULL constraint failed: Users.Username'.

Then comes the real question, why EF is trying to insert new users at all. Obviously, you created the User entities from the OwnerIds to add already existing users to the list, not to insert them.

Well, I'm assuming that the AddEntityAsync() method you are using (I'm not familiar with it) is an extension method, and inside it, you are using the DbContext.Add() or DbSet<TEntity>.Add() method. Even if that is no the case, apparently AddEntityAsync() at least works similarly as them.

The Add() method causes the entity in question (Bottle) and all it's related entities (Users) present in the entity-graph to be marked as Added. An entity marked as Added implies - This is a new entity and it will get inserted on the next SaveChanges call. Therefore, with your first approach, EF tried to insert the User entities you created. See details - DbSet<TEntity>.Add()

In your second approach, you fetched the existing User entities first. When you fetch existing entities using the DbContext, EF marks them as Unchanged. An entity marked as Unchanged implies - This entity already exists in the database and it might get updated on the next SaveChanges call. Therefore, in this case the Add method caused only the Bottle entity to be marked as Added and EF didn't try to re-insert any User entities you fetched.

As a general solution, in a disconnected scenario, when creating new entity with an entity-graph (with one or more related entities) use the Attach method instead. The Attach method causes any entity to be marked as Added only if it doesn't have the primary-key value set. Otherwise, the entity is marked as Unchanged. See details - DbSet<TEntity>.Attach()

Following is an example -

var bottleEntity = mapper.Map<Bottle>(bottle);
bottleEntity.Owners = bottle
    .OwnerIds // List<int>
    .Select(id => new User { Id = id })
    .ToCollection(); // my extension method

await context.Bottles.Attach(bottleEntity);

await context.SaveChangesAsync();

Not related to the issue :
Also, since you are already using AutoMapper, if you define your BottleForAddition DTO as something like -

public class BottleForAddition
{
    public int Id { get; set; }
    public string Username { get; set; }

    // some other properties

    public Collection<int> Owners { get; set; }     // the list of owner Id
}

then you will be able to configure/define your maps like -

this.CreateMap<BottleForAddition, Bottle>();

this.CreateMap<int, User>()
    .ForMember(d => d.Id, opt => opt.MapFrom(s => s));

which could simplify the operation code like -

var bottleEntity = mapper.Map<Bottle>(bottle);

await context.Bottles.Attach(bottleEntity);

await context.SaveChangesAsync();