Entity Framework relationships between different DbContext and different schemas

Question

So, I have two main objects, Member and Guild. One Member can own a Guild and one Guild can have multiple Members.

I have the Members class in a separate DbContext and separate class library. I plan to reuse this class library in multiple projects and to help differentiate, I set the database schema to be "acc". I have tested this library extensively and can add, delete, and update Members in the acc.Members table.

The Guild class is as such:

public class Guild
{
    public Guild()
    {
        Members = new List<Member>();
    }

    public int ID { get; set; }
    public int MemberID { get; set; }
    public virtual Member LeaderMemberInfo { get; set; }
    public string Name { get; set; }
    public virtual List<Member> Members { get; set; }
}

with a mapping of:

internal class GuildMapping : EntityTypeConfiguration<Guild>
{
    public GuildMapping()
    {
        this.ToTable("Guilds", "dbo");
        this.HasKey(t => t.ID);
        this.Property(t => t.MemberID);
        this.HasRequired(t => t.LeaderMemberInfo).WithMany().HasForeignKey(t => t.MemberID);
        this.Property(t => t.Name);
        this.HasMany(t => t.Members).WithMany()
            .Map(t =>
            {
                t.ToTable("GuildsMembers", "dbo");
                t.MapLeftKey("GuildID");
                t.MapRightKey("MemberID");
            });
    }
}

But, when I try to create a new Guild, it says that there is no dbo.Members.

I got reference to the Member's EF project and added the mapping to the Members class to the DbContext that the Guild class is a part of. modelBuilder.Configurations.Add(new MemberMapping()); (Not sure if that is the best way.)

This resulted with this error:

{"The member with identity 'GuildProj.Data.EF.Guild_Members' does not exist in the metadata collection.\r\nParameter name: identity"}

How can I utilize the foreign key between these two tables cross DbContexts and with different database schemas?

UPDATE

I narrowed down the cause of the error. When I create a new guild, I set the guild leader's Member ID to MemberID. This works fine. But, when I then try to add that leader's Member object to the Guild's List of Members (Members), that's what causes the error.

UPDATE 2

Here is the code of how I create the Context that the Guild class is in. (As requested by Hussein Khalil)

public class FSEntities : DbContext
{
    public FSEntities()
    {
        this.Configuration.LazyLoadingEnabled = false;
        Database.SetInitializer<FSEntities>(null);
    }

    public FSEntities(string connectionString)
        : base(connectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new GuildMapping());
        modelBuilder.Configurations.Add(new KeyValueMappings());
        modelBuilder.Configurations.Add(new LocaleMappings());

        modelBuilder.Configurations.Add(new MemberMapping());
    }

    public DbSet<Guild> Guilds { get; set; }
    public DbSet<KeyValue> KeyValues { get; set; }
    public DbSet<Locale> Locales { get; set; }
}

This is how I am saving it in the repo:

    public async Task CreateGuildAsync(Guild guild)
    {
        using (var context = new FSEntities(_ConnectionString))
        {
            context.Entry(guild.Members).State = EntityState.Unchanged;
            context.Entry(guild).State = EntityState.Added;
            await context.SaveChangesAsync();
        }
    }

FINAL RESOLUTION

So, I had to add mappings to Member, Role, and Permission in DbContext that contained Guild. I had to add Role and Permission because Member had List<Role> Roles and each Role had List<Permission> Permissions.

This got me closer to the solution. I was still getting errors like:

{"The member with identity 'GuildProj.Data.EF.Member_Roles' does not exist in the metadata collection.\r\nParameter name: identity"}

Here, when you pull Member from the Session, you get something like this:

System.Data.Entity.DynamicProxies.Member_FF4FDE3888B129E1538B25850A445893D7C49F878D3CD40103BA1A4813EB514C

Entity Framework does not seem to play well with this. Why? I am not sure, but I think it is because ContextM creates a proxy of Member and by cloning the Member into a new Member object, ContextM no longer has association. This, I think, allows ContextG to use the new Member object freely. I tried setting ProxyCreationEnabled = false in my DbContexts, but the Member object being pulled out of Session kept being of type System.Data.Entity.DynamicProxies.Member.

So, what I did was:

Member member = new Member((Member)Session[Constants.UserSession]);

I had to clone each Role and each Permission as well inside their respective constructors.

This got me 99% of the way there. I had to alter my repo and how I was saving the Guild object.

            context.Entry(guild.LeaderMemberInfo).State = EntityState.Unchanged;
            foreach(var member in guild.Members)
            {
                context.Entry(member).State = EntityState.Unchanged;
            }
            context.Entry(guild).State = EntityState.Added;
            await context.SaveChangesAsync();

Answer 1

This is working code:

In assembly "M":

public class Member
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class MemberMapping : EntityTypeConfiguration<Member>
{
    public MemberMapping()
    {
        this.HasKey(m => m.Id);
        this.Property(m => m.Name).IsRequired();
    }
}

In assemby "G":

• your Guild class
• your Guild mapping, albeit with WillCascadeOnDelete(false) in the LeaderMemberInfo mapping.
• modelBuilder.Configurations.Add(new GuildMapping()); and modelBuilder.Configurations.Add(new MemberMapping());

Code:

var m = new Member { Name = "m1" };
var lm = new Member { Name = "leader" };
var g = new Guild { Name = "g1" };
g.LeaderMemberInfo = lm;
g.Members.Add(lm);
g.Members.Add(m);
c.Set<Guild>().Add(g);
c.SaveChanges();

Executed SQL:

INSERT [dbo].[Members]([Name])
VALUES (@0)
SELECT [Id]
FROM [dbo].[Members]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
-- @0: 'leader' (Type = String, Size = -1)

INSERT [dbo].[Guilds]([MemberID], [Name])
VALUES (@0, @1)
SELECT [ID]
FROM [dbo].[Guilds]
WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()
-- @0: '1' (Type = Int32)
-- @1: 'g1' (Type = String, Size = -1)

INSERT [dbo].[GuildsMembers]([GuildID], [MemberID])
VALUES (@0, @1)
-- @0: '1' (Type = Int32)
-- @1: '1' (Type = Int32)

INSERT [dbo].[Members]([Name])
VALUES (@0)
SELECT [Id]
FROM [dbo].[Members]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
-- @0: 'm1' (Type = String, Size = -1)

INSERT [dbo].[GuildsMembers]([GuildID], [MemberID])
VALUES (@0, @1)
-- @0: '1' (Type = Int32)
-- @1: '2' (Type = Int32)

This also works when associating existing objects.

---

Original answer for more general case:

You can't combine types in different contexts into one object graph. That means, you can't do something like

from a in context.As
join b in context.Bs on ...

...because there's always one context that should create the whole SQL query, so it should have all required mapping information.

You can register the same type into two different contexts though, even from different assemblies. So you could map Member in the context in Guild's assembly, let's call it contextG, but only if

1. Member doesn't refer to other types that aren't mapped in contextG. This may imply that navigation properties in Member must be ignored explicitly.
2. Member can't refer to types in contextG, because these types are not part of Member's context.

If any of these conditions can't be fulfilled the best you can do is create a new Member class in Guild's assembly and register its mapping in the context. Maybe you want to use a different name to prevent ambiguity, but this is about the only alternative left.