"Attach or Add an entity that is not new...loaded from another DataContext. This is not supported." Problem on insert of linker object.

Question

Similar error as other questions, but not quite the same, I am not trying to attach anything.

What I am trying to do is insert a new row into a linking table, specifically UserAccomplishment. Relations are set in LINQ to User and Accomplishment Tables.

I have a generic insert function:

Public Function insertRow(ByVal entity As ImplementationType) As Boolean
        If entity IsNot Nothing Then

            Dim lcfdatacontext As New LCFDataContext()
            Try
                lcfdatacontext.GetTable(Of ImplementationType)().InsertOnSubmit(entity)

                lcfdatacontext.SubmitChanges()
                lcfdatacontext.Dispose()
                Return True
            Catch ex As Exception
                Return False
            End Try
        Else
            Return False
        End If
End Function

If you try and give UserAccomplishment the two appropriate objects this will naturally crap out if either the User or Accomplishment already exist. It only works when both user and accomplishment don't exist. I expected this behavior. What does work is simply giving the userAccomplishment object a user.id and accomplishment.id and populating the rest of the fields. This works but is kind of awkward to use in my app, it would be much easier to simply pass in both objects and have it work out what already exists and what doesn't. Okay so I made the following (please ignore the fact that this is horribly inefficient because I know it is):

Public Class UserAccomplishmentDao
    Inherits EntityDao(Of UserAccomplishment)

    Public Function insertLinkerObjectRow(ByVal userAccomplishment As UserAccomplishment)

        Dim insertSuccess As Boolean = False

        If Not userAccomplishment Is Nothing Then

            Dim userDao As New UserDao()
            Dim accomplishmentDao As New AccomplishmentDao()
            Dim user As New User()
            Dim accomplishment As New Accomplishment()


            'see if either object already exists in db'
            user = userDao.getOneByValueOfProperty("Id", userAccomplishment.User.Id)
            accomplishment = accomplishmentDao.getOneByValueOfProperty("Id", userAccomplishment.Accomplishment.Id)


            If user Is Nothing And accomplishment Is Nothing Then
                'neither the user or the accomplishment exist, both are new so insert them both, typical insert'
                insertSuccess = Me.insertRow(userAccomplishment)

            ElseIf user Is Nothing And Not accomplishment Is Nothing Then
                'user is new, accomplishment is not new, so just insert the user, and the relation in userAccomplishment'
                Dim userWithExistingAccomplishment As New UserAccomplishment(userAccomplishment.User, userAccomplishment.Accomplishment.Id, userAccomplishment.LastUpdatedBy)
                insertSuccess = Me.insertRow(userWithExistingAccomplishment)
            ElseIf Not user Is Nothing And accomplishment Is Nothing Then
                'user is not new, accomplishment is new, so just insert the accomplishment, and the relation in userAccomplishment'
                Dim existingUserWithAccomplishment As New UserAccomplishment(userAccomplishment.UserId, userAccomplishment.Accomplishment, userAccomplishment.LastUpdatedBy)
                insertSuccess = Me.insertRow(existingUserWithAccomplishment)
            Else
                'both are not new, just add the relation'
                Dim userAccomplishmentBothExist As New UserAccomplishment(userAccomplishment.User.Id, userAccomplishment.Accomplishment.Id, userAccomplishment.LastUpdatedBy)
                insertSuccess = Me.insertRow(userAccomplishmentBothExist)
            End If

        End If

        Return insertSuccess

    End Function

End Class

Alright, here I basically check if the supplied user and accomplishment already exists in the db, and if so call an appropriate constructor that will leave whatever already exists empty, but supply the rest of the information so the insert can succeed.

However, upon trying an insert:

Dim result As Boolean = Me.userAccomplishmentDao.insertLinkerObjectRow(userAccomplishment)

In which the user already exists, but the accomplishment does not (the 99% typical scenario) I get the error:

"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."

I have debugged this multiple times now and am not sure why this is occuring, if either User or Accomplishment exist, I am not including it in the final object to try to insert. So nothing appears to be attempted to be added. Even in debug, upon insert, the object was set to empty. So the accomplishment is new and the user is empty.

1) Why is it still saying that and how can I fix it ..using my current structure

2) Pre-emptive 'use repository pattern answers' - I know this way kind of sucks in general and I should be using the repository pattern. However, I can't use that in the current project because I don't have time to refactor that due to my non existence knowledge of it and time constraints. The usage of the app is going to so small that the inefficient use of datacontext's and what have you won't matter so much. I can refactor it once it's up and running, but for now I just need to 'push through' with my current structure.

Edit: I also just tested this when having both already exists, and only insert each object's IDs into the table, that works. So I guess I could manually insert whichever object doesn't exist as a single insert, then put the ids only into the linking table, but I still don't know why when one object exists, and I make it empty, it doens't work.

Answer 1

First and foremost, I think you're working too hard, but it's understandable.

Let me see if understand what you're trying to do: You want to update an association table that tracks Users and Accomplishments. Regardless of what exists beforehand, by the time you are finished, you want a valid UserAccomplishment record, complete with fulfilled and valid relations. Let's start with:

public void AddUserAccomplishment(user User, accomplishment Accomplishment)
{
    using (LCFDataContext dc = new LCFDataContext())
    {
        // Substitute <UserName> for any other unique field in the record.
        var tempUser = dc.Users
            .Where(a => a.UserName.ToLower() == user.UserName.ToLower())
            .SingleOrDefault();

        // Using SingleOrDefault() creates an empty User entity object if 
        // a matching record can't be found, which means the Id field will
        // be equal to zero.
        if (tempUser.Id == 0)
        {
            // Populate the empty tempUser entity object
            tempUser.UserName = user.UserName;
               .
               .
               .
            dc.Users.InsertOnSubmit(tempUser);
        }

        // Do the same thing for the Accomplishment.
        var tempAccomplishment = dc.Accomplishments
            .Where(a => a.Title.ToLower() == accomplishment.Title.ToLower())
            .SingleOrDefault();

        if (tempAccomplishment.Id == 0)
        {
            // Populate the empty tempAccomplishment entity object
            tempAccomplishment.Title = accomplishment.Title;
               .
               .
               .
            dc.Accomplishments.InsertOnSubmit(tempAccomplishment);
        }

        // Now, you need to make sure that the UserAccomplishment entity object
        // doesn't already exist.
        if (dc.UserAccomplishments
            .Where(a => a.User.Id == tempUser.Id
                && a.Accomplishment.Id == tempAccomplishment.Id)
            .Count == 0)
        {
            UserAccomplishment userAccomplishment = new UserAccomplishment();

            userAccomplishment.User = tempUser;
            userAccomplishment.Accomplishment = tempAccomplishment;
            dc.UserAccomplishments.InsertOnSubmit(userAccomplishment);
        }
        else
        {
            // The UserAccomplishment record already exists, so throw an
            // exception or display an error message.
        }

        dc.SubmitChanges();       
    }
}

Let me know if this helps...