Get a Distinct List from ADO.Net DataSet Using Linq

There are some sample .Net functions out there to pull a list of distinct items from an ADO.Net DataTable, but none of them I found take advantage of LINQ.  So I thought I would post something I came up with to fill the gap.

In the code sample below, the goal is to pull a list of distinct values from a single column in a DataTable.  There are a few ways to approach this, depending on the end-game.  I listed 4 options, 2 with LINQ syntax and their Lambda expression twins.

// —————————————————————–
// Build a list DataTable to test
// —————————————————————–
DataTable tbl = new DataTable();
tbl.Columns.Add(“Title”, typeof(string));
for (int i = 1; i <= 100; i++)
{
    // Add 1 row
    DataRow row1 = tbl.NewRow();
    row1["Title"] = string.Format(“Title {0}”, i);
    tbl.Rows.Add(row1);

    // Add duplicate row
    DataRow row2 = tbl.NewRow();
    row2["Title"] = string.Format(“Title {0}”, i);
    tbl.Rows.Add(row2);

    // Add null row for testing
    DataRow row3 = tbl.NewRow();
    row3["Title"] = DBNull.Value;
    tbl.Rows.Add(row3);
}

// —————————————————————–
// All four of these examples yield the same results
// Each returns a list of 100 distinct rows
// Pick your poison!
// —————————————————————–

// Sample 1
// —————————————————————–
// Get a distinct list of titles
// Linq syntax against the DataTable using
// This is functionally the same as the 2nd example
List<string> distinctTitles2 =
        (from r in
            (IEnumerable<DataRow>)tbl.AsEnumerable()
            where !string.IsNullOrEmpty(r.Field<string>(“Title”))
            select r.Field<string>(“Title”)).Distinct().ToList();

// Sample 2
// —————————————————————–
// Get a distinct list of titles
// Lambda expression against the DataTable 
// This is functionally the same as the 1st example
List<string> distinctTitles1 = ((IEnumerable<DataRow>)tbl.AsEnumerable())
            .Select<DataRow, string>(r => r.Field<string>(“Title”))
            .Where(title => !string.IsNullOrEmpty(title))
            .Distinct().ToList();

// Sample 3
// —————————————————————–
// Get a distinct list of titles
// On this one, I’m using the tbl.Select() method to get an array of DataRows matching filter criteria
// Linq expression is then used against the DataRow array to get distinct titles
// This is functionally the same as the 4th example
List<string> distinctTitles4 =
        (from r in
        tbl.Select(“Title IS NOT NULL And TRIM(Title) <> ””)
        select r.Field<string>(“Title”))
        .Distinct().ToList();

// Sample 4
// —————————————————————–
// Get a distinct list of title
// On this one, I’m using the tbl.Select() method to get an array of DataRows matching filter criteria
// Lambda expression against the DataRow array to get distinct titles
// This is functionally the same as the 3rd example
List<string> distinctTitles3 = tbl.Select(“Title IS NOT NULL And TRIM(Title) <> ””)
        .Select(r => r.Field<string>(“Title”))
        .Distinct().ToList();

Enjoy!

 

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!