Linq-To-SQL Problem Inserting Multiple Objects That Are The Same

Question

Here is my object association:

Summary
-> Job
  -> Operator
-> Job
  -> Operator

So, I have a Summary object that contains a collection of Jobs, which has an Operator object (clock #, name, etc)

When creating a new summary, I go through and create the new jobs that exist and add all the properties. Then, do a single insert on the Summary object that is built. Then, this inserts new jobs and operators if needed, otherwise I may read existing Jobs and Operators from the database.

The problem comes in when I have the same Operator running both Jobs and that Operator does not exist in the database. Linq-To-SQL is trying to insert the same object twice and failing since I'm using the clock # as the primary key.

I can create an auto number for the primary key, but then I'll have duplilcate data and I'd rather not do that. Does anyone have any ideas to get around this?

Thanks!

Answer 1

I think the problem resides in how you write your object creation. I haven't seen your code, but I think you're doing something like this:

Summary summary = new Summary
                                  {
                                      Jobs = new List<Job>
                                                 {
                                                     new Job
                                                         {
                                                             Operator = new Operator {Name = "foo"}
                                                         },
                                                     new Job
                                                         {
                                                             Operator = new Operator {Name = "foo"}
                                                         }
                                                 }
                                  };

If that's your case, try instantiating the operator "foo" beforehand and then assigning it to both jobs.

If you already do it and still get the error, then you should create the operator before and save it to the database, then retrieve it and assign it to the desired jobs.