asp.net mvc - Inserting multiple records in the third table having foreign keys to parent using entity framework 6

Question

I am trying to learn how to use Entity Framework 6 with an already created database, without creating an .edmx file, i.e, using the DbContext and POCO classes.

These are my model classes:

[Table("Category")]
public class Category
{
   [Key]
   public long CategoryID { get; set; }
   public string CategoryName { get; set; }
}

[Table("RegistrationForm")]
public class RegistrationForm
{
    [Key]
    public int RegistrationID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; } 
    public int Country { get; set; }        
    public string Email { get; set; }       
    public string Phone { get; set; }  
}   

[Table("RegistrationCategory")]
public class RegistrationCategory
{
    [Key]
    public long RegistrationCategory { get; set; }
    public long RegistrationID { get; set; }//Foreign key to RegistrationID in RegistrationForm table in database
    public long CategoryID { get; set; }//Foreign key to CategoryID in Category table in database
}

My DbContext class:

public class MyContext : DbContext
{
    public virtual DbSet<RegistrationForm> RegistrationForm { get; set; }
    public virtual DbSet<Category> Category { get; set; }
    public virtual DbSet<RegistrationCategory> RegistrationCategory { get; set; }
}

Here I want to use the default model builder of DbContext.User can select multiple categories in the registration screen so the RegistrationCategory table will have multiple records for each registration. Therefore RegistrationForm and RegistrationCategory are in a one-to-many relationship.

How to write foreign key mappings between the above mentioned models?

How to bind data from Category table data in the mvc view(listbox) so that we can save one record in RegistrationForm table and multiple records in RegistrationCategory table without using loops (using mappings between the c# models) in Entity Framework 6?

@Claies I am still very new to entity framework so I don't know how navigation properties work... — seadrag0n
– seadrag0n, Commented Mar 15, 2015 at 8:38
It looks like RegistrationCategory is a join table that doesn't have it's own properties? it doesn't really look like it even needs a model. — Claies
– Claies, Commented Mar 15, 2015 at 8:38
@Claies yes there are no properties, it is a relation table between Category and RegistrationForm which will multiple records for each category selected for a registration.. — seadrag0n
– seadrag0n, Commented Mar 15, 2015 at 8:40

Claies · Accepted Answer · 2015-03-15 09:24:37Z

1

The database schema that you have here is a Many to Many relationship between RegistrationForm and Category, with a join table. The RegistrationCategory Table is not necessary to be modeled in Entity Framework at all. You will need to use Entity Framework Fluent API to generate the correct mappings.

First, your RegistrationForm Table:

public class RegistrationForm
{
    [Key]
    public int RegistrationID { get; set; }
    ...

    // add a navigation property ICollection<Category> to reference the categories
    public virtual ICollection<Category> Categories { get; set; }
}

Next, the Category class:

public class Category
{
    [Key]
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }

    //Navigation property to reference the RegistrationForms
    public virtual ICollection<RegistrationForm> RegistrationForms { get; set; }
}

next, in your DbContext: note the change in pluralization, and the removal of the RegistrationCategory, you do not need a model class for it at all.

public class MyContext : DbContext
{
    public virtual DbSet<RegistrationForm> RegistrationForms { get; set; }
    public virtual DbSet<Category> Categories { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<RegistrationForm>()
        .HasMany(r => r.Categories)
        .WithMany(c => c.RegistrationForms)
        .Map(
            m =>
            {
              m.MapLeftKey("RegistrationID");
              m.MapRightKey("CategoryID");
              m.ToTable("RegistrationCategory");
            }
        );
}

With this in place, you can now query all the Categories of a RegistrationForm or all the RegistrationForms of a Category.

foreach (var category in registrationForm.Categories)
{
    //do whatever with each category
}

edited Mar 15, 2015 at 9:24

answered Mar 15, 2015 at 9:18

Claies

22.4k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

seadrag0n Over a year ago

how to bind Categories using @Html.ListBox in the view? I have take that data in a separate List in a ViewModel class? How to save multiple selected categories?

Claies Over a year ago

yes, Categories would need to be a list in your ViewModel. if you sent the entire List to the View, and received back a subset, you could loop through the subset and add each member to the list on the registrationForm object. If you aren't sure how to implement that, it's probably more appropriate as a whole new question.

seadrag0n Over a year ago

so to read selected values from the ListBox, I have to declare a List<int> property, bind it to the ListBox, loop through the selected values and add new items in ICollection<Category> Categories { get; set; } and finally call .Add() and SaveChanges() methods on the context?

Claies Over a year ago

The Listbox can be bound to a List<Category> and the selected items can be another List<Category>... you don't even really have to loop through, just assign the registrationForm.Categories = selection, unless you want to do anything with the list....

seadrag0n Over a year ago

I have created two List<Category> properties in my ViewModel class, public List<Category> Categories { get; set; }, public List<Category> SelectedCategories { get; set; } one to bind the master data and one to get the selected categories and I have created ListBox using @Html.ListBoxFor(model => model.SelectedCategories, new SelectList(Model.Categories, "CategoryID", "CategoryName")), but I am getting 0 values on POST in SelectedCategories property...

|

Collectives™ on Stack Overflow

asp.net mvc - Inserting multiple records in the third table having foreign keys to parent using entity framework 6

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related