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.

Habib · Accepted Answer · 2014-01-28 14:50:51Z

2

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

answered Jan 28, 2014 at 14:50

Habib

224k30 gold badges420 silver badges446 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:49:24Z

1

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

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Jan 28, 2014 at 14:52

Arsen Mkrtchyan

50.9k33 gold badges155 silver badges187 bronze badges

Comments

Robert Fricke · Accepted Answer · 2014-01-28 14:58:15Z

0

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 "]));
}

edited Jan 28, 2014 at 14:58

answered Jan 28, 2014 at 14:51

Robert Fricke

3,6531 gold badge23 silver badges35 bronze badges

Collectives™ on Stack Overflow

select specific rows from datatable using where clause C#

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related