Create a DataTable containing only unique values with Linq in C#

user_14705210 0
Viewed 105

I have a DataTable dt_Candidates

      Candidate      |   First Name   |   Last Name   
 --------------------|----------------|--------------- 
  John, Kennedy      | John           | Kennedy       
  Richard, Nixon     | Richard        | Nixon         
  Eleanor, Roosevelt | Eleanor        | Roosevelt     
  Jack, Black        | Jack           | Black         
  Richard, Nixon     | Richard        | Nixon

I want to create without a nested loops and preferably using Linq, a DataTable containing ONLY unique values like this one called dt_Candidates2:

      Candidate      |   First Name   |   Last Name   
 --------------------|----------------|--------------- 
  John, Kennedy      | John           | Kennedy       
  Eleanor, Roosevelt | Eleanor        | Roosevelt     
  Jack, Black        | Jack           | Black

And a list or an array called RejectedCandidates containing the distinct duplicates

RejectedCandidates = {"Richard, Nixon"}
datatable linq c#
2 Answers

As noted, I don't think it really needs LINQ here. It can go something like this:

DataTable dt = new DataTable();
dt.Columns.Add("Candidate");
dt.Columns.Add("First");
dt.Columns.Add("Last");
dt.PrimaryKey = new []{ dt.Columns["Candidate"] }; //means that dt.Find() will work

while(...){
  string candidate = ...

  if(dt.Rows.Find(candidate) != null)
    RejectList.Add(...);
  else
    dt.Rows.Add(...);
}

Avoid using LINQ's .Any on a DataTable for this. Not only is it a pain to get going because it needs casting steps or extension libraries (see here) to, it will then use loops to find the info you seek; the built-in mechanism for the PrimaryKey uses hash tables for much faster lookups.

user_14106640 
var dt = new DataTable
{
    Columns = {"Candidate", "First Name", "Last Name"},
    Rows = 
    {
        new object [] { "John, Kennedy", "John", "Kennedy"},
        new object [] { "Richard, Nixon", "Richard", "Nixon"},
        new object [] { "Eleanor, Roosevelt", "Eleanor", "Roosevelt"},
        new object [] { "Jack, Black", "Jack", "Black"},
        new object [] { "Richard, Nixon", "Richard", "Nixon"},
    }
};

you can use grouping (groupBy) to find duplicates, filter them out, and then create a new DataTable, using DataTableExtensions.CopyToDataTable extension method:

var dt2 = dt.AsEnumerable()

            .GroupBy(r => r["Candidate"])
            .Where(g => g.Count() == 1)

            .Select(g => g.First())
            .CopyToDataTable();
user_15064540
Related