Entity Query Not Updating in For Each Correctly

I have the following code:

  ' Now retabulate the current occupancy.
        For Each row In assignments
            Try
                Dim assignment As Integer = row.room
                Dim student_id As String = row.id
                Dim update_occupancy = (From p In dbCon开发者_运维技巧text.Beds _
                                        Where p.occupant = 0 _
                                        Where p.room = assignment _
                                        Select p).FirstOrDefault
                update_occupancy.occupant = student_id

            Catch ex As Exception

            End Try

        Next
        dbContext.SaveChanges()

In this segment I am iterating through the current assignments and when the bed is not occupied (=0) and the room is equal to the current student, I insert the student into that room.

Problem is that update_occupancy seems to always be staying the same. For example, I might have four beds (ID 101, 102, 103, 104), the first time the student should be assigned to 101, the next student to 102, the next to 103, and so on. But it is just overwriting the first one over and over again. I can't put a dbContext.SaveChanges() inside of the For Each, it breaks. Thoughts?

---

That is an obvious problem. You are asking your database for a record without occupant and assigning a new occupant but without saving changes you are doing this in loop so the query must always return the same row.

Your exception happens only if assignments is enumerated result of the query. If you want to avoid that exception simply call ToList on the query.

Still I would think about the algorithm and try to load beds upfront. This requires one query to get assignments and one query + one update for each bed. If your assignemnts return 1.000 rows you will have to do 1.000 queries for beds. You will not avoid 1.000 updates because EF doesn't support command batching. There is also decission about what is a transaction in your alogrithm. Is it update of single bed or is it processing of all loaded assignments? Based on this decission you can also think about your algorithm.

Btw. if assignments is a query it can be most probably rewritten to some join with beds so you will not need to do sub queries in loop.