How to solve Entity Framework Primary Key Violation on INSERT?

Question

I've tried few things:

1. Attaching data into Context
2. Manually modifying edmx file (here is the source)
3. Manually getting the ForeignKey Object by (data.RTMS_GsmOperator = Context.RTMS_GsmOperator.FirstOrDefault(c => c.Id == data.GsmOperatorId);) and then SaveChanges

Here is the code for the Option 1:

     data.Id = GetMaxId(data)
     Context.Attach(data); //Here I attached it
     Context.RTMS_PackageApplication.AddObject(data);
     Context.SaveChanges(); //I get the error here

Here is the code for the Option 3:

data.Id = GetMaxId(data)

 data.RTMS_GsmOperator = Context.RTMS_GsmOperator.FirstOrDefault(c => c.Id == data.GsmOperatorId); //Here
 data.RTMS_Machine = Context.RTMS_Machine.FirstOrDefault(c => c.Id == data.MachineId); //Here

 Context.RTMS_PackageApplication.AddObject(data);
 Context.SaveChanges(); //I get the error here

None above worked!

NOTE: NONE of the Id's are auto-incremental.

When I run the code below:

public RTMS_PackageApplication Insert(RTMS_PackageApplication data)
{
     using (var Context = base.RtmsEntites)
     {
         //Since, its not auto-incremental, I do it manually.
         data.Id = GetMaxId(data)
         Context.RTMS_PackageApplication.AddObject(data);
         Context.SaveChanges(); //I get the error here
     }
}

Error:

Violation of PRIMARY KEY constraint 'PK_GsmOperator'. Cannot insert duplicate key in object 'dbo.RTMS_GsmOperator'. The duplicate key value is (1).
The statement has been terminated.

If you must know here is the GetMaxId method:

private int GetMaxId(RTMS_PackageApplication data)
{
int Result = 1;
var Temp =
                        base.RtmsEntites.RTMS_PackageApplication.AsQueryable().OrderByDescending(u => u.Id).
                            FirstOrDefault();
                    if (Temp != null)
                        Result = Temp.Id + 1;

return Result;
}

As for the auto-incremental; the problem is on the GsmOperatorId (Foreign Key data table) and the data is ALREADY there, I just want to add the Id into PackageApplciation Table. So, I'm NOT trying to add new GsmOperator only PackageApplication

The EF is trying to INSERT GsmOperator and Machine entities as well. But why? I even re-attached it as in one of the answeres below.

How can I fix this?

Answer 1

Can you try to Save the changes using the same DataContext instance that you use to to load RTMS_GsmOperator ? Something like this:

var opr = theContext.RTMS_GsmOperator.FirstOrDefault(c => c.Id == WHATEVER); 
var pa = new RTMS_PackageApplication(); 
pa.RTMS_GsmOperator = opr; 
pa.RTMS_Machine= <RTMS_Machine_Variable> ; 
opr.RTMS_PackageApplications.Add(pa); 
theContext.SaveChanges();

Answer 2

I think the proper solution here is to make the field an autoincrement id field, instead of trying to fudge it yourself.

Even if you can manage to get past this error, what happens when load increases and multiple clients are inserting records at the same time? - you may very well get conflicts, i.e. two different clients both get told that the next 'maxId' is the same number...Let the DB server handle this for you...that's what its good at.

Answer 3

Ok, I found out that I made a small mistake and thanks to Elio.Batista I realized it.

For validation purposes I set some default values to some properties. Among these properties there are GsmOperator and Machine.

But, instead of setting the GsmOperatorId property, I mistakenly set the RTMS_GsmOperator property which is the E-F entity. And because I set some default value, its not null and E-F recognizes it as a NEW entity and tries to INSERT it.

So, during an EF INSERT, the objects ForeignKey linked entity should be null.

My wrong code:

//Notice that this is partial entity class of EF edmx file
public partial class RTMS_PackageApplication
{
    public RTMS_PackageApplication()
    {
       this.RTMS_GsmOperator = new RTMS_GsmOperator();
       this.RTMS_Machine = new RTMS_Machine();
    }
}

Now, I changed and corrected to:

//Notice that this is partial entity class of EF edmx file
public partial class RTMS_PackageApplication
{
    public RTMS_PackageApplication()
    {
        this.GsmOperatorId = 0; //As Default value
        this.MachineId =  0; //As Default value
    }
}