Entity Framework Code First Many to many Insert

Question

I am using EF code first approach with fluent api. I am having one registration form in my application where in registering candidate can select multiple options from dropdown(Interested In Dropdown on Sign-Up form) that has a predefined set of options (which may increase in future but the chances are very rare). When the user submits the form I want to save this records to database. So I created following entities.

Participant Class where the registering candidates information will be saved

public class Participant
    {
        public Participant()
        {
            Interests = new Collection<Interest>();
        }
        [Key]
        public int Id { get; set; }
        [DisplayName("First Name")]
        [StringLength(50, ErrorMessage = "First name cannot be more than 50 characters")]
        [Required(ErrorMessage = "You must fill in first name")]
        public string FirstName { get; set; }

        [DisplayName("Last Name")]
        [StringLength(50, ErrorMessage = "Last name cannot be more than 50 characters")]
        [Required(ErrorMessage = "You must fill in last name")]
        public string LastName { get; set; }

        [Required(ErrorMessage = "You must indicate your full birthday")]
        [DisplayName("Birthday")]
        [DataType(DataType.DateTime)]
        public DateTime BirthDate { get; set; }

        [DisplayName("Gender")]
        [Required(ErrorMessage = "You must select gender")]
        public int Gender { get; set; }

        public string Address { get; set; }

        public int CountryId { get; set; }
        public Country Country { get; set; }

        [DisplayName("Zip code")]
        [StringLength(10, ErrorMessage = "Zip code cannot be more than 10 characters")]
        public string ZipCode { get; set; }

        public string Mobile { get; set; }

        public string PhotoUrl { get; set; }

        public int UserId { get; set; }
        public User User { get; set; }

        public virtual ICollection<Interest> Interests { get; set; }
}

Interest Class from where the Interested In dropdown on Sign-up form will get populate *The user can select multiple options from the Interested In dropdown*

public class Interest
    {
        public Interest()
        {
            Participants = new Collection<Participant>();
        }
        public int Id { get; set; }
        public string InterestName { get; set; }
        public virtual ICollection<Participant> Participants { get; set; }
    }

To hold each participants interest I created a ParticipantInterests table in DB with following schema. ParticipantInterests Id (PK) ParticipantId (FK from Participants table) InterestId (FK Interests table)

I added public virtual ICollection<Participant> Participants { get; set; } in Interest model and

public virtual ICollection<Interest> Interests { get; set; } in Participant model to form Many-To-Many association.

My Data Context class is as follows

public class STNDataContext : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<Participant> Participants { get; set; }
        public DbSet<Country> Countries { get; set; }
        public DbSet<Interest> Interests { get; set; }
        public DbSet<Role> Roles { get; set; }
        public DbSet<SecurityQuestion> SecurityQuestions { get; set; }

        public DbSet<Tour> Tours { get; set; }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Participant>().
                HasMany(p => p.Interests).
                WithMany().
                Map(
                    m =>
                    {
                        m.ToTable("ParticipantInterests");
                        m.MapLeftKey("ParticipantId");
                        m.MapRightKey("InterestId");
                    });
            modelBuilder.Entity<User>().HasRequired(u => u.Role);
            modelBuilder.Entity<Participant>().HasRequired(p => p.Country);
            modelBuilder.Entity<Participant>().HasRequired(p => p.Interests);
        }


        public virtual void Commit()
        {
            base.SaveChanges();
        }
    }

Controller Action Code

public virtual ActionResult Register(StudentRegisterViewModel studentRegisterViewModel)
        {
if (ModelState.IsValid)
            {
                if (_userService.IsUserExists(studentRegisterViewModel.Participant.User) == false)
                {
                    studentRegisterViewModel.Participant.User.Username = studentRegisterViewModel.Username;
                    studentRegisterViewModel.Participant.User.Email = studentRegisterViewModel.Email;
                    studentRegisterViewModel.Participant.User.DateCreated = DateTime.Now;
                    studentRegisterViewModel.Participant.User.Id = 3;
                    studentRegisterViewModel.Participant.User.IsApproved = false;
                    studentRegisterViewModel.Participant.User.RoleId = 2;
                    studentRegisterViewModel.Participant.CountryId = 1;
                    foreach (var interestItem in studentRegisterViewModel.SelectedInterests)
                    {
                        var interest = new Interest { Id = interestItem};
                        studentRegisterViewModel.Participant.Interests.Add(interest);
                    }

                    _participantService.CreatParticipant(studentRegisterViewModel.Participant);

                }
            }


            studentRegisterViewModel.Gender =
                Enum.GetNames(typeof(Gender)).Select(
                    x => new KeyValuePair<string, string>(x, x.ToString(CultureInfo.InvariantCulture)));
            studentRegisterViewModel.Interests = _interestService.GetAllInterests();
            return View(studentRegisterViewModel);
        }

When I try to Create Participant I get following error. {"Cannot insert the value NULL into column 'InterestName', table 'StudyTourNetworkDB.dbo.Interests'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated."}

Ideally as per my thinking it should insert Participant Information in Participants table and Participants Interests in ParticipantsInterests table. But it is trying to insert record in Interests table also which should not happen. Please help me resolve this problem. I may be doing wrong by creating many-to-many association.

Thanks

Answer 1

• Remove the Id column from ParticipantInterests table and make ParticipantId and InterestId a composite primary key. Leave them as foreign keys.

• Change your many-to-many mapping to...

  //...
  HasMany(p => p.Interests).
  WithMany(i => i.Participants).
  //...
  

  ... and remove this mapping line:

  modelBuilder.Entity<Participant>().HasRequired(p => p.Interests);
  

• Attach the interest to the context to avoid that EF tries to insert it:

  foreach (var interestItem in studentRegisterViewModel.SelectedInterests)
  {
      var interest = new Interest { Id = interestItem};
      context.Interests.Attach(interest);
      studentRegisterViewModel.Participant.Interests.Add(interest);
  }
  

  You must bring the line that attaches the interest to the context into your service classes. I guess you don't have a context available in your controller. But hopefully you get the idea.