C# Modify List item while in a foreach loop

Question

as the title say's I am attempting to get to edit a list item inside of a foreach loop. I know it is not possible, so I am asking for solutions to work around this.

As this is taking data directly from a MySQL server, it need's to be done inside of this loop and with the correct indexes. I attempted to use a second list and change the data outside of the foreach loop, but that did not account for the index positions of the items.

foreach (string friend in friendsOnline)
{
 query = "SELECT `username` FROM `accounts` WHERE email = '" + friend + "'";
 
 cmd = new MySqlCommand(query, conn);
 rdr = cmd.ExecuteReader();
 
 while (rdr.Read())
 {
 for (int i = 0; i<friendsOnline.Count; i++)
 {
 if (friendsOnline[i].Contains(friend))
 {
 friendsOnline[i] = rdr[0].ToString();
 }
 }
 }
}

• Steeeve: What exactly are you trying to achieve? Why not use a for-loop instead of foreach?
• Joel Coehoorn: Holy sql injection vulnerability, Batman.
• Luke: @JoelCoehoorn To be honest, I'm not really bothered if user's decide to add a random email address to their friend's list.
• Joel Coehoorn: @Luke You can use this to do a lot more than just add random emails addresses... for example, I could absolutely use the exact code in your question to drop every table your database.

Answer 1

As I can see, you have emails in the friendsOnline:

string[] friendsOnline = new[] {
 "[email protected]",
 "[email protected]",
}; 

and you want to change these emails into names:

string[] friendsOnline = new[] {
 "Frodo Baggins",
 "Sauron the Black",
}; 

First of all, I suggest executing query just once: SQL is too slow to be run in a loop; instead you can build a dictionary which eMail corresponds to which indexes:

using System.Linq;
...
// eMail - indexes correspondence
Dictionary<string, int[]> dict = friendsOnline
 .Select((name, index) => (name : name, index : index))
 .GroupBy(pair => pair.name, pair => pair.index, StringComparer.OrdinalIgnoreCase)
 .ToDictionary(group => group.Key, group => group.ToArray());
// We take all users (no WHERE), but once
// We read both username and email
string sql = 
 @"SELECT `username`,
 `email` 
 FROM `accounts`";
 
//DONE: using - we should Dispose IDisposable Command and Reader
using cmd = new MySqlCommand(sql, conn);
using rdr = cmd.ExecuteReader();
while (rdr.Read()) {
 string name = Convert.ToString(rdr[0]);
 string mail = Convert.ToString(rdr[1]); 
 // If we have correspondent indexes, we change friendsOnline
 if (dict.TryGetValue(mail, out var indexes))
 foreach (var index in indexes)
 friendsOnline[index] = name;
}

• Luke: Hi Dmitry, this does not work in this instance, as the email is pulled beforehand from another MySQL Table. As well as, rdr[1] does not exist, since it is only searching for the username, it will only return rdr index 0.
• Dmitrii Bychenko: @Luke: As I can see from the query in the question, email is a field of account query. I've rewritten the query: I've dropped WHERE but add email to SELECT. Note, that now query have 2 fields, that's why rdr[1] exists (and equals to email)
• Luke: I see yes, however Dictionary throw's an error with "using the generic type dictionary requires 2 type arguments" I have not used Dictionary before, do you know the issue here?
• Joel Coehoorn: @Luke If the emails also came from a MySql table, you should have pulled down the names in the same query.
• Joel Coehoorn: Modifying the original query is exactly what I'm suggesting. Bring down the extra field earlier in your process, and do a little extra work there in how you retrieve and save the data if needed. Two different tables is irrelevant. That's what JOINs are for. These changes will easily speed up this process by at least an order of magnitude.