ASP.net MVC Linq-to-SQL Insertion Problems

Question

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.

Create a new ASP.net MVC 2 project.
Create two tables- Experiments and Results with a One-To-Many relationship (shown below).
Create a new action within the Home controller called "RunExperiment".
Create a basic linq-to-sql data context.
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.

Example image of database setup.

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");
    }

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!

score 2 · Accepted Answer · answered Jun 06 '11 at 23:06

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

GalacticCowboy · Answer 2 · 2011-06-06T23:35:33.203

2

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.

edited Jun 06 '11 at 23:35

answered Jun 06 '11 at 23:27

GalacticCowboy

11,663
2
41
66

Agreed; this could have been way cleaner. I'm actually following the repository pattern used in the nerd dinner example. Do you create/dispose of your data context for each method, or do you create one instance used by the model/repository and then keep it alive? – ArtOfTheSmart Jun 07 '11 at 02:59
Use the connection pool - that's what it's there for. I don't recall how they did it in Nerd Dinner, but in general it's a best practice to only keep the connection for as long as you need it. – GalacticCowboy Jun 07 '11 at 20:55
Thanks Galactic. What's the best way for me to learn about the connection pool? I'm doing some searches but coming up empty. – ArtOfTheSmart Jul 10 '11 at 05:59
@Mr. Bluesummers - The connection pool is a resource provided by the framework that lets your app bypass the overhead of creating a connection from scratch every time. Without connection pooling, you'd want to hold the open connection for as long as possible to avoid that overhead. With connection pooling, you can safely close your connection handle whenever you're not using it and get a new one with low overhead later. (Note that LINQ will already use connection pooling for you; what I'm describing is already normal behavior for your app so there's not anything special you have to do.) – GalacticCowboy Jul 10 '11 at 18:19
See http://blogs.msdn.com/b/kaevans/archive/2009/04/09/linq-to-sql-and-connection-pooling.aspx for some comments on LINQ to SQL and the connection pool. – GalacticCowboy Jul 10 '11 at 18:21

score 1 · Answer 3 · answered Jun 06 '11 at 22:39

1

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?

answered Jun 06 '11 at 22:39

Paul Creasey

28,321
10
54
90

ASP.net MVC Linq-to-SQL Insertion Problems

3 Answers3