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 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):

It seems to be a problem 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).

Sql Respoitory 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.