Entity Framework 5 adding existing entity to nested collection

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?

Fetching the Users is generally the correct course of action. This allows you to make the associations but also helps validate that the reference IDs passed from the client are valid. Fetching entities by ID is generally quite fast, so I'd consider avoiding async/await for this operation. async is suited for large or high-frequency operations where server responsiveness could be "hung up". Using it everywhere just leads to slower operations overall.

EF will want to use proxies for navigation properties both for lazy loading (not to be relied on as a crutch, but useful to avoid errors as a worst-case) as well as for change tracking.

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

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

    // some other properties

    public virtual ICollection<User> Owners { get; set; } = new List<User>();
}

then in the applicable code...

var bottleEntity = mapper.Map<Bottle>(bottle);
var users = context.Users
    .Where(u => bottle.OwnerIds.Contains(u.Id))
    .ToList();

foreach(var user in users)
    bottleEntity.Users.Add(user);

// Or since dealing with a new Entity could do this...
//((List<User>)bottleEntity.Users).AddRange(users);

await context.SaveChangesAsync();

return bottleEntity.Id;

It might be tempting to just create the users and attach them to the DbContext and much of the time this would work, except if there is ever the possibility that the DbContext might have been tracking an instance of any of those to-be-attached users, which will result in a runtime error that an entity with the same ID is already being tracked.

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

var proxyUsers = bottle.OwnerIds
    .Select(x => new User { Id = x }).ToList();

foreach(var user in proxyUsers)
{
    context.Users.Attach(user);
    bottleEntity.Users.Add(user);
}
await context.SaveChangesAsync();

return bottleEntity.Id;

This requires either turning off all entity tracking or remember to always query entities with AsNoTracking which can lead to additional work and intermitted bugs appearing if this isn't adhered to consistently. To deal with possible tracked entities is a fair bit more work:

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

var proxyUsers = bottle.OwnerIds
    .Select(x => new User { Id = x }).ToList();
var existingUsers = context.Users.Local
    .Where(x => bottle.OwnerIds.Contains(x.Id)).ToList();
var neededProxyUsers = proxyUsers.Except(existingUsers, new UserIdComparer()).ToList();
foreach(var user in neededProxyUsers)
    context.Users.Attach(user);


var users = neededProxyUsers.Union(existingUsers).ToList();

foreach(var user in users)
    bottleEntity.Users.Add(user);

await context.SaveChangesAsync();

return bottleEntity.Id;

Any existing tracked entity needs to be found and referenced in place of an attached user reference. The other caveat of this approach is that the "proxy" users created for non-tracked entities are not complete user records so later code expecting to get User records from the DbContext could receive these attached proxy rows and result in things like null reference exceptions etc. for fields that were not populated.

Hence, fetching the references from the EF DbContext to get the relatable entities is generally the best/simplest option.