Linq 2 Sql DataContext Update Issue During Fetch/Edit/Update Workflow

Question

In many of my ASP.Net MVC 3 action methods, I'm having some serious issues with trying to update a record (or set of records) resulting in the following exception:

An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.

That's because I retrieve a record via my repository using one DataContext and then, after editing that record, attempt to save it using another new DataContext (i.e. two separate methods in my repository).

The problem is described better than I can ever do in this AzamSharp blog post entitled Dealing with LINQ to SQL DataContext Issues:

http://geekswithblogs.net/AzamSharp/archive/2008/05/17/122222.aspx

I come across many, many posts here and elsewhere with various suggestions, fixes and workarounds but the fact that this seemingly common workflow is not naturally supported in Linq 2 Sql is driving me nuts.

My question(s) is this... should I abandon Linq 2 Sql for a better approach that handles this workflow natively? If so, where should I look (EF, EF Code First, 3rd party)? If not, how do you handle this and get it to work (detach methods in partial classes, timestamps, etc.)?

I could write specific methods that handle the query and update in one method call but that would require me to add business logic inside the repository which is a no-no (or so I'm told). Plus that sounds like stored procedure wrapped in C# syntax. Sort of makes Linq 2 Sql redundant at that point.

Thanks for suggestions. And if you need code samples, etc. please take a look at the linked blog post above. It is much more eloquent than I could ever be at this point.

Joel Cunningham Joel Cunningham · Accepted Answer · 2011-01-02T05:24:44

I would use a DataContext that is created each time you create an instance of a repository and not create a DataContext for every method call like in the example.

Here is an example from the NerdDinners tutorial written by ScottGu on how to use Linq To Sql with ASP.NET MVC. It uses one DataContext per repository. It would create a new repository for each web request.

public class DinnerRepository 
{
    private NerdDinnerDataContext db = new NerdDinnerDataContext();

    //    
    // Query Methods
    public IList<Dinner> FindAllDinners() 
    {
        return db.Dinners.ToList();
    }  

    public IList<Dinner> FindUpcomingDinners() 
    {
        return (from dinner in db.Dinners
               where dinner.EventDate > DateTime.Now
               orderby dinner.EventDate
               select dinner).ToList();
    }    

    public Dinner GetDinner(int id) 
    {
        return db.Dinners.SingleOrDefault(d => d.DinnerID == id);
    }

    //
    // Insert/Delete Methods
    public void Add(Dinner dinner) 
    {
        db.Dinners.InsertOnSubmit(dinner);
    }    

    public void Delete(Dinner dinner) 
    {
        db.RSVPs.DeleteAllOnSubmit(dinner.RSVPs);
        db.Dinners.DeleteOnSubmit(dinner);
    }    

    //
    // Persistence 
    public void Save() 
    {
        db.SubmitChanges();
    }
}

In more complicated scenarios you can create a DataContext and attach it to the currently running thread in ASP.NET if you have to share the DataContext across multiple Repositories.

Linq 2 Sql DataContext Update Issue During Fetch/Edit/Update Workflow

2 Answers