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.

Answer 2

I have found that when I am having problem with Entity and building relationships it's often because I am going against the flow of the framework or trying to build relationships or abstractions that are not technically well-formed. What I would suggest here is to take a quick step back and analyze a few things before looking deeper into this specific issue.

First off, I am curious why you are using different schema here for what is likely a single application accessing an object graph. Multiple schemas can be useful in some context but I think Brent Ozar makes a very salient point in this article. Given that information, I am inclined to first suggest that you merge the multiple schema into one and push over to using a single DB context for the database.

The next thing to tackle is the object graph. At least for me the biggest struggles I have had with data modeling are when I don't first figure out what questions the application has of the database. What I mean by this is figure out what data does the application want in its various contexts first and then look at how to optimize those data structures for performance in a relational context. Let's see how that might be done ...

Based on your model above I can see that we have a few key terms/objects in the domain:

• Collection of Guilds
• Collection of Members
• Collection of Guild Leaders.

Additionally we have some business rules that need to be implemented:

• A Guild can have 1 leader (and possibly more than 1?)
• A Guild Leader must be a Member
• A Guild has a list of 0 or more Members
• A Member can belong to a Guild (and possibly more than 1?)

So given this information let's investigate what questions your application might have of this data model. I the application can:

• look up a member and see their properties
• look up a member and see their properties and that they are a guild leader
• look up a guild and see a list of all its members
• look up a guild and see a list of guild leaders
• look up a list of all guild leaders

Ok, now we can get down to brass tacks, as they say...

The use of the join table between Guilds and Members is optimal in this instance. It will provide you the ability to have members in multiple guilds or no guilds and provide a low locking update strategy - so good call!

Moving onto Guild Leaders there are a few choices that might make sense. Even though there may never be a case for say guild sergeants, I think it makes sense to consider a new entity called a Guild Leader. What this approach allows for is several fold. You can cache in app the list of guild leader ids, so rather than make a db trip to authorize a guild action taken by a leader you can hit local application cache that only has the leader id list and not the whole leader object; conversely, you can get the list of leaders for a guild and regardless of the query direction you can hit clustered indexes on the core entities or the easy-to-maintain intermediate index on the join entity.

Like I noted at the start of this way to long "answer", when I run into issues like yours, it's typically because I am going against the grain of Entity. I encourage you to re-think your data model and how with a lower friction approach - loose the multiple schema and add an intermediate guild_leader object. Cheers!

Answer 3

Unless you explicitly say, that Member entity should be mapped to acc.Members, EF will expect it to be in dbo schema Members table. For this you need to provide either EntityTypeConfiguration for this type or annotate it with System.ComponentModel.DataAnnotations.Schema.TableAttribute like [Table("acc.Members")]

Answer 4

I am answering your updated question :

try using this line before updating your context

context.Entry(Guild.Members).State = Entity.EntityState.Unchanged

this will solve the error you have

Answer 5

I know this is not relevant anymore, but I had the same problem and wanted to share my solution to anyone who comes across this post:

I understood the question as follows:

• You wanted to create a Database with schema "a" and "b"
• You wanted to have a connection between these two schema's

My Solution (using EFCore 5):

DBModel Schema "a":

• Users

Schema "b":

• Sessions
• Jwts

Following code snippets are inside the OnModelCreating method of the context for schema "a":

base.OnModelCreating(builder);

builder.HasDefaultSchema("a");
        
builder.Entity<User>(entity =>
{
     entity.Property(x => x.FirstName);
     entity.Property(x => x.LastName);
});

It could happen, that EF Core notices the navigation properties on the User class and includes them in the migration, since we don't want to include them we have to specifically ignore them:

builder.Ignore<Session>();
builder.Ignore<Jwt>();

Following code snippets are inside the OnModelCreating method of the context for schema "b":

base.OnModelCreating(builder);

builder.HasDefaultSchema("b");
        
builder.Entity<Session>(entity =>
{
     entity.HasOne(x => x.User)
         .WithMany(x => x.Sessions)
         .HasForeignKey(x => x.UserId);

     entity.HasMany(x => x.Jwts)
         .WithOne(x => x.Session)
         .HasForeignKey(x => x.SessionId);

     entity.Property(x => x.UserAgent);
});

builder.Entity<User>(entity => {
    entity.ToTable("Users", "a", t => t.ExcludeFromMigrations())
});

This is a bit counterintuitive, because you tell EF Core to exclude the User table from the migration, but since you already created the table in the context for schema "a", there is no need to create it again so you have to exclude it. Why should we not use "builder.Ignore()" then? Because we have to tell EF Core the schema of the table and this is only possible through this method.

To do this does not have any direct advantages except that you can share code more easily. For example, you could have a base structure for a common service like User. Now you want to build upon it, but always want the User entity in you structures, without syncing the databases. You can build now a UserService with a User -> Session -> Jwt structure and create any other service connected with the user structure, for example Blog Posts with a User -> Blog -> Posts, User -> Posts.

This way you always use the same User table and don't have to sync between the services.

CAREFULL: This has one major drawback, because you could slow down database performance if the specific entity has a lot of updates. This is because a specific entity can only be changed by one process, for something like a user this is not a big problem, but could become one if the specific case is met.