Unable to add data into Many-To-Many relationship database table with code first

Question

I have 3 tables that are connected together as follows:

Survey

public enum Language
    {
        Danish,
        English
    }

    public class Survey
    {
        public Survey()
        {
            Id = Guid.NewGuid();
            DateCreated = DateTime.Now;
            Sort = 0;
        }

        [Key]        
        public Guid Id { get; set; }

        public bool Active { get; set; }

        public string Title { get; set; }

        public bool Status { get; set; }

        [Display(Name = "Kommentar")]
        public string Comment { get; set; }

        public string MAilReciever { get; set; }

        public string CCMailReciever { get; set; }

        public string BBCMailReciever { get; set; }

        public int Sort { get; set; }

        public DateTime DateCreated { get; set; }

        public string StartText { get; set; }

        public string EndText { get; set; }

        public string RegardText { get; set; }

        public Language Language { get; set; }

        public virtual ICollection<UserSurvey> UserSurveys { get; set; }

        public virtual ICollection<Chapters> Chapters { get; set; }

        public virtual ICollection<Questions> Questions { get; set; }
    }

ApplicationUser The default ApplicationUser table from Mvc with identity.

UserSurvey (My relationShipTable between Survey and ApplicationUser)

public class UserSurvey
    {
        [Key, Column(Order = 0)]
        public Guid SurveyId { get; set; }
        [Key, Column(Order = 1)]
        public string ApplicationUserId { get; set; }

        public virtual Survey Survey { get; set; }

        public virtual ApplicationUser ApplicationUser { get; set; }

        public bool Active { get; set; }

        public DateTime StartDate { get; set; }

        public DateTime EndDate { get; set; }

        public string RegardText { get; set; }
    }

I'm trying to add a new relation between a user and a servey by the following code:

UserSurvey Item = new UserSurvey();
Item.Survey = s;
Item.ApplicationUser = userinfo;
Item.Active = true;
Item.StartDate = Convert.ToDateTime(dateFrom);
Item.EndDate = Convert.ToDateTime(dateTo);
db.UserSurvey.Add(Item);
db.SaveChanges();

The error message I get says that I cant "Duplicate the SurveyId primary key because it already exist in the database (table survey). I know it exist but I just want to make a relation between users and surveys, not create a new survey or user.

Thanks for your help

Answer 1

Your problem is that you are providing a completed UserSurvey object with nested objects (Survey/ApplicationUser) populated. When you do that and set the changeset as "Add" (caused by db.UserSurvey.Add(Item);). EF will attempt to issue INSERT INTO for the parent (UserSurvey) and all child/related(Survey/ApplicationUser) tables since it thinks that the "Add" changeset applies to all of these tables. To resolve your issue, you need to just provide the Ids. That will just insert a new relationship:

UserSurvey Item = new UserSurvey();
//Item.Survey = s; //This object filling is making EF believe that you are attempting to add a new record in Survey. Comment it out
//Item.ApplicationUser = userinfo; //This object filling is making EF believe that you are attempting to add a new record in ApplicationUser. Comment it out
Item.ApplicationUserId = userinfo.ApplicationUserId;//or whatever is the id column name in ApplicationUser table/class
Item.SurveyId = s.SurveyId;//I see that you are missing SurveyId property in UserSurvey. You will need to add this. This will be the Foreign Key to Survey table just like you have ApplicationUserId FK to ApplicationUser table.
Item.Active = true;
Item.StartDate = Convert.ToDateTime(dateFrom);
Item.EndDate = Convert.ToDateTime(dateTo);
db.UserSurvey.Add(Item);
db.SaveChanges();

So, the underlying idea is to fill the Ids if the nested records (Survey/Application in this case) already exist. You only populated nested object when you want EF to attempt to INSERT them too for you. Dont populate them if you dont want this. Just the Ids help EF to create just the relationship and not go after creating these related records again for you.

Hope this helps.