none
SubmitOnChange inserting duplicated values for referenced tables RRS feed

  • Question

  • Hello,

    I am doing my first steps in LINQ, and I am facing a small problem, I hope you can help me.

    I have a WCF project with a GetTask and a SaveTask method which I am testing using the WCF Test Client.

    A Task is a table in my DB and references (FK) many other tables like Severity, Team, User, etc.

    I inserted a task directly in the DB and I am able to call the GetTask method to get it, then I copy the data (and change the PK) and call the SaveTask() method (using the WCF Client).

    I get an exception that says that I am trying to add a duplicated key in the severity table, but this is not the behavior I want. I dont want to create a new severity, but only to reference an already existing one.

    Here is my code in my SaveTask method:

    TrackingDataContext dataConext = new TrackingDataContext();
    
    dataConext.Tasks.InsertOnSubmit(task);
    dataConext.SubmitChanges();
    

    Why is it trying to add new items in the reference tables?

     

    One more question: the desired behavior for this method id: if the ID is null, it creates a new task (in my DB ID is a identity column with auto increment). IS this the behavior of tje InsertOnSubmit method? Or should I implement by myself?

     

    Thank you,

    Oscar


    My Blog - Dicas .NET
    Friday, December 3, 2010 2:01 PM

Answers

  • I inserted a task directly in the DB and I am able to call the GetTask method to get it, then I copy the data (and change the PK) and call the SaveTask() method (using the WCF Client).

    I get an exception that says that I am trying to add a duplicated key in the severity table, but this is not the behavior I want. I dont want to create a new severity, but only to reference an already existing one.

    Here is my code in my SaveTask method:

    Why is it trying to add new items in the reference tables?


    The SaveTask method looks fine, so it must be something you are doing from GetTask and copying the data. Some questions:

    • Are you creating a new task object or reusing the one you've retrieved in GetTask?
    • How are you associating the task object with the severity object?

    I think the following should work:

    Task existingTask = // select the existing task record;
    
    Task newTask = new Task();
    
    newTask.severity_id = existingTask.severity_id;
    // plus copying the other fields
    // but don't set newTask.Id
    
    dataConext.Tasks.InsertOnSubmit(newTask);
    dataConext.SubmitChanges();
    
    

    There are two ways to associate the task and severity; one is to use the id fields (as per my example above) and the second is to use the navigation properties (e.g. newTask.Severity = existingTask.Severity) - don't use both.

    I'm not sure if that lot will solve your duplicate key problem; if not, post a bit more about what you do between GetTask and SaveTask please.

    • Marked as answer by Oscarfh Monday, December 6, 2010 10:27 PM
    Monday, December 6, 2010 1:54 PM
  • One more question: the desired behavior for this method id: if the ID is null, it creates a new task (in my DB ID is a identity column with auto increment). IS this the behavior of tje InsertOnSubmit method? Or should I implement by myself?

    LINQ does it for you, though it is actually done with the SubmitChanges().

    Task task = new Task();
    
    // Don't set task.Id, leave it null.
    
    dataConext.Tasks.InsertOnSubmit(task);
    
    // still null
    
    dataConext.SubmitChanges();
    
    // New value assigned by SubmitChanges() and available as task.Id
    
    
    

     

    If you want to create two associated records with one SubmitChanges, then use the navigation property. For example:

    Severity sev = new Severity();
    
    Task task = new Task();
    
    Task.Severity = sev;
    
    dataConext.Tasks.InsertOnSubmit(task);
    dataConext.Severities.InsertOnSubmit(sev);
    
    dataConext.SubmitChanges();
    
    // Hey presto; task's severity ID is automatically set to the new severity record's auto-id.
    
    

     

    • Marked as answer by Oscarfh Monday, December 6, 2010 10:28 PM
    Monday, December 6, 2010 2:03 PM

All replies

  • I inserted a task directly in the DB and I am able to call the GetTask method to get it, then I copy the data (and change the PK) and call the SaveTask() method (using the WCF Client).

    I get an exception that says that I am trying to add a duplicated key in the severity table, but this is not the behavior I want. I dont want to create a new severity, but only to reference an already existing one.

    Here is my code in my SaveTask method:

    Why is it trying to add new items in the reference tables?


    The SaveTask method looks fine, so it must be something you are doing from GetTask and copying the data. Some questions:

    • Are you creating a new task object or reusing the one you've retrieved in GetTask?
    • How are you associating the task object with the severity object?

    I think the following should work:

    Task existingTask = // select the existing task record;
    
    Task newTask = new Task();
    
    newTask.severity_id = existingTask.severity_id;
    // plus copying the other fields
    // but don't set newTask.Id
    
    dataConext.Tasks.InsertOnSubmit(newTask);
    dataConext.SubmitChanges();
    
    

    There are two ways to associate the task and severity; one is to use the id fields (as per my example above) and the second is to use the navigation properties (e.g. newTask.Severity = existingTask.Severity) - don't use both.

    I'm not sure if that lot will solve your duplicate key problem; if not, post a bit more about what you do between GetTask and SaveTask please.

    • Marked as answer by Oscarfh Monday, December 6, 2010 10:27 PM
    Monday, December 6, 2010 1:54 PM
  • One more question: the desired behavior for this method id: if the ID is null, it creates a new task (in my DB ID is a identity column with auto increment). IS this the behavior of tje InsertOnSubmit method? Or should I implement by myself?

    LINQ does it for you, though it is actually done with the SubmitChanges().

    Task task = new Task();
    
    // Don't set task.Id, leave it null.
    
    dataConext.Tasks.InsertOnSubmit(task);
    
    // still null
    
    dataConext.SubmitChanges();
    
    // New value assigned by SubmitChanges() and available as task.Id
    
    
    

     

    If you want to create two associated records with one SubmitChanges, then use the navigation property. For example:

    Severity sev = new Severity();
    
    Task task = new Task();
    
    Task.Severity = sev;
    
    dataConext.Tasks.InsertOnSubmit(task);
    dataConext.Severities.InsertOnSubmit(sev);
    
    dataConext.SubmitChanges();
    
    // Hey presto; task's severity ID is automatically set to the new severity record's auto-id.
    
    

     

    • Marked as answer by Oscarfh Monday, December 6, 2010 10:28 PM
    Monday, December 6, 2010 2:03 PM
  • I am using the WCF Test Client, then it is not code, I am really copying the values to the input mask. I will test it soon with code, then I will know if it is working :)

     

    tks!


    My Blog - Dicas .NET
    Monday, December 6, 2010 10:29 PM
  • Good luck. Sorry I don't know WCF test client.
    Wednesday, December 8, 2010 8:44 PM