0

I am designing a web application with ASP.NET using MVC 5 and EF 6. Our team uses code first migrations to design our database. In our project we have two models - Location and Recreation. Recreation is essentially an enumerable tag that can be applied to any Location (many-to-many relationship), so I designed a 3rd bridge entity model to handle the relations (so I can reference it in the code). Here are the abbreviated model definitions:

Location:

/* * Represents a location on a map. */ public class Location { public int LocationID { get; set; } public String Label { get; set; } public double Latitude { get; set; } public double Longitude { get; set; } public virtual ICollection<Recreation> Recreations { get; set; } } 

Recreation:

/* * Represents a recreation activity type, such as Hiking or Camping, that * can be applied to any location as a tag (each Location may have 0 or more * Recreation tags). */ public class Recreation { public int RecreationID { get; set; } public string Label { get; set; } public virtual ICollection<Location> Locations { get; set; } } 

Bridge Entity:

/* * Bridge entity to handle associations of Location and Recreation */ public class LocationRecreation { [Key] [ForeignKey("Location")] [Column(Order = 1)] [Display(Name = "Location")] public int LocationID { get; set; } [Key] [ForeignKey("Recreation")] [Column(Order = 2)] [Display(Name = "Recreation")] public int RecreationID { get; set; } public virtual Location Location { get; set; } public virtual Recreation Recreation { get; set; } } 

When I add a migration and run update-database, this works with the default scaffolding of controllers/views. But, upon looking in the Server Explorer tab, I see the following tables:

  • Locations
  • Recreations
  • LocationRecreations
  • RecreationLocations (extra table?)

The views process CRUD on LocationRecreations, but the virtual properties point to RecreationLocations, so they don't work as that table remains empty.

What is causing the migrations to create a duplicate table? Can I modify my models in some way to allow for only one table to be created, and so that the virtual properties function as intended?

EDIT:

I have re-created the error in a new Visual Studio Project hosted here on Github. For clarity, I selected Individual User accounts, and thus am using the single ApplicationDbContext in the IdentityModels.cs file for my db context:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser> { public ApplicationDbContext() : base("DefaultConnection", throwIfV1Schema: false) { } public DbSet<Location> Locations { get; set; } public DbSet<Recreation> Recreations { get; set; } public static ApplicationDbContext Create() { return new ApplicationDbContext(); } // Associate a Location with a Recreation. public void AddOrUpdateRecreationLocation(string locationLabel, string recreationLabel) { var location = this.Locations.SingleOrDefault(l => l.Label == locationLabel); var recreation = location.Recreations.SingleOrDefault(r => r.Label == recreationLabel); //i if it does not exist, register the item. if (recreation == null) location.Recreations.Add(this.Recreations.Single(r => r.Label == recreationLabel)); } } 
3
  • I used your model classes and got only the 3 required tables, not the extra one. The only thing I did differently was not use update-database, I just created a controller & view and ran the app. Don't know what the problem is. Commented Feb 20, 2015 at 7:57
  • I think the problem is I needed to have the entity framework build the bridge table for me, and access it only using virtual properties in the Recreation and Location classes. I removed the LocationRecreation class and table, and now things work ok, except i can't access the table directly from a DbContext class. I will leave this question up in case others have more insights on the topic. Commented Feb 20, 2015 at 8:28
  • I don't think you gave the complete setup and steps to reproduce. Can you reproduce this problem starting from an empty project, with no pre-existing database? Commented Feb 20, 2015 at 19:19

1 Answer 1

1

I resolved my issue. Location and Relation both need a virtual ICollection<LocationRelation> (a collection of my bridge entity). The problem was I was telling the entity framework to point indirectly to the related items, and thus it inferred to create a third bridge table for me.

The LocationRecreation and DbContext code is fine. Here are the modified Location and Recreation models:

/* * Represents a location on a map. */ public class Location { public int LocationID { get; set; } public String Label { get; set; } public double Latitude { get; set; } public double Longitude { get; set; } public virtual ICollection< LocationRecreation > LocRecs { get; set; } } /* * Represents a recreation activity type, such as Hiking or Camping, that * can be applied to any location as a tag (each Location may have 0 or more * Recreation tags). */ public class Recreation { public int RecreationID { get; set; } public string Label { get; set; } public virtual ICollection< LocationRecreation > LocationRecs { get; set; } } 
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.