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.
