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

What exactly are you trying to achieve? Why not use a for-loop instead of foreach? — Steeeve
– Steeeve, Commented Nov 13, 2021 at 20:06
@JoelCoehoorn To be honest, I'm not really bothered if user's decide to add a random email address to their friend's list. — Luke
– Luke, Commented Nov 13, 2021 at 23:30
@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. — Joel Coehoorn
– Joel Coehoorn, Commented Nov 15, 2021 at 14:50

Dmitrii Bychenko · Accepted Answer · 2021-11-13 21:56:49Z

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

edited Nov 13, 2021 at 21:56

answered Nov 13, 2021 at 20:12

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Luke Over a year ago

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 Over a year ago

@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 Over a year ago

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 Over a year ago

@Luke If the emails also came from a MySql table, you should have pulled down the names in the same query.

Joel Coehoorn Over a year ago

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.

|

Collectives™ on Stack Overflow

C# Modify List item while in a foreach loop

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related