ASP.net MVC Linq-to-SQL Insertion Problems

TLDR Summary

I'm having a few issues with ASP.net MVC's InsertOnSubmit() function. How do you build and insert new objects into the database without running into key constraints?

Steps to Reproduce

I've gone ahead and created a simple demonstration of the 3-or-so major problems I've been having and maybe someone can help me out here.

1. Create a new ASP.net MVC 2 project.
2. Create two tables- Experiments and Results with a One-To-Many relationship (shown below).
3. Create a new action within the Home controller called "RunExperiment".
4. Create a basic linq-to-sql data context.
5. Have RunExperiment generate a new experiment and results, and insert them into the database via the new data context.

This process falls apart quickly since there's some magic that needs to happen to prevent duplicate primary keys and to ensure that the dependencies stay intact.

Now take a look at my example "RunExperiment" function:

    public ActionResult RunExperiment(string Title, string Scientist)
    {
        RepositoryDataContext RDC = new RepositoryDataContext();

        // Generate the experiment
        Experiment thisExperiment = new Experiment();
        thisExperiment.experimentTitle = Title;
        thisExperiment.experimentScientist = Scientist;

        // Perform some work.
        System.Threading.Thread.Sleep(500);

        // Attempt to insert the experiment.  
        // Works once, fails subsequently due to duplicate primary key.
        RDC.Experiments.InsertOnSubmit(thisExperiment);
        RDC.SubmitChanges();

        for (int i = 0; i < 5; i++)
        {
            Result thisResult = new Result();
            thisResult.resultDate = DateTime.Now;
            thisResult.resultTemp = i;
            thisResult.Experiment = thisExperiment;

            RDC.Results.InsertOnSubmit(thisResult);
            RDC.SubmitChanges();
        }            

        return View("Index");
    }开发者_如何学Python

Any thoughts? I know this is basic stuff, but I'm trying to work away from just copying the Nerd Dinner pattern but I'm getting these errors.

Thanks!

---

Can you confirm the set up of your experimentId column in SQL Server - I believe this should be set up as:

[experimentId] [int] IDENTITY(1,1) NOT NULL

I was able to reproduce your error when I removed the IDENTITY

in LINQ to SQL, the properties for the column should have Int NOT NULL IDENTITY in the "Server Data Type" property

---

First, you'll want to address one of my pet peeves - not disposing your connection.

Then, leverage LINQ to do the foreign key associations for you automatically. Call SubmitChanges() once to submit all of it.

public ActionResult RunExperiment(string Title, string Scientist)
{
    using (RepositoryDataContext RDC = new RepositoryDataContext())
    {
        // Generate the experiment
        Experiment thisExperiment = new Experiment();
        thisExperiment.experimentTitle = Title;
        thisExperiment.experimentScientist = Scientist;

        // Perform some work.
        System.Threading.Thread.Sleep(500);

        for (int i = 0; i < 5; i++)
        {
            Result thisResult = new Result();
            thisResult.resultDate = DateTime.Now;
            thisResult.resultTemp = i;

            // LINQ will automatically wire up the association during the insert
            thisExperiment.Results.Add(thisResult);
        }

        // Attempt to insert the experiment, with associated results
        RDC.Experiments.InsertOnSubmit(thisExperiment);
        RDC.SubmitChanges();
    }

    return View("Index");
}

BTW, the data access piece of this (everything that you're struggling with) is LINQ to SQL, not specific to ASP.NET MVC. You don't have to use LINQ to SQL with MVC, and you don't have to use MVC with LINQ to SQL.

---

It is trying to insert thisExperiment again each time, can you do it the other around, add the results to the result property of the experiment?

or alternatively, just specify the experimentID instead of link them?