L2S DataContext out of synch: row not found or changed

Psssst...!

Read on, by all means. But I can tell you here that the problem had nothing to do with the DataContext, but with Dependency Injection. I have left the question up, as it documents one possible issue with the "row not found or changed error" that has nothing to do with real world concurrency conflicts.

It seems the problems have been caused by badly written dependency injection. Or rather, I am beginning to believe, by default lifecycle management by the DI container I used.

The problem was that I used a DataContext as a constructor argument that was supplied by Ninject. It seems that the default behaviour was to cache this DataContext, leading to all manner of unexpected behaviour. I will ask a separate question about this.

Anyway, what follows is my original question, which as you will see, misses the real cause of the issue by a mile...

The Problem

I am getting a number of errors that imply that the DataContext, or rather, the way I am using the DataContext is getting out of开发者_JAVA技巧 synch.

The error occurs on db.SubmitChanges() where db is my DataContext instance. The error is:

Row not found or changed.

The problem only occurs intermitently, for example, adding a row then deleting it. If I stop the dev server and restart, the added row is there and I can delete it no problem.

Ie, it seems that the problem is related to the DataContext losing track of the rows that have been added.

IMPORTANT:

Before anyone votes to close this thread, on the basis of it being a duplicate, I have checked the sql server profiler and there is no "Where 0 = 1" in the SQL.

I have also recreated the dbml file, so am satisfied that the database schema is in synch with the schema represented by the dbml file.

Ie, no cases of mismatched nullable/not nullable columns, etc.

My Diagnosis (for what it is worth):

The problem seems (to me) related to how I am using the DataContext. I am new to MVC, Repositories and Services patterns, so suspect that I have wired things up wrong.

The Setup

Simple eLearning app in its early stages. Pupils need to be able to add and delete courses (Courses table) to their UserCourses.

To do this, I have a service that gets a specific DataContext instance Dependency Injected into its constructor.

Service Class Constructor:

public class SqlPupilBlockService : IPupilBlockService
{
    DataContext db;
    public SqlPupilBlockService(DataContext db)
    {
        this.db = db;
        CoursesRepository = new SqlRepository<Course>(db);
        UserCoursesRepository = new SqlRepository<UserCourse>(db);
    }
// Etc, etc
}

The CoursesRepository and UserCoursesRepository are both private properties of the service class that are of type IRepository (just a simple generic repository interface).

SqlRepository Code:

public class SqlRepository<T> : IRepository<T> where T : class
    {
        DataContext db;
        public SqlRepository(DataContext db)
        {
            this.db = db;
        }

        #region IRepository<T> Members

        public IQueryable<T> Query
        {
            get { return db.GetTable<T>(); }
        }

        public List<T> FetchAll()
        {
            return Query.ToList();
        }

        public void Add(T entity)
        {
            db.GetTable<T>().InsertOnSubmit(entity);
        }

        public void Delete(T entity)
        {
            db.GetTable<T>().DeleteOnSubmit(entity);
        }

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

        #endregion
    }

The two methods for adding and deleting UserCourses are:

Service Methods for Adding and Deleting UserCourses:

public void AddUserCourse(int courseId)
{
    UserCourse uc = new UserCourse();
    uc.IdCourse = courseId;
    uc.IdUser = UserId;
    uc.DateCreated = DateTime.Now;
    uc.DateAmended = DateTime.Now;
    uc.Role = "Pupil";
    uc.CourseNotes = string.Empty;
    uc.ActiveStepIndex = 0;
    UserCoursesRepository.Add(uc);
    UserCoursesRepository.Save();
}

public void DeleteUserCourse(int courseId)
{
    var uc = (UserCoursesRepository.Query.Where(x => x.IdUser == UserId && x.IdCourse == courseId)).Single();
    UserCoursesRepository.Delete(uc);
    UserCoursesRepository.Save();

}

Ajax

I am using Ajax via Ajax.BeginForm

I don't think that is relevant.

ASP.NET MVC 3

I am using mvc3, but don't think that is relevant: the errors are related to model code.

The problem only occurs intermitently, for example, adding a row then deleting it. If I stop the dev server and restart, the added row is there and I can delete it no problem.

Your code does not show what the link is between the Added Row and the Delete/Update. Your Add() doesn't return an object reference.

I'm thinking you are missing a Refresh (ie reload the object after Insert). Is your IdCourse also the PK in the Table?

Edit:

Further research has revealed that the problem is with the dependency injection.

The problem was related to how Dependency Injection manages the items it creates. Google for 'lifecycle management' in IoC or DI. Essentially, DI cached a DataContext constructor argument that I injected.

For a way to solve this using the Factory Pattern, see this thread: Ninject caching an injected DataContext? Lifecycle Management?

The accepted answer solved it all.