指定的LINQ表达式包含对与不同上下文关联的查询的引用

Question

我知道在stackoverflow上有类似的问题 - 我仔细看了看它们，认为我的问题有点类似，但一直没能找到解决方案看着其他任何问题/答案。 我得到试图执行下面的代码时出现错误：

Private Sub btnReserve_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReserve.Click 
    ' Check that the room is still available. 
    Dim dbCheckOccupants As New pbu_housingEntities 
    Dim hall As String = CStr(Session("hall")) 
    Dim room As String = CStr(Session("room")) 
    Dim checkOccupants = From p In dbCheckOccupants.Rooms _ 
         Let building_id = p.Building1.id _ 
         Where p.building_name = hall _ 
         Where p.room1 = room _ 
         Select p.current_occupancy, p.max_occupancy, p.id, building_id 
    If checkOccupants.First.current_occupancy >= checkOccupants.First.max_occupancy Then 
     ' If it isn't available, let student know. 
     lblResult.Text = "Sorry, this room is now fully occupied. Please choose another room." 
    Else 
     ' If it is available, add the student to the room. 
     Dim AddOccupant As New pbu_housingEntities 
     Dim Occupant As New Resident 
     Dim gender As String = CStr(Session("gender")) 
     Dim person_name As String = CStr(Session("person_name")) 
     Dim class_level As String = CStr(Session("class_level")) 
     Dim semester As String = CStr(Session("term")) 
     Dim people_code_id As String = CStr(Session("people_code_id")) 
     Dim first_name As String = CStr(Session("first_name")) 
     Dim last_name As String = CStr(Session("last_name")) 
     Dim building_id As String = checkOccupants.First.building_id 
     Dim room_id As String = checkOccupants.First.id 
     Occupant.building = building_id 
     Occupant.room = room_id 
     Occupant.gender = gender 
     Occupant.person_name = person_name 
     Occupant.class_level = class_level 
     Occupant.semester = semester 
     Occupant.people_code_id = people_code_id 
     Occupant.create_date = Date.Now 
     Occupant.first_name = first_name 
     Occupant.last_name = last_name 
     AddOccupant.Residents.AddObject(Occupant) 
     AddOccupant.SaveChanges() 
     ' Increment the number of occupants in the room. 
     Dim UpdateRoomOccupancy As New pbu_housingEntities 
     Dim UpdateOccupancy = (From p In UpdateRoomOccupancy.Rooms _ 
           Where p.building_name = hall _ 
           Where p.room1 = room _ 
           Select p).First 
     UpdateOccupancy.current_occupancy = UpdateOccupancy.current_occupancy + 1 
     UpdateRoomOccupancy.SaveChanges() 
     ' Add the student to a bed. 
     Dim AddBed As New pbu_housingEntities 
     Dim UpdateBed = From p In AddBed.Beds _ 
         Where p.building = building_id _ 
         Where p.room = room_id _ 
         Where p.occupant = "" _ 
         Select p 

     ' Get the student's ID from the residency table. 
     Dim GetID = From p In AddBed.Residents _ 
        Where p.people_code_id = people_code_id _ 
        Order By p.id Descending _ 
        Select p 

     Dim myID As String = GetID.First.id.ToString 

     UpdateBed.First.occupant = myID 
     AddBed.SaveChanges() 
     lblResult.Text = "Success! You have successfully requested residency in this room!" 
    End If 
End Sub 

它赶上在这条线的错误：

Dim myID As String = GetID.First.id.ToString 

至于我，我没有使用多说背景？

Answer 1

我不明白为什么你可以在你指定的行上得到异常，但代码在我看来有两个缺陷：你在这个方法中实例化4个不同的上下文（尽管我没有看到你混合了来自这些背景），你不会处理它们中的任何一个。 （数据库上下文引用外部资源（数据库连接），其中明确地配置是一个很好的做法。）

尝试重写它，这样你只有一个上下文dbContext，SaveChanges只调用一次，这种单一的背景布置在方法（通过Using块）的端部：

Private Sub btnReserve_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReserve.Click 
    ' Check that the room is still available. 
    Using (dbContext As pbu_housingEntities = New pbu_housingEntities) 
     ' Check that the room is still available. 
     Dim hall As String = CStr(Session("hall")) 
     Dim room As String = CStr(Session("room")) 
     Dim checkOccupants = From p In dbContext.Rooms _ 
          Let building_id = p.Building1.id _ 
          Where p.building_name = hall _ 
          Where p.room1 = room _ 
          Select p.current_occupancy, p.max_occupancy, p.id, building_id 
     If checkOccupants.First.current_occupancy >= checkOccupants.First.max_occupancy Then 
      ' If it isn't available, let student know. 
      lblResult.Text = "Sorry, this room is now fully occupied. Please choose another room." 
     Else 
      ' If it is available, add the student to the room. 
      Dim Occupant As New Resident 
      Dim gender As String = CStr(Session("gender")) 
      Dim person_name As String = CStr(Session("person_name")) 
      Dim class_level As String = CStr(Session("class_level")) 
      Dim semester As String = CStr(Session("term")) 
      Dim people_code_id As String = CStr(Session("people_code_id")) 
      Dim first_name As String = CStr(Session("first_name")) 
      Dim last_name As String = CStr(Session("last_name")) 
      Dim building_id As String = checkOccupants.First.building_id 
      Dim room_id As String = checkOccupants.First.id 
      Occupant.building = building_id 
      Occupant.room = room_id 
      Occupant.gender = gender 
      Occupant.person_name = person_name 
      Occupant.class_level = class_level 
      Occupant.semester = semester 
      Occupant.people_code_id = people_code_id 
      Occupant.create_date = Date.Now 
      Occupant.first_name = first_name 
      Occupant.last_name = last_name 
      dbContext.Residents.AddObject(Occupant) 

      ' Increment the number of occupants in the room. 
      Dim UpdateOccupancy = (From p In dbContext.Rooms _ 
            Where p.building_name = hall _ 
            Where p.room1 = room _ 
            Select p).First 
      UpdateOccupancy.current_occupancy = UpdateOccupancy.current_occupancy + 1 

      ' Add the student to a bed. 
      Dim UpdateBed = From p In dbContext.Beds _ 
          Where p.building = building_id _ 
          Where p.room = room_id _ 
          Where p.occupant = "" _ 
          Select p 

      ' Get the student's ID from the residency table. 
      Dim GetID = From p In dbContext.Residents _ 
         Where p.people_code_id = people_code_id _ 
         Order By p.id Descending _ 
         Select p 

      Dim myID As String = GetID.First.id.ToString 

      UpdateBed.First.occupant = myID 

      dbContext.SaveChanges() 

      lblResult.Text = "Success! You have successfully requested residency in this room!" 
     End If 
    End Using 
End Sub 

随着一点点运气误差消失，代码又是做以前一样。

编辑

我只是快速添加通用注释：也许你有你需要一个新的环境，每次查询，插入或删除操作（错误的）想法。情况并非如此，实际上在不同环境下混合对象的风险很高。在大多数情况下，你可以按照你的方法的标准模式，像这样：

' Create one single context 
Using (dbContext As pbu_housingEntities = New pbu_housingEntities) 

    ' Use here this dbContext for as many queries, Adds, Deletes and Changes 
    ' as you need and like 

    ' Save ALL Adds, Deletes and Changes you have done in this block 
    dbContext.SaveChanges() 

End Using 

同样重要的是要知道的SaveChanges将执行一个数据库事务，所以这是一个全有或全无操作。我可以想象，这在你的方法中也很重要。例如，您在Increment the number of occupants in the room.和Add the student to a bed.中评论过的两个操作不应该同时发生，也不应该发生，以避免数据库中的不一致状态？使用不同的上下文可能会导致第一次操作成功，但第二次操作失败，从而使数据在数据库中处于不一致的状态。

Answer 2

您能否在一次通话中获得ID？

Dim MyID = (From p In AddBed.Residents _ 
        Where p.people_code_id = people_code_id _ 
        Order By p.id Descending _ 
        Select p.id).SingleOrDefault() 

您正在调用上下文的多个实例。尝试使用块作为其他答案建议