LINQ(2 SQL) Insert Multiple Tables Question

Question

I have 3 tables. A primary EmploymentPlan table with PK GUID EmploymentPlanID and 2 FK's GUID PrevocServicesID & GUID JobDevelopmentServicesID. There are of course other fields, almost exclusively varchar(). Then the 2 secondary tables with the corresponding PK to the primary's FK's.

I am trying to write the LINQ INSERT Method and am struggling with the creation of the keys. Say I have a method like below. Is that correct? Will that even work? Should I have seperate methods for each?

Also, when INSERTING I didn't think I needed to provide the PK for a table. It is auto-generated, no?

Thanks,

public static void InsertEmploymentPlan(int planID, Guid employmentQuestionnaireID, string user, bool communityJob, bool jobDevelopmentServices, 
                                                                            bool prevocServices, bool transitionedPrevocIntegrated, bool empServiceMatchPref)
    {
        using (var context = MatrixDataContext.Create())
        {
                var empPrevocID = Guid.NewGuid();
                var prevocPlan = new tblEmploymentPrevocService
                                     {
                                         EmploymentPrevocID = empPrevocID
                                     };
                context.tblEmploymentPrevocServices.InsertOnSubmit(prevocPlan);

                var empJobDevID = Guid.NewGuid();
                var jobDevPlan = new tblEmploymentJobDevelopmetService()
                {
                    JobDevelopmentServicesID = empJobDevID
                };
                context.tblEmploymentJobDevelopmetServices.InsertOnSubmit(jobDevPlan);

                var empPlan = new tblEmploymentQuestionnaire
                                  {
                                      CommunityJob = communityJob,
                                      EmploymentQuestionnaireID = Guid.NewGuid(),
                                      InsertDate = DateTime.Now,
                                      InsertUser = user,
                                      JobDevelopmentServices = jobDevelopmentServices,
                                      JobDevelopmentServicesID =empJobDevID,
                                      PrevocServices = prevocServices,
                                      PrevocServicesID =empPrevocID,
                                      TransitionedPrevocToIntegrated =transitionedPrevocIntegrated,
                                      EmploymentServiceMatchPref = empServiceMatchPref 
                                  };
                context.tblEmploymentQuestionnaires.InsertOnSubmit(empPlan);

                context.SubmitChanges();
        }
    }

---

I understand I can use more then 1 InsertOnSubmit, See SO ? HERE, I just don't understand how that would apply to my situation and the PK/FK creation.

Answer 1

I think the issue is (if I understand it correctly) you are deferring the inserting, except you don't know it...

Since you're creating FKs but differing their insertion until the end, it doesn't know what to do, so when you try to create the main entry it's enforcing the FK constraints (which might not exist yet), thus failing. Try creating the FK entries and actually submitting the changes to the database before insert the main entry.

For example, say you have the following tables: Child Toy ToyOwner

ToyOwner has FK constraints on Child and Toy. If the entries are missing in that table, you will not be able to insert an entry into ToyOwner. So you'd have to do something like the following:

    Child myChild;
    Toy myToy;

//Queue up the changes that are going to be submitted
    InsertOnSubmit(myChild)
    InsertOnSubmit(myToy)

//Submit the queue
    SubmitChanges();

//Now that those FKs are filled, we can insert the main entry with those FK values
    ToyOwner = new myToyOwner
    myToyOwner.Child = myChild
    myToyOwner.Toy = myToy

//And insert the new queue into the DB
    InsertOnSubmit(myToyOwner)
    SubmitChanges();

Answer 2

The pk can be auto generated when the table's definition in the db does it for you. Also the property for the corresponding pk on the linq model has to configured to be updated after the insert, so it gets the auto generated ID.

I don't think the relation on those tables is on your linq model. Otherwise you should be able to do:

    using (var context = MatrixDataContext.Create())
    {
            var empPlan = new tblEmploymentQuestionnaire
                              {
                                  CommunityJob = communityJob,
                                  InsertDate = DateTime.Now,
                                  InsertUser = user,
                                  JobDevelopmentServices = jobDevelopmentServices,
                                  JobDevelopmentService = new tblEmploymentJobDevelopmetService(),
                                  PrevocServices = prevocServices,
                                  PrevocService = new tblEmploymentPrevocService(),
                                  PrevocServicesID =empPrevocID,
                                  TransitionedPrevocToIntegrated =transitionedPrevocIntegrated,
                                  EmploymentServiceMatchPref = empServiceMatchPref 
                              };
            context.tblEmploymentQuestionnaires.InsertOnSubmit(empPlan);

            context.SubmitChanges();
    }

ps. not having the relation in the model is a design decision, so the above doesn't mean that's the only way to do it. The way you showed (with the extra SubmitChanges calls as in the other answer) is perfectly valid, just responds to a different design.