Select statement on DataTable without where clause using Linq

Question

I've got a piece of code that needs to deal with a DataTable. The DataTable looks something like this:

  PartnerID    |    Partner Name    |    GroupID    |    Group Name    |    Description
------------------------------------------------------------------------------------------
      1        |     First Name     |       4       |    Group Name1   |   Foo
      2        |     Second Name    |       12      |    Group Name2   |    Bar
      3        |     Third Name     |       7       |    Group Name3   |    Hello
      3        |     Third Name     |       8       |    Group Name4   |    Hello World

Now what I am trying to accomplish is the performance of following SQL Statement:

SELECT DISTINCT PartnerID, Partner Name
FROM Table1

In C# using Linq with the expected output looking like this:

  PartnerID    |    Partner Name    |
-------------------------------------
      1        |    First Name      |
      2        |    Second Name     |
      3        |    Third Name      |

I have already seen following post:

LINQ query on a DataTable

And found it very unhelpful for my case, since all i really want to do is getting specified columns, but all the answers there display a solution that only seems to be working with where statements or selecting all columns by default.

My current code looks something like this right now:

     static void Main(string[] args)
     {
        DataTable fullTable = new DataTable();

        AddColumns(fullTable, "PartnerID", "Partner Name", "GroupID", "Group Name", "Description");

        fullTable.Rows.Add(1, "First Name", 4, "Group Name1", "Foo");
        fullTable.Rows.Add(2, "Second Name", 12, "Group Name2", "Bar");
        fullTable.Rows.Add(3, "Third Name", 7, "Group Name3", "Hello");
        fullTable.Rows.Add(3, "Third Name", 8, "Group Name4", "Hello World");

        var selectTwoCols = from arow in fullTable.AsEnumerable()
                            select arow; //how do i select specific columns from those rows?
        foreach (DataRow dataRow in selectTwoCols.Rows)
        {
            foreach (var item in dataRow.ItemArray)
            {
                Console.Write(item + " ");
            }
            Console.WriteLine();
        }

    }

    static void AddColumns(DataTable table, params string[] columnNames)
    {
        for (int i = 0; i < columnNames.Length; i++)
        {
            table.Columns.Add(columnNames[i]);
        }
    }

I'm open to using different classes aswell, although im still curious to know how this would be solved using DataTables in particular

Answer 1

fullTable
   .AsEnumerable()
   .Select(x => new 
                { 
                   PartnerID = x.Field<int>("PartnerID"), 
                   PartnerName = x.Field<string>("Partner Name")
                })
   .Distinct();

This will create an anonymous type with the two properties you want. You than apply a Distinct to remove the duplicates. Anonymous types handles GetHashCode and Equals for you which Distinct uses to identify duplicates.

Answer 2

We've already got a good answer here, but i think this is a more "intended" way of doing this.

In addition to the DataTable class, C# offers a DataView class.

In that classes Documentation we can read the following:

Represents a databindable, customized view of a DataTable for sorting, filtering, searching, editing, and navigation.

So judging from this, I assume, that Microsoft intention for this class was to be used in combination with DataTable to filter out rows or columns and pretty much any of the operations mentioned in the documentation.

So I used

DataView view = new DataView(fullTable); 

DataTable twoColsDistinct = view.ToTable(true, "PartnerID", "Partner Name"); //distinct
DataTable twoColsNonDistinct = view.ToTable(false, "PartnerID", "Partner Name"); //not distinct

To get two DataTables with only the two required columns selected and printing those two datatables content results in following output:

two cols distinct
----------------------
1 First Name
2 Second Name
3 Third Name
----------------------
two cols non distinct
----------------------
1 First Name
2 Second Name
3 Third Name
3 Third Name

which is exactly what i needed. just selecting columns.

using following code results in the exact same output:

var query = fullTable
               .AsEnumerable()
               .Select(x => new
                   {
                       PartnerID = x.Field<string>("PartnerID"),
                       PartnerName = x.Field<string>("Partner Name")
                   }
               ).Distinct();

foreach(var t in query)
{
    Console.WriteLine(t.PartnerID + " " + t.PartnerName);
}

prints

1 First Name
2 Second Name
3 Third Name

although i personally prefer the more compact solution above because with the "longer" solution, we are creating custom objects (more precisely: anonymous types), with PartnerID and PartnerName becoming that objects instance variables and in my "simple" case, I was not dealing with any object oriented "stuff" to begin with.

I find the compact solution easier to read and understand.

Bonus:

We can use the same DataView object to create filtered DataTables aswell which may or may not be useful:

view.RowFilter = "PartnerID > 1";

DataTable partnerIdGreaterThanOne = view.ToTable(true);

Printing this DataTable outputs the following:

2 Second Name 12 Group Name2 Bar
3 Third Name 7 Group Name3 Hello
3 Third Name 8 Group Name4 Hello World

Not sure if using Linq is any better here. Just wanted to throw this out there.

EDIT:

I did some performance testing and what i found out was that using

var query = fullTable
            .AsEnumerable()
            .Select(x => new
                    {
                        PartnerID = x.Field<string>("PartnerID"),
                        PartnerName = x.Field<string>("Partner Name")
                    })
            .Distinct();

is A LOT faster than the DataView solution IF one does not need the additional DataTable object created on the dataview approach.