“附加或添加不是新的实体...从另一个 DataContext 加载。这是不支持的。”插入链接器对象时出现问题

Question

与其他问题类似的错误，但不完全相同，我不想附加任何内容。

我想做的是将新行插入到链接表中，特别是 UserAccomplishment 中。关系在 LINQ to User 和 Accomplishment 表中设置。

我有一个通用插入函数：

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

如果您尝试为 UserAccomplishment 提供两个适当的对象，如果 User 或 Accomplishment 已经存在，这自然会失败。它仅在用户和成就都不存在时才有效。我预料到了这种行为。有效的方法是简单地为 userAccomplishment 对象提供 user.id 和 Achievement.id 并填充其余字段。这可行，但在我的应用程序中使用起来有点尴尬，简单地传入两个对象并让它计算出已经存在的和不存在的会容易得多。好的，所以我做了以下操作（请忽略这个效率非常低的事实，因为我知道它是这样）：

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

好吧，在这里我基本上检查提供的用户和成就是否已存在于数据库中，如果存在，则调用一个适当的构造函数将已存在的内容保留为空，但提供其余信息以便插入成功。

但是，在尝试插入时：

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

其中用户已经存在，但成就不存在（99% 的典型情况），我收到错误：

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

我现在已经多次调试此问题，并且不确定为什么会发生这种情况，如果用户或成就存在，我没有将其包含在尝试插入的最终对象中。因此似乎没有尝试添加任何内容。即使在调试中，在插入时，该对象也被设置为空。所以成就是新的，用户是空的。

1）为什么它仍然这么说，我该如何修复它..使用我当前的结构

2）先发制人的“使用存储库模式答案” - 我知道这种方式通常很糟糕，我应该使用存储库模式。但是，我无法在当前项目中使用它，因为由于我不存在知识和时间限制，我没有时间重构它。该应用程序的使用量将会如此之小，以至于数据上下文的低效使用以及您所拥有的内容不再那么重要。一旦它启动并运行，我就可以对其进行重构，但现在我只需要“推进”我当前的结构。

编辑：我也刚刚在两者都存在时测试了这一点，并且仅将每个对象的 ID 插入到表中，这有效。所以我想我可以手动插入不存在的对象作为单个插入，然后仅将 id 放入链接表中，但我仍然不知道为什么当一个对象存在时，我将其设为空，它确实如此不工作。

Answer 1

首先，我认为你工作得太辛苦了，但这是可以理解的。

让我看看是否明白您要做什么：您想要更新跟踪用户和成就的关联表。无论预先存在什么，当您完成时，您都需要一个有效的 UserAccomplishment 记录，其中包含已履行且有效的关系。让我们开始：

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();       
    }
}

让我知道这是否有帮助......