I am new to ef core. I am trying to implement many to many .

Here is my DBContext

public class MaxCiSDbContext : DbContext
{
    public DbSet<Job> Jobs { get; set; }
    public DbSet<Staff> Staffs { get; set; }

    public MaxCiSDbContext(DbContextOptions<MaxCiSDbContext> options) : base(options)
    {
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Job>()
            .HasMany(t => t.Staffs)
            .WithMany(t => t.Jobs);
            
        base.OnModelCreating(modelBuilder);
    }
}

and Here is my Staff Class

public class Staff
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Mobile { get; set; }
    public string Address { get; set; }

    //Navigation 
    public virtual ICollection<Job> Jobs { get; set; }
}

Here is my Job Class

public class Job
{
    [Key]
    public string Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string State { get; set; }
    public string ClientOrderNumber { get; set; }
    public string StartDate { get; set; }
    public string DueDate { get; set; }
    public string CompletedDate { get; set; }
    public string ClientId { get; set; }
    public string ManagerId { get; set; }
    public string PartnerId { get; set; }
    //Navigation 
    public virtual ICollection <Staff> Staffs { get; set; }
}

I Call an API which returns a XmlDocument, I read that document and update database. Here is how I deal with xmldocument.

        //Fetch Current Jobs and populate to DB
        XmlDocument apiresults = JobMethods.GetCurrent();
        XmlNodeList nodes = apiresults.DocumentElement.SelectNodes("/Response/Jobs/Job");

        foreach (XmlNode node in nodes)
        {
            Job MaxCiSJob = new Job()
            {
                Id = node.SelectSingleNode("ID").InnerText,
                Name = node.SelectSingleNode("Name").InnerText,
                Description = node.SelectSingleNode("Description").InnerText,
                State = node.SelectSingleNode("State").InnerText,
                ClientOrderNumber = node.SelectSingleNode("ClientOrderNumber").InnerText,
                StartDate = node.SelectSingleNode("StartDate").InnerText,
                DueDate = node.SelectSingleNode("DueDate") != null ? node.SelectSingleNode("DueDate").InnerText : "",
                CompletedDate = node.SelectSingleNode("CompletedDate") != null ? node.SelectSingleNode("CompletedDate").InnerText : "",
                ClientId = node.SelectSingleNode("Client/ID").InnerText,
                ManagerId = node.SelectSingleNode("Manager/ID") != null ? node.SelectSingleNode("Manager/ID").InnerText : "",
                PartnerId = node.SelectSingleNode("Partner") != null ? node.SelectSingleNode("Partner").InnerText : ""


            };
            XmlNodeList Assigned = node.SelectNodes("Assigned/Staff");
            MaxCiSJob.Staffs = new Collection<Staff>();
            foreach (XmlNode staffNode in Assigned)
            {
                var staffId = staffNode.SelectSingleNode("ID").InnerText;

                var staff = _db.Staffs.Find(staffId);
                if(staff != null)
                {
                    MaxCiSJob.Staffs.Add(staff);
                }

            }

            if (_db.Jobs.Find(MaxCiSJob.Id) == null)
            {
                //Insert Record
                _db.Jobs.Add(MaxCiSJob);
            }
            else
            {
                // UPDATE recorde
                _db.Jobs.Update(MaxCiSJob);
            }

        }
        _db.SaveChanges();
    }

Everything works well when I run the program for the first time(The linking table ,"JobStaff", is empty) but when I run the Program for the second time I get an excetpion:

SqlException: Violation of PRIMARY KEY constraint 'PK_JobStaff'. Cannot insert duplicate key in object 'dbo.JobStaff'. The duplicate key value is (J14995, 557898).

Can someone please help me on how can I resolve this issue.

CodePudding user response：

Running your code EF core wants to add entities anyway. Because your entities are not attached. Try this code:

//Fetch Current Jobs and populate to DB
XmlDocument apiresults = JobMethods.GetCurrent();
XmlNodeList nodes = apiresults.DocumentElement.SelectNodes("/Response/Jobs/Job");

foreach (XmlNode node in nodes)
{
    var id = node.SelectSingleNode("ID").InnerText;

    Job MaxCiSJob = _db.Jobs.Find(id);

    if (MaxCiSJob == null)
    {
        MaxCiSJob = new Job() { Id = id };

        _db.Jobs.Add(MaxCiSJob);
    }

    MaxCiSJob.Name = node.SelectSingleNode("Name").InnerText;
    MaxCiSJob.Description = node.SelectSingleNode("Description").InnerText;
    MaxCiSJob.State = node.SelectSingleNode("State").InnerText;
    MaxCiSJob.ClientOrderNumber = node.SelectSingleNode("ClientOrderNumber").InnerText;
    MaxCiSJob.StartDate = node.SelectSingleNode("StartDate").InnerText;
    MaxCiSJob.DueDate = node.SelectSingleNode("DueDate") != null ? node.SelectSingleNode("DueDate").InnerText : "";
    MaxCiSJob.CompletedDate = node.SelectSingleNode("CompletedDate") != null ? node.SelectSingleNode("CompletedDate").InnerText : "";
    MaxCiSJob.ClientId = node.SelectSingleNode("Client/ID").InnerText;
    MaxCiSJob.ManagerId = node.SelectSingleNode("Manager/ID") != null ? node.SelectSingleNode("Manager/ID").InnerText : "";
    MaxCiSJob.PartnerId = node.SelectSingleNode("Partner") != null ? node.SelectSingleNode("Partner").InnerText : "";

    XmlNodeList Assigned = node.SelectNodes("Assigned/Staff");
    foreach (XmlNode staffNode in Assigned)
    {
        MaxCiSJob.Staffs.Clear();

        var staffId = staffNode.SelectSingleNode("ID").InnerText;

        var staff = _db.Staffs.Find(staffId);
        if (staff != null)
        {
            MaxCiSJob.Staffs.Add(staff);
        }

    }

}

_db.SaveChanges();

And you should change your domains this way in order not to get NullReferenceException:

public class Staff
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Mobile { get; set; }
    public string Address { get; set; }

    //Navigation 
    public virtual ICollection<Job> Jobs { get; set; } = new Collection<Job>();
}

public class Job
{
    [Key]
    public string Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string State { get; set; }
    public string ClientOrderNumber { get; set; }
    public string StartDate { get; set; }
    public string DueDate { get; set; }
    public string CompletedDate { get; set; }
    public string ClientId { get; set; }
    public string ManagerId { get; set; }
    public string PartnerId { get; set; }
    //Navigation 
    public virtual ICollection<Staff> Staffs { get; set; } = new Collection<Staff>();
}