Owned Collections as Many to Many Relationship

[todd-skelton]

I would like to have owned collections for two classes that share a table that creates a many-to-many relationship. Is there a way to map the intermediate entity as an owned collection on both entities?

Unhandled Exception: System.InvalidOperationException: Cannot use table 'Accounts' for entity type 'User.Accounts#Account' since it is being used for entity type 'Application.Accounts#Account' and there is no relationship between their primary keys.
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.ValidateSharedTableCompatibility(IReadOnlyList`1 mappedTypes, String tableName)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.ValidateSharedTableCompatibility(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Internal.SqlServerModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ValidatingConvention.Apply(InternalModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelBuilt(InternalModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.ModelBuilder.FinalizeModel()
   at System.Lazy`1.ViaFactory(LazyThreadSafetyMode mode)
   at System.Lazy`1.ExecutionAndPublication(LazyHelper executionAndPublication, Boolean useDefaultConstructor)
   at System.Lazy`1.CreateValue()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.EntryWithoutDetectChanges[TEntity](TEntity entity)
   at Microsoft.EntityFrameworkCore.DbContext.SetEntityState[TEntity](TEntity entity, EntityState entityState)

Steps to reproduce

    public class User
    {
        public Guid Id { get; set; }
        public string Username { get; set; }
        public ICollection<Account> Accounts { get; set; }
    }

    public class Application
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public ICollection<Account> Accounts { get; set; }
    }

    public class Account
    {
        public Guid UserId { get; private set; }
        public Guid ApplicationId { get; private set; }
    }

    public class SecurityDbContext : DbContext
    {
        public SecurityDbContext(DbContextOptions options) : base(options)
        {
        }

        public DbSet<Application> Applications { get; set; }
        public DbSet<User> Users { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Application>(builder =>
            {
                builder.OwnsMany(e => e.Accounts, o =>
                {
                    o.ToTable("Accounts");
                    o.HasForeignKey(e => e.ApplicationId);
                    o.HasKey(e => new { e.UserId, e.ApplicationId });
                });
            });

            modelBuilder.Entity<User>(builder =>
            {
                builder.OwnsMany(e => e.Accounts, o =>
                {
                    o.ToTable("Accounts");
                    o.HasForeignKey(e => e.UserId);
                    o.HasKey(e => new { e.UserId, e.ApplicationId });
                });
            });
        }
    }

Further technical details

EF Core version: 2.2.0 Database Provider: Microsoft.EntityFrameworkCore.SqlServer Operating system: Windows 10 IDE: Visual Studio 2017 15.9.3

[ajcvickers]

@xKloc Can you give us some insights on why you want to use owned collections for this?

[todd-skelton]

The main goal was to use nested owned types and collections on my two aggregates (user and application), so I would load the entire object graph without having to chain all the includes. I tried to use two different types mapped to the same table, but I run into the same issue. I have to create a one-to-one relationship between the two types, and I can't do that because they are owned entities.

I just created some extension methods that perform all the includes for now.

[ajcvickers]

@xKloc Thanks for the additional info. I'm linking this to issue #1985, which is about defining aggregate behaviors, and #2953 which is about rule-based eager loading.

[divega]

I gave some thinking to this, so capturing some notes:

1. Rule-based eager loading that is independent of ownership/aggregates could be a good fit for this particular ask. Though in my opinion this is not as important as aggregate behaviors. I feel it is reasonable to say no to this in 3.0 and in the meantime try to improve our messaging on what ownership is and isn't for.

2. Having the same object instance be owned by multiple owners (as the issue is described originally) would require EF Core to deviate more from its current design/assumptions. For example, we could enable advanced mapping in which we stop altogether protecting customers from mapping multiple objects to the same table and row (currently this only works if there is a 1:1 relationship or one of the types is a query types), and trust them that they will find ways to use such mappings correctly (e.g. if they never modify two objects that map to the same row in the same unit of work, the should be fine). Incidentally this is maps to the idea of supporting multiple bounded contexts in the same model and DbContext. This also seems useful (NHibernate users have been complaining for years that EF doesn't allow this) but it doesn't feel high priority for 3.0.

[AndriySvyryd]

This is mainly a duplicate of #2953

[pantonis]

@AndriySvyryd @ajcvickers any update on this. I run into a similar case and I cannot define a many-to-many between two entities when the join table is a ValueObject (ownsmany)

[AndriySvyryd]

@pantonis We aren't planning to support this as it doesn't match owned entities semantics (they can only have one owner).