Sort System.Data.DataTable with multiple sort criteria

Question

I have this System.Data.DataTable:

DataTable dt = new DataTable();
dt.Columns.Add("Sector", typeof(string));
dt.Columns.Add("BBG IPC", typeof(double));
dt.Columns.Add("Analyst", typeof(string));
dt.Columns.Add("Issuer Group", typeof(string));
dt.Columns.Add("Seniority", typeof(string));
dt.Columns.Add("Mkt Value", typeof(double));
dt.Columns.Add("Nom Value", typeof(double));
dt.Columns.Add("Issue Group Rating", typeof(string));
dt.Columns.Add("Current SBR", typeof(string));
dt.Columns.Add("Notches", typeof(int));
dt.Columns.Add("Forward SBR", typeof(string));
dt.Columns.Add("Probability of Downgrade", typeof(string));
dt.Columns.Add("Risk Category", typeof(string));
dt.Columns.Add("Comment", typeof(string));
dt.Columns.Add("Restricted Message", typeof(string));


dt.Rows.Add(new object[] { "Agencies", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "BB",-1, "YY", "Hight", "R1", "Comment", "gvbhjnnijnibj" });
dt.Rows.Add(new object[] { "Agencies", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "CC",2, "II", "Low", "R2", "Bergtrgrt", "Other" });
dt.Rows.Add(new object[] { "Agencies", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "EE",3, "LL", "Mid", "R1", "vggvbhjjnnjoioion", "ggvbhibniujbuhvg uvvugvghV" });
dt.Rows.Add(new object[] { "Consumer", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "OO",-1, "SS", "Higth", "R3", "vgvgyhvgvjhbkj", "bibhjbhjbjhb  ubuyhbuyhb hbuhbuhbhb" });
dt.Rows.Add(new object[] { "Energy", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "PP",-2, "QQ","Higth", "R1", "ertyuiop", "tfcgvhb Uvugvugvuhvh" });
dt.Rows.Add(new object[] { "Energy", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "GG",-3, "FF", "Low", "R2", "gvgvgvvgfccfdxdxrtf xrtfvgh tdctfgv trcfygvh tcfyghv b ygvhb ", "ygvgubiujb" });

I need to Sort the table on the basis of these rules:

1st Level: Sector in alphabetic order.

2nd Level: Downgrade probability in the following order: High, Mid, Low

3rd Level: Issuer Group in alphabetic order

I've tried something like thisdt.DefaultView.Sort("Sector", "Probability of Downgrade", "Issuer Group"); but does not work. How can i do it? Thank you!

Answer 1

Try following which is similar to your last request :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication11
{
    class Program
    {
        static void Main(string[] args)
        {
            List<string> probabilityStr = new List<string>() { "Hight", "Mid", "Low" };

            DataTable dt = new DataTable();
            dt.Columns.Add("Sector", typeof(string));  //1
            dt.Columns.Add("BBG IPC", typeof(double));
            dt.Columns.Add("Analyst", typeof(string));
            dt.Columns.Add("Issuer Group", typeof(string));  //3
            dt.Columns.Add("Seniority", typeof(string));
            dt.Columns.Add("Mkt Value", typeof(double));
            dt.Columns.Add("Nom Value", typeof(double));
            dt.Columns.Add("Issue Group Rating", typeof(string));
            dt.Columns.Add("Current SBR", typeof(string));
            dt.Columns.Add("Notches", typeof(int));
            dt.Columns.Add("Forward SBR", typeof(string));
            dt.Columns.Add("Probability of Downgrade", typeof(string));  //2
            dt.Columns.Add("Risk Category", typeof(string));
            dt.Columns.Add("Comment", typeof(string));
            dt.Columns.Add("Restricted Message", typeof(string));


            dt.Rows.Add(new object[] { "Agencies", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "BB", -1, "YY", "Hight", "R1", "Comment", "gvbhjnnijnibj" });
            dt.Rows.Add(new object[] { "Agencies", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "CC", 2, "II", "Low", "R2", "Bergtrgrt", "Other" });
            dt.Rows.Add(new object[] { "Agencies", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "EE", 3, "LL", "Mid", "R1", "vggvbhjjnnjoioion", "ggvbhibniujbuhvg uvvugvghV" });
            dt.Rows.Add(new object[] { "Consumer", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "OO", -1, "SS", "Higth", "R3", "vgvgyhvgvjhbkj", "bibhjbhjbjhb  ubuyhbuyhb hbuhbuhbhb" });
            dt.Rows.Add(new object[] { "Energy", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "PP", -2, "QQ", "Higth", "R1", "ertyuiop", "tfcgvhb Uvugvugvuhvh" });
            dt.Rows.Add(new object[] { "Energy", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "GG", -3, "FF", "Low", "R2", "gvgvgvvgfccfdxdxrtf xrtfvgh tdctfgv trcfygvh tcfyghv b ygvhb ", "ygvgubiujb" });


            DataTable dt2 = dt.Clone();

            var sectors = dt.AsEnumerable()
                .OrderBy(x => x.Field<string>("Sector"))
                .ThenBy(x => probabilityStr.IndexOf(x.Field<string>("Probability of Downgrade")))
                .ThenBy(x => x.Field<string>("Issuer Group"))
                .GroupBy(x => x.Field<string>("Sector"))
                .ToList();

            for (int si = 0; si < sectors.Count(); si++)
            {
                var probabilities = sectors[si].GroupBy(x => x.Field<string>("Probability of Downgrade")).ToList();
                for (int pi = 0; pi < probabilities.Count(); pi++)
                {
                    var groups = probabilities[pi].GroupBy(x => x.Field<string>("Issuer Group")).ToList();
                    for (int gr = 0; gr < groups.Count(); gr++)
                    {
                        for (int r = 0; r < groups[gr].Count(); r++)
                        {
                            DataRow row = dt2.Rows.Add();
                            for (int i = 0; i < dt2.Columns.Count; i++)
                            {
                                switch (dt2.Columns[i].ColumnName)
                                {
                                    case "Sector" :
                                        if (pi == 0)
                                        {
                                            row["Sector"] = sectors[si].Key;
                                        }
                                        else
                                        {
                                            row["Sector"] = DBNull.Value;
                                        }
                                        break;

                                    case "Probability of Downgrade" :
                                        if (gr == 0)
                                        {
                                            row["Probability of Downgrade"] = probabilities[pi].Key;
                                        }
                                        else
                                        {
                                            row["Probability of Downgrade"] = DBNull.Value;
                                        }
                                        break;

                                    case "Issuer Group" :
                                        if (r == 0)
                                        {
                                            row["Issuer Group"] = groups[gr].Key;
                                        }
                                        else
                                        {
                                            row["Issuer Group"] = DBNull.Value;
                                        }
                                        break;

                                    default :
                                        row[i] = groups[r].First()[dt2.Columns[i].ColumnName];
                                        break;
                                }

                            }
                        }
                    }
                }
            }
        }
    }
}

Answer 2

The syntax for the DataView.Sort property is very similar to that used in SQL Server.

For your example, you could use this string:

dt.DefaultView.Sort = "Sector ASC, [Probability of Downgrade] ASC, [Issuer Group] ASC";

.. and then just access dt.DefaultView in your code.

But you'll probably have a problem, because an ascending sort on ("High", "Mid", "Low") is going to return them as "High", "Low", "Mid". Because they're strings, it'll do it alphabetically.

The simplest solution would be to use a numeric representation of the probability, if you have one in the source database, and display High/Mid/Low but not sort by them.

Here's a LINQ workaround, if the only data option you have is the probability string:

// Make a dictionary to map your probability strings to integers.
var probLookup = new Dictionary<string, int>();
probLookup["High"] = 3;
probLookup["Mid"] = 2;
probLookup["Low"] = 1; 

// Convert your DataTable into an array of a new type, that has the DataRow
// plus the three things you need to sort by, as separate properties.
var temp = dt.Select().Select(row => new() {   
                                   Row = row,
                                   Sector = row["Sector"],
                                   // Map the downgrade prob. to an integer.
                                   DowngradeProb = probLookup[(string) row["Probability of Downgrade"]],
                                   IssuerGroup = row["Issuer Group"]
                                 });

// Now, sort it by the 3 properties we created.
var temp2 = temp
           .OrderBy(a => a.Sector)
           .ThenByDesc(a => a.DowngradeProb)
           .ThenBy(a => a.IssuerGroup);

// Now, fish out the Row again.
var temp3 = temp2.Select(a => a.Row).ToArray();

IMPORTANT DISCLAIMER: The LINQ was done from memory: I don't warrant it!