Linq to Sql - How to update an object using a repository pattern?

Question

There is tons of information on this, but even after reading for hours and hours I can't seem to get this to work the way I want.

I'm trying to update a User object by passing in a User object and generically comparing changes to a User object I pull out of the database. I always end up getting the NotSupportedException when using this method:

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.

Here is how I am trying to do it:

    public void SaveUser(User User)
    {
        using (DataContext dataContext = new DataContext(WebConfigurationManager.ConnectionStrings["database"].ConnectionString))
        {
            // New user
            if (User.UserID == 0)
            {
                dataContext.Users.InsertOnSubmit(User);
            }
            // Existing user
            else
            {
                User dbUser = dataContext.Users.Single(u => u.UserID.Equals(User.UserID));
                Type t = dbUser.GetType();
                foreach (PropertyInfo p in t.GetProperties())
                {
                    if (p.CanWrite & p.GetValue(dbUser, null) != p.GetValue(User, null))
                    {
                        p.SetValue(dbUser, p.GetValue(User, null), null);
                    }
                }
                //dataContext.Refresh(RefreshMode.KeepCurrentValues, dbUser);
            }
            dataContext.SubmitChanges();
        }
    }

The commented out line I tried uncommented too, but it was no help.

If I comment out the foreach() loop and add a line like dbUser.UserName = "Cheese"; it will update the User's name in the database fine. That leads me to believe it is something with how the foreach() loop changing the dbUser object that causes this to fail.

When I debug the dbUser object, it appears to correctly acquire all the changes from the User object that was passed as an argument.

I also did some reading on optimistic concurrency and added a column to the table of data type timestamp, but that didn't seem to have any effect either.

What exactly am I doing wrong here?

How can I get this to generically detect what has changed and correctly persist the changes to the database?

Answer 1

My guess is there's a foreign key relation that you are trying to copy over that was not initially loaded (because of lazy-loading) During the copying, it's attempting to load it, but the DataContext has already been disposed.

I've been working on a similar problem. I ended up using AutoMapper to handle copying the properties for me. I have configured AutoMapper to ignore the primary key field as well as any relations. Something like:

public void Update(User user)
{
     using (var db = new DataContext(...))
     {
         var userFromDb = db.Users.Where(x => x.Id == user.Id).Single();
         AutoMapper.Mapper.Map(user, userFromDb);
         db.SubmitChanges();
    }
}

My automapper configuration is something like

AutoMapper.Mapper.Create<User, User>().ForMember(dest => dest.Id, opt => opt.Ignore())
                                      .ForMember(dest => dest.SomeRelation, opt => opt.Ignore());

You can find AutoMapper here: http://automapper.codeplex.com/

Answer 2

I keep my repo pretty lean, it's only job is to interact with the database. I build a Service layer on top of the repo that does a little more work

public class EventRepository : IEventRepository
{

    private DBDataContext dc;
    public EventRepository()
    {
        dc = new DBDataContext();
    }

    public void Create(Event @event)
    {
        dc.Events.InsertOnSubmit(@event);
    }

    public System.Linq.IQueryable<Event> Read()
    {
        object events = (from e in dc.Eventse);
        return events.AsQueryable;
    }

    public void SubmitChanges()
    {
        dc.SubmitChanges();
    }

}

Then the corresponding call from the service layer looks like this

public void AddEvent(Event @event)
{
    _EventRepository.Create(@event);
}

public void SubmitChanges()
{
    _EventRepository.SubmitChanges();
}

And I call it from my controller.

// AutoMapper will allow us to map the ViewModel with the DomainModel
Mapper.CreateMap<Domain.ViewModels.EventsAddViewModel, Domain.Event>();
object @event = Mapper.Map<Domain.ViewModels.EventsAddViewModel, Domain.Event>(eventToAdd);

// Add the event to the database
EventService.AddEvent(@event);
EventService.SubmitChanges();