Removing Items in a List While Iterating Through It with For Each Loop

Question

I have a list named NeededList I need to check each item in this list to see if it exists in my database. If it does exist in the database I need to remove it from the list. But I can't change the list while I'm iterating through it. How can I make this work?

Here is my code so far:

For Each Needed In NeededList
        Dim Ticker = Needed.Split("-")(0).Trim()
        Dim Year = Needed.Split("-")(1).Trim()
        Dim Period = Needed.Split("-")(2).Trim()
        Dim Table = Needed.Split("-")(3).Trim()
        Dim dr As OleDbDataReader
        Dim cmd2 As New OleDb.OleDbCommand("SELECT * FROM " & Table & " WHERE Ticker = ? AND [Year] = ? AND Period = ?", con)
        cmd2.Parameters.AddWithValue("?", Ticker)
        cmd2.Parameters.AddWithValue("?", Year)
        cmd2.Parameters.AddWithValue("?", Period)
        dr = cmd2.ExecuteReader
        If dr.HasRows Then
            NeededList.Remove(Needed)
        End If
Next

Answer 1

No you can't do that using For Each. The reason is explained in this answer where the author tries to pinpoint the logical problems and the practical inefficiencies that such implementation could trigger.

But you can do that using the old fashioned for .. loop.
The trick is to start from the end and looping backwards.

For x = NeededList.Count - 1 to 0 Step -1
    ' Get the element to evaluate....
    Dim Needed = NeededList(x)
    .....
    If dr.HasRows Then
        NeededList.RemoveAt(x)
    End If
Next

You need to approach the loop in this way because you don't want to skip an element because the current one has just been deleted.

For example, in a forward loop, suppose that you remove the fourth element in the collection, after that, the fifth element slips down to the fourth position. But then the indexer goes up to 5. In this way, the previous fifth element (now in the fourth position) is never evaluated by the logic inside the loop.
Of course you could try to change the value of the indexer but this ends always in bad code and bugs waiting to happen.