Entity Framework relationships between different DbContext and different schemas

c# dbcontext entity-framework foreign-keys repository

Question

The two primary aims I have are Member and Guild. A Guild may be owned by one Member, and a Guild may have more than one Member.

I have a distinct DbContext and class library for the Members class. I want to utilize this class library across several projects, thus I changed the database schema to "acc" to make it easier to distinguish between them. I have put this library through a lot of testing, and I am able to add, delete, and update Members in the acc.Members database.

As for the Guild class:

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 an alignment 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");
            });
    }
}

However, it states there are no dbo.Members when I try to start a new Guild.

I located the reference to the Member's EF project and added the Members class mapping to the DbContext, which includes the Guild class.modelBuilder.Configurations.Add(new MemberMapping()); (Maybe not the best approach.)

This led to the following error:

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

How can I use the foreign key between these two tables in various database schemas and across DbContexts?

UPDATE

I found the error's root cause. I set the guild leader's Member ID to MemberID when I make a new guild. This is effective. But the problem occurs when I attempt to add that leader's Member object to the Guild's List of Members (Members).

REVISED 2

The code I use to build the Context that the Guild class is in is shown below. (As Hussein Khalil desired)

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; }
}

I'm saving it in the repository like this:

    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();
        }
    }

COMPLETE RESOLUTION

I had to add mappings as a result.Member , Role , andPermission DbContext, which includedGuild . Role and Permission have to be added since Member hadList<Role> Roles every Role hadList<Permission> Permissions .

This helped me get closer to the answer. I continued to have issues like:

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

When you remove the Member from theSession you end up with something like this:

System.Data.Entity.DynamicProxies.Member_FF4FDE3888B129E1538B25850A445893D7C49F878D3CD40103BA1A4813EB514C

This does not appear to operate well with Entity Framework. Why? I'm not sure why, but I believe it is because ContextM produces a proxy of the Member and loses association when the Member is copied into a new Member object. I believe that this enables ContextG to utilize the new Member object at will. I tried making my DbContexts' ProxyCreationEnabled = false, however the Member object that was being taken out of the session kept being of type System. Data.Entity.DynamicProxies.Member.

So here's what I did:

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

I had to duplicate each.Role and everyPermission inside of their respective constructors as well.

I was 99% there after doing this. I have to modify my repo and my method of saving theGuild 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();
1
16
6/16/2015 8:34:09 PM

Accepted Answer

This code is functional.

In the "M" assembly:

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();
    }
}

Assumption "G":

  • your Guild class
  • your Guild mapping, even thoughWillCascadeOnDelete(false) in theLeaderMemberInfo 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();

carried out 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)

When attaching existing objects, this also functions.


Original response for a more generic situation:

Types from various contexts cannot be combined into a single object graph. Therefore, you are unable to take action such as

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

Because the entire SQL query should always be created in a single context, that context should also contain all necessary mapping information.

Even from distinct assemblies, you can register the same type into two different contexts. In order to mapMember in the situation inGuild 's assemblage, shall we saycontextG however, only if

  1. Member does not mention additional types that were mapped in aren'tcontextG This could imply that the navigational features inMember must be expressly disregarded.
  2. Member cannot use types incontextG because these categories do not fall underMember 's setting.

The best you can do is establish a new one if any of these conditions can't be met.Member course inGuild the mapping's assembly and register it in the context. To avoid confusion, you might wish to choose a different name, but this is pretty much your only choice.

12
6/15/2015 7:25:55 AM

Popular Answer

Unless you specify otherwiseMember ought to be mapped toacc.Members EF will anticipate that it is indbo schema Members table. You must do so by offering eitherEntityTypeConfiguration or mark it with this form of annotationSystem.ComponentModel.DataAnnotations.Schema.TableAttribute like [Table("acc.Members")]



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow