Can't get Database to update using Repository Pattern and LINQ to SQL.

Question

My project has the following setup

Controller -> Service (for validation) -> Repository (for LINQ) -> dbml -> Database

Controller

''# <AcceptVerbs(HttpVerbs.Post)>  -  hiding this line so that code formatting looks proper in SO.
Function Edit(ByVal user As Domain.User, ByVal id As Integer) As ActionResult

    If ModelState.IsValid Then

        If Not String.IsNullOrEmpty(user.UserName) AndAlso _
            Not String.IsNullOrEmpty(user.WebSite) AndAlso _
            Not String.IsNullOrEmpty(user.Email) AndAlso _
            Not String.IsNullOrEmpty(user.About) AndAlso _
            Not user.Region Is Nothing AndAlso _
            Not user.BirthDate Is Nothing AndAlso _
            Not user.isProfileComplete = True Then

            user.isProfileComplete = True
            user.Reputation = user.Reputation + 10

            UserService.UpdateUser(user)

        Else
            UserService.UpdateUser(user)
        End If

        UserService.SubmitChanges()

        Return RedirectToAction("Details", "Users", New With {.id = id, .slug = user.UserName})
    Else
        Return View(user)
    End If
End Function

Service

    Public Sub UpdateUser(ByVal user As User) Implements IUserService.UpdateUser
        _UserRepository.UpdateUser(user)
    End Sub

    Public Sub SubmitChanges() Implements IUserService.SubmitChanges
        _UserRepository.SubmitChanges()
    End Sub

Repository

    Public Sub UpdateUser(ByVal user As User) Implements IUserRepository.UpdateUser
        Dim _user = (From u In dc.Users
            Where u.ID = user.ID
            Select u).Single
        _user = user
    End Sub

    Public Sub SubmitChanges() Implements IUserRepository.SubmitChanges
        dc.SubmitChanges()
    End Sub

The problem is that it's not getting updated. I must be doing something quite stupid here. Any advice will be greatly appreciated.

Answer 1

Public Sub UpdateUser(ByVal user As User) Implements IUserRepository.UpdateUser 
    Dim _user = (From u In dc.Users 
        Where u.ID = user.ID 
        Select u).Single 
    _user = user 
End Sub 

In this method, you are getting the User from the database. That User instance is being tracked by Linq2Sql in terms of knowing if the properties have changed. So, after the linq statement _user is known by the data context and can be tracked. When you assign the user parameter to the object (_user = user), you lose the reference to the tracked object, and replace it with an untracked object. You can either individually update the _user variable that contains information from the database, or attempt to attach the user parameter to the data context and use that directly (dc.Users.Attach(user)). Attaching can be complex because if you don't have the same datacontext instance than it won't attach properly. Below is how I would do it.

Public Sub UpdateUser(ByVal user As User) Implements IUserRepository.UpdateUser 
    Dim _user = (From u In dc.Users 
        Where u.ID = user.ID 
        Select u).Single 
    With _user
        .Name = user.Name
        .Email = user.Email
    End With
End Sub