Entity Framework Code First Many to Many Setup For Existing Tables

Question

I have the following tables Essence, EssenseSet, and Essense2EssenceSet

Essense2EssenceSet is the linking table that creates the M:M relationship.

I've been unable to get the M:M relationship working though in EF code first though.

Here's my code:

[Table("Essence", Schema = "Com")]
    public class Essence
    {
        public int EssenceID { get; set; }
        public string Name { get; set; }
        public int EssenceTypeID { get; set; }
        public string DescLong { get; set; }
        public string DescShort { get; set; }
        public virtual ICollection<EssenceSet> EssenceSets { get; set; }
        public virtual EssenceType EssenceType { get; set; }
    }

    [Table("EssenceSet", Schema = "Com")]
    public class EssenceSet
    {
        public int EssenceSetID { get; set; }
        public int EssenceMakerID { get; set; }
        public string Name { get; set; }
        public string DescLong { get; set; }
        public string DescShort { get; set; }

        public virtual ICollection<Essence> Essences { get; set; }
    }

[Table("Essence2EssenceSet", Schema = "Com")]
    public class Essence2EssenceSet
    {
        //(PK / FK)
        [Key] [Column(Order = 0)] [ForeignKey("Essence")] public int EssenceID { get; set; }
        [Key] [Column(Order = 1)] [ForeignKey("EssenceSet")] public int EssenceSetID { get; set; }

        //Navigation
        public virtual Essence Essence { get; set; }
        public virtual EssenceSet EssenceSet { get; set; }
    }
            public class EssenceContext : DbContext
            {
                public DbSet<Essence> Essences { get; set; }
                public DbSet<EssenceSet> EssenceSets { get; set; }
                public DbSet<Essence2EssenceSet> Essence2EssenceSets { get; set; }

                protected override void OnModelCreating(DbModelBuilder mb)
                {
                    mb.Entity<Essence>()
                        .HasMany(e => e.EssenceSets)
                        .WithMany(set => set.Essences)
                        .Map(mc =>
                            {
                                mc.ToTable("Essence2EssenceSet");
                                mc.MapLeftKey("EssenceID");
                                mc.MapRightKey("EssenceSetID");
                            });
                }
        }

This is the code I'm trying to run:

    Essence e = new Essence();
                            e.EssenceTypeID = (int)(double)dr[1];
                            e.Name          = dr[2].ToString();
                            e.DescLong      = dr[3].ToString();

                            //Get Essence Set
                            int setID = (int)(double)dr[0];
                            var set = ctx.EssenceSets.Find(setID);
                            e.EssenceSets = new HashSet<EssenceSet>();
                            e.EssenceSets.Add(set);
                            ctx.Essences.Add(e);
ctx.SaveChanges();

And here's the error:

An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception.

I'm not able to find the problem. I'd greatly appreciate help setting this up right. Thanks!

score 26 · Accepted Answer · edited May 14 '19 at 15:38

26

Remove your Essence2EssenceSet model class. If junction table contains only keys of related entities participating in many-to-many relations it is not needed to map it as entity. Also make sure that your fluent mapping of many-to-many relations specifies schema for table:

mb.Entity<Essence>()
  .HasMany(e => e.EssenceSets)
  .WithMany(set => set.Essences)
  .Map(mc =>
      {
          mc.ToTable("Essence2EssenceSet", "Com");
          mc.MapLeftKey("EssenceID");
          mc.MapRightKey("EssenceSetID");
      });

edited May 14 '19 at 15:38

bug

143
1
9

answered May 17 '11 at 09:13

Ladislav Mrnka

360,892
59
660
670

You are correct, thank you! I did forget about the Table Schema. Do you feel it is bad form to expose the junction table in cases like this when it is not (loaded) with additional fields? – user169867 May 17 '11 at 16:02
@Ladislav Mrnka, Is there no way to do this with explicitly defining the table and using DataAnnotations? I find it much easier to maintain and comment my code using DataAnnotations instead of Fluent Mapping. – Scott Aug 24 '11 at 14:40
@Scott: I'm not aware of the way to do this with data annotations. Data annotations are for the simplest scenarios where you let EF to generate the database and you don't bother with its structure or naming. – Ladislav Mrnka Aug 24 '11 at 15:24
1

What if there are additional fields in the existing table. How can I map them? – Leon Mar 03 '12 at 17:24
@Leon: In such case you must map it as a separate entity. – Ladislav Mrnka Mar 03 '12 at 23:53
@Ladislav Mrnka: Do you mean instead of a many to many, treat it as 2 one to many? If not, could you please show me how it (many to many) can be done assuming Essence2EssenceSet has an additional field? Thanks – Leon Mar 04 '12 at 12:00
@Leon: Yes you will create a new entity for your junction table and replace many-to-many relation with two ont-two-many relations. – Ladislav Mrnka Mar 04 '12 at 21:15
@Nikos I know this is very late, but the correct place is in OnModelCreating (`override`) of your DbContext subclass (probably called BaseContext). – bug May 16 '19 at 18:31

Entity Framework Code First Many to Many Setup For Existing Tables

1 Answers1

Linked