C# DataSet - Retrieving Unique Value based on colu

Question

I'm having some issues trying to retrieve unique values from a DataSet in csharp, is that possible?

Actually I'm doing something like this that gets a dataset from a webservice:

 webService.getInstructions(Username, Password, AppKey).Tables[0].Select(null, "account name asc");

So in this case I get a alphabetical list from the accounts, but there are some duplicated rows in this dataset.

Is there any way to make this Dataset return values with unique "account number" and sort it alphabetically by "account name"?

Something in place of the filterExpression would be very nice I think. :)

Thanks in advance

Answer 1

I would use a little linq magic on the datatable.

        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("AccountNumber", typeof(System.Int32)));
        dt.Columns.Add(new DataColumn("AccountName", typeof(System.String)));

        for (int ii = 0; ii < 20; ii++)
            dt.Rows.Add(new object[]{ii, "abc" + ii.ToString()});

        dt.Rows[6][0] = 5;
        dt.Rows[7][0] = 5;
        dt.Rows[8][0] = 5;

        //using grouping to isolate groups with just one item
        var groupedRows =   from row in dt.Select("", "AccountName ASC")
                            group row by row["AccountNumber"] into rowGroup
                            where rowGroup.Count() == 1
                            select rowGroup;

        foreach (var group in groupedRows)
            foreach(DataRow dr in group)
                Console.WriteLine("Account #: {0}  Account name: {1}", dr["AccountNumber"], dr["AccountName"]);


        //using nested select to avoid grouping
        Console.WriteLine();
        Console.WriteLine("Nested select");
        var z = from row in dt.Select()
                where (from x in dt.Select() where (int) x["AccountNumber"] == (int) row["AccountNumber"] select x).Count() == 1
                orderby row["AccountName"]
                select row;

        foreach(DataRow dr in z)
            Console.WriteLine("Account #: {0}  Account name: {1}", dr["AccountNumber"], dr["AccountName"]);


        Console.WriteLine();
        Console.WriteLine("Datatable select");
        var y = from row in dt.Select()
                where (from x in dt.Select("AccountNumber = " + row["AccountNumber"]) select x).Count() == 1
                orderby row["AccountName"]
                select row;

        foreach (DataRow dr in y)
            Console.WriteLine("Account #: {0}  Account name: {1}", dr["AccountNumber"], dr["AccountName"]);

With the results printed to the screen, notice that any row with an AccountNumber of '5' is missing, because it wasn't unique. Also notice that in the first example i used the dataTable.Select() to do the ordering, as the ordering is the same irrespective of what rows are removed due to not being unique. The second and third samples will give you an IEnumerable list of rows to work with that you can bind directly to, the first will give you a bunch of groups containing the individual rows.

Answer 2

Personally I'd change the web-service to do this filtering and sorting at the server to reduce bandwidth needs, probably returning a simple data-type or custom class (not DataTable or anything similar). But LINQ would do the job... (updated after re-reading the question)

var rows = dataset.Tables[0].AsEnumerable()
    .DistinctBy(row => row.Field<string>("account number"))
    .OrderBy(row => row.Field<string>("account name"))
    .ToArray();

using custom DistinctBy method:

    static IEnumerable<TSource> DistinctBy<TSource, TValue>(
        this IEnumerable<TSource> source,
        Func<TSource, TValue> selector)
    {
        HashSet<TValue> unique = new HashSet<TValue>();
        foreach (var item in source)
        {
            if (unique.Add(selector(item))) yield return item;
        }
    }