locked
Need help on EF code first many-to-many RRS feed

  • Question

  • I am developing an ASP.NET MVC4 application with EF Code First. I am having a many-to-many relationship among following classes. I have defined relationship using EF fluent api in my context class. But I am getting an error as it is trying to insert values into one of my master table involved in many-to-many relation. Can anyone help me correct my problem. Thanks in advance and for valuable time.I am using repository pattern and unit of work with Ninject as dependency injection.

    Participant Class

    public class Participant
        {
            [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; }

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

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

    Interest Class

    public class Interest
        {
            public int Id { get; set; }
            public string InterestName { get; set; }
            public virtual ICollection<Participant> Participants { get; set; }
        }

    Data Context

    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(i => i.Participants).
                    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);
            }
            public virtual void Commit()
            {
                base.SaveChanges();
            }
        }

    Controller 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);
                        var user = _userService.GetUser(studentRegisterViewModel.Participant.User.Username);
                        }
                }
                studentRegisterViewModel.Gender =
                    Enum.GetNames(typeof(Gender)).Select(
                        x => new KeyValuePair<string, string>(x, x.ToString(CultureInfo.InvariantCulture)));
                studentRegisterViewModel.Interests = _interestService.GetAllInterests();
                return View(studentRegisterViewModel);
            }

    Participant (Left Table)

    Id

    FirstName

    LastName

    ParticipantInterests (Many-to-Many Table)

    ParticipantId

    InterestId

    Interests(Right Table)

    Id

    InterestName

    This is how the tables in the database. The Interest table has a fixed set of records(Study, Job, Other,etc) which get displayed in Interested In dropdown on Sign-up form. The registering participant can select multiple interested in options while signing up and when he clicks Sign Up button the Participant record should get saved in Participant Table and selected Interests in ParticipantInterests table.

    Ideally it should insert Participant into Participants Table and Participant Interests in ParticipantInterests many-to-many table. But it is giving 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."}

    It is trying to insert into Interests table which should not happen.

    Participant ParticipantInterests Interests Id Id Id FirstName ParticipantId InterestName LastName InterestId


    Thursday, December 6, 2012 9:03 AM

Answers

  • Hi,

    I think, in this line: studentRegisterViewModel.Participant.Interests.Add(interest);

    The interest object you add has not the value for InterestName whcih is required. Please assign a value to it or make it nullable (public String? InterestName { get; set; })

    • Marked as answer by Alexander Sun Thursday, December 20, 2012 7:50 AM
    Sunday, December 9, 2012 3:39 PM