Need help on EF Code First Fluent Mapping Many To Many

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; }

        [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

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

DataContext

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)
                {
                    // Attempt to register the user

                    //WebSecurity.CreateUserAndAccount(model.UserName, model.Password);
                    //WebSecurity.Login(model.UserName, model.Password);
                    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;
                    var participant = new Participant
                    {
                        Id = studentRegisterViewModel.Participant.Id,
                        FirstName = studentRegisterViewModel.Participant.FirstName,
                        LastName = studentRegisterViewModel.Participant.LastName,
                        Interests = new Collection<Interest>()
                    };
                    var interests = new List<Interest>();
                    foreach (var interestItem in studentRegisterViewModel.SelectedInterests)
                    {
                        var interest = new Interest { Id = interestItem, Participants = new Collection<Participant>() };
                        interest.Participants.Add(participant);
                        interests.Add(interest);
                    }
                    studentRegisterViewModel.Participant.Interests = interests;
                    _participantService.CreatParticipant(studentRegisterViewModel.Participant);
                    //_userService.CreatUser(studentRegisterViewModel.Participant.User);
                    //TODO: Need to check if do we need to register the user and get him signed-in. If yes signing in implementation goes here.
                    var user = _userService.GetUser(studentRegisterViewModel.Participant.User.Username);
                    //Session["User"] = user;
                    //FormsAuthentication.SetAuthCookie(user.Username, false);
                    //Growl("Welcome", "Thanks for registering and welcome to Truck Tracker.");
                    //return RedirectToAction("Index", "Home");
                }
            }
            //return RedirectToAction("Index", "Home");
            // If we got this far, something failed, redisplay form
            studentRegisterViewModel.Gender =
                Enum.GetNames(typeof(Gender)).Select(
                    x => new KeyValuePair<string, string>(x, x.ToString(CultureInfo.InvariantCulture)));
            studentRegisterViewModel.Interests = _interestService.GetAllInterests();
            return View(studentRegisterViewModel);
        }

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

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. The registering participant can select multiple interested in options and when he clicks Sign Up button the Participant record will get saved in Participant Table and selected Interests in ParticipantInterests table.

Thanks

Sorry, what exactly is the error? – ledgeJumper Dec 06 '12 at 06:08 — ledgeJumper, Dec 06 '12 at 06:08

ledgeJumper · Answer 1 · 2012-12-06T07:25:27.773

I was messing around with the same things the other day. Pain to get it figured out but here is a very basic example of a many to many I got working:

namespace Project.Models
{
    public class Affiliate
    {
        public Affiliate()
        {
            Merchants = new HashSet<Merchant>();
        }
        public int Id { get; set; }
        public ICollection<Merchant> Merchants { get; set; }
    }
}


namespace Project.Models
{
    public class Merchant
    {
        public Merchant()
        {
            Affiliates = new HashSet<Affiliate>();
        }
        public int Id { get; set; }
        public ICollection<Affiliate> Affiliates{ get; set; }
    }
}

and in the DbContext I did this:

            modelBuilder.Entity<Merchant>().
              HasMany(c => c.Affiliates).
              WithMany(p => p.Merchants).
              Map(
               m =>
               {
                   m.MapLeftKey("MerchantId");
                   m.MapRightKey("AffiliateId");
                   m.ToTable("MerchantAffiliates");
               });

Update

I am trying to understand what you are looking to accomplish. It seems like this:

A participant can be interested in many things. Multiple participants can be interested in the same thing. If that is the fact I would consider changing the model so a participant has a list of interests, but the interests table, in my opinion, does not need a list of participants. If you wanted to retrieve say all participants with an interest in hiking you could just do something like this:

Context.Participants.ToList().Where(x => x.Interest.Name == "hiking");

Make sense?

Thanks davidisawesome your solution is absolutely correct but I think I have fallen short to explain the problem precisely. I have updated the problem defination. Thanks a lot. — pbhalchandra, Dec 06 '12 at 07:04

Need help on EF Code First Fluent Mapping Many To Many

1 Answers1