LINQ to SQL Basic insert throws: Attach or Add not new entity related exception

Question

I am trying to insert a record. This code worked but has stopped working I don't know why. Here is the code:

using (SAASDataContext dc = new SAASDataContext())
{
    tblAssessment a2 = new tblAssessment();

    a2.AssessmentCentreId = centreId;
    a2.AttemptNumber = 1;

    dc.tblAssessments.InsertOnSubmit(a2);
    dc.SubmitChanges();

    CurrentAssessmentId = a2.AssessmentId;
}

The code compiles but throws the exception below on the dc.SubmitChanges(); line.

Exception thrown:

An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

Notes: AssessmentCentreId is a foreign key on tblCentre, centreId is a valid existing centre id, AssessmentCentreId and AttemptNumber are the only not null fields all other columns allow nulls.

I have googled but all the results seem to pertain to people trying to attach entities pulled from other disconnected DataContext's I'm not doing that so I'm stumped.

UPDATE:

Adding

dc.DeferredLoadingEnabled = false;

at the top of the using block makes it work, but I'd like to know why coz I have no idea at the moment sufficiently advanced technology being indistinguishable from magic right now :)

Answer 1

This was bugging me as well. I did some searching and found a lot of confusion and some sloppy work-arounds regarding detached entities. I then found a nice solution on codeplex that has solved the immediate issue and has greatly extended linq2sql functionality. Its a small class thats really easy to implement and forms an EntityBase for your projects dbml's.

Here is the offical description and link. LINQ to SQL Entity Base is a simple base class that is primarily designed to support LINQ to SQL in a disconnected way, which is one of the shortcomings of the LINQ to SQL technology at the present time. This is highly useful in an n-Tier, distributed or ASP.NET environment where disconnected functionality is relavent.

http://linq2sqleb.codeplex.com/

Hope this helps anyone who is having similar.

Jerome Vernon

Answer 2

The issue is that the Centre object doesn't exist in the context. It's in the db, but not "in your hand" with a2. Try this:

a2.AssessmentCentre = dc.AssessmentCentres.SingleOrDefault(
    d=>d.AssessmentCentreId.Equals(centreId));

The AssessmentCentre object will then exist in the context, which means there will be no problem with attaching it to a2.

Answer 3

If these are the only non-null columns, where is the primary key? LINQ to SQL requires a primary key to insert data. If you're using AssessmentCentreId on its own or along with AttemptNumber as a composite, are you sure you're not trying to insert a duplicate key?