select specific rows from datatable using where clause C#

Question

I have a datatable dt_Customers which contains Customer_ID, Email and some other data. Some of the emails are duplicates in this data table, means they are repeating 2,3 or more times.

I have selected distinct emails in a list by using:

List<string> Email = dt_Customers.AsEnumerable().Select(r => r.Field<string>("Email")).Distinct().ToList(); 

Against these selected Emails which are in the list now. Is it possible to select Customer_ID from the data table?

Or I have to write SQL query which will fetch the data from SQL Server database against selected email Ids in the list.

Answer 1

Or I have to write SQL query which will fetch the data from SQL Server database against selected email Ids in the list.

No you don't have to fetch them again from the Database, you already have them in your DataTable. You can use the following query which would check the Emails list against the DataTable in memory, like:

List<int> customerIDs = dt_Customers.AsEnumerable()
                            .Where(r => Email.Contains(r.Field<string>("Email")))
                            .Select(r => r.Field<int>("Customer_ID"))
                            .ToList();

Answer 2

Unfortunately there is no DistinctBy method in LINQ, here is workaround and custom Extension method

var customerIds = dt_Customers.AsEnumerable()
                              .GroupBy(r => r.Field<string>("Email"))
                              .Select(g => g.First().Field<int>("Customer_ID"))
                              .ToList();

Answer 3

You can group by Email then select all Customer_IDs from that group:

var emails = dt_Customers.AsEnumerable().GroupBy(dr => dr.Field<string>("Email"));
foreach (var emailGroup in emails)
{
    var email = emailGroup.Key;
    var ids = emailGroup.Select(dr => dr.Field<int>("Customer_ID "]));
}