CODEWITHSUNDEEP
c#loopsdatatable
Dave Smash
Dave Smash

Reputation: 3001

nested foreach loops returns only distinct

I have a database where each email address should belong to a unique customer, but I have many duplicates. I used a sql query to list customer ID, email address pairs for each occurrence where there is more than one customer ID mapped to a single email address. The result looks something like this (addresses changed to protect the innocent)

Customer ID   Email
101233        [email protected]
108993        [email protected]
113224        [email protected]
89223         [email protected]
188223        [email protected]

In c#, I populate this into a DataTable called dt with 722 rows. I use this to make a second DataTable called distinctTbl with 344 rows containing only the distinct email addresses using this:

DataTable distinctTbl = dt.AsDataView().ToTable(true, "Email");

I am trying to use nested loops to make a list of integers (Customer ID) for each email address:

foreach (DataRow dr in distinctTbl.Rows)
{
    // for each email address:
    List<int> idNums = new List<int>();

    foreach (DataRow myRow in dt.Rows) 
    {
        // for every customerID / email pair in the original table
        if (myRow["Email"] == dr["Email"])
        {
            idNums.Add((int)myRow["CustomerID"]);
        }
    }

    // Do something with the List<int> before exiting outside loop
}

When I run this code, each List of integers contains exactly one value. The value is correct, but there should be a minimum of two for each email address. I have done enough debugging to figure out that it always correctly identifies the first one, but skips any subsequent matches. I'm sure I'm missing something obvious, but does somebody see what is happening?

Upvotes: 0

Views: 1060

Answers (2)

Oblivious Sage
Oblivious Sage

Reputation: 3395

Ditch the foreach loops.

You can use Linq to get the information you're looking for more easily.

Dictionary<string, List<int>> emailIDs =
    dt.Rows.OfType<DataRow>()
           .GroupBy(row => (string)row["Email"])
           .ToDictionary(grp => grp.Key,
                         grp => grp.Select(row => (int)row["CustomerID"]).ToList());

Upvotes: 2

tinstaafl
tinstaafl

Reputation: 6948

One quick and easy solution is to use a Dictionary<string,List<int>> instead of a list:

    Dictionary<string, List<int>> idNums = new Dictionary<string, List<int>>();
    foreach (DataRow myRow in dt.Rows)
    {
        string email = myRow["Email"].ToString()
        if (idNums.ContainsKey(email))
        {
            idNums[email].Add((int)myRow["CustomerID"]);
        }
        else
        {
            idNums.Add(email, new List<int> { (int)myRow["CustomerID"] });
        }
    }

Now idNums will contain the list of id's associated with each email.

Upvotes: 2

Related Questions