Why doesn't my DataTable.DefaultView.Sort sort things in order?

Question

So I'm trying to sort some data and print it, but DataTable.DefaultView.Sort doesn't seem to work how I would expect it to work. Given this data:

Col1    Col2    Col3
2802    32      0
2802    80      3
2802    130     3
2802    40      0
2802    40      0
2802    35      0

And sorting by "Col2", I am getting:

2802    130     3
2802    32      0
2802    35      0
2802    40      0
2802    40      0
2802    80      3

I wrote a simple ranking library to do what I expect (DataTableRanker.Framework.Util), which is below this code snippet, but it only sorts by 1 column and I'd prefer to use the .Sort = Col1, Col2, Col3, etc:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DataTableRanker.Framework.Util;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable testTable = new DataTable();
            testTable.Columns.Add("FacilityID");
            testTable.Columns.Add("FromDimensions");
            testTable.Columns.Add("FromAttributeName");

            DataRow dr1 = testTable.NewRow();
            dr1["FacilityID"] = "2802";
            dr1["FromDimensions"] = 4*8;
            dr1["FromAttributeName"] = 0;
            testTable.Rows.Add(dr1);

            DataRow dr2 = testTable.NewRow();
            dr2["FacilityID"] = "2802";
            dr2["FromDimensions"] = 8*10;
            dr2["FromAttributeName"] = 3;
            testTable.Rows.Add(dr2);

            DataRow dr3 = testTable.NewRow();
            dr3["FacilityID"] = "2802";
            dr3["FromDimensions"] = 10*13;
            dr3["FromAttributeName"] = 3;
            testTable.Rows.Add(dr3);

            DataRow dr4 = testTable.NewRow();
            dr4["FacilityID"] = "2802";
            dr4["FromDimensions"] = 4*10;
            dr4["FromAttributeName"] = 0;
            testTable.Rows.Add(dr4);

            DataRow dr5 = testTable.NewRow();
            dr5["FacilityID"] = "2802";
            dr5["FromDimensions"] = 4 * 10;
            dr5["FromAttributeName"] = 0;
            testTable.Rows.Add(dr5);

            DataRow dr6 = testTable.NewRow();
            dr6["FacilityID"] = "2802";
            dr6["FromDimensions"] = 5*7;
            dr6["FromAttributeName"] = 0;
            testTable.Rows.Add(dr6);

            DataTable copyTestTable;
            copyTestTable = testTable.Copy();
            copyTestTable.Columns.Add("Ranking");

            Console.Write("-- Not Sorted --");
            Console.WriteLine();

            foreach (DataRowView dr in testTable.DefaultView)
            {
                for (int i = 0; i < testTable.DefaultView.Table.Columns.Count; i++)
                    Console.Write(dr[i] + "\t");
                Console.WriteLine();
            }

            testTable.DefaultView.Sort = "FromDimensions";

            Console.Write("-- Sorted --");
            Console.WriteLine();
            foreach (DataRowView dr in testTable.DefaultView)
            {
                for (int i = 0; i < testTable.DefaultView.Table.Columns.Count; i++)
                    Console.Write(dr[i] + "\t");
                Console.WriteLine();
            }

            Console.Write("-- Ranked with System.Data --");
            Console.WriteLine();
            testTable.Columns.Add(new DataColumn("Rank", typeof(int)));
            int count = 1;
            foreach (DataRowView dr in testTable.DefaultView)
            {
                dr["Rank"] = count++;
            }

            foreach (DataRowView dr in testTable.DefaultView)
            {
                for (int i = 0; i < testTable.DefaultView.Table.Columns.Count; i++)
                    Console.Write(dr[i] + "\t");
                Console.WriteLine();
            }

            Console.Write("-- Ranked with DataTableRanker --");
            Console.WriteLine();

            DataTableSort u1 = new DataTableSort();
            u1.RankDataTable(ref copyTestTable, "FromDimensions", "Ranking");
            copyTestTable.DefaultView.Sort = "Ranking";
            foreach (DataRowView dr in copyTestTable.DefaultView)
            {
                for (int i = 0; i < copyTestTable.DefaultView.Table.Columns.Count; i++)
                    Console.Write(dr[i] + "\t");
                Console.WriteLine();
            }
        }
    }
}

Here is DataTableRanker.Framework.Util:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace DataTableRanker.Framework.Util
{
    public class DataTableSort
    {
        public void RankDataTable(ref DataTable toOperateDataTable, string sortByValue, string rankingValue)
        {
            // group data elements
            List<RankGroup> rgc = new List<RankGroup>();
            foreach (DataRow dr1 in toOperateDataTable.Rows)
            {
                RankGroup g1 = new RankGroup();
                foreach (DataRow dr2 in toOperateDataTable.Rows)
                {

                    if (dr1 != dr2 && dr2 != null)
                    {
                        if (!g1.Index.Contains(toOperateDataTable.Rows.IndexOf(dr1)))
                        {
                            g1.Index.Add(toOperateDataTable.Rows.IndexOf(dr1));
                            g1.Score = float.Parse((string)dr1[sortByValue]);
                        }

                        if ((float.Parse((string)dr1[sortByValue]) == float.Parse((string)dr2[sortByValue]) && !g1.Index.Contains(toOperateDataTable.Rows.IndexOf(dr2))))
                        {
                            g1.Index.Add(toOperateDataTable.Rows.IndexOf(dr2));
                            g1.Score = float.Parse((string)dr2[sortByValue]);
                        }                        
                    }

                }
                if (!ExistsIndexes(rgc, g1))
                    rgc.Add(g1);
            }

            List<RankGroup> sortedRankGroup = rgc.OrderByDescending(t => t.Score).ToList();

            int ranking = 1;
            foreach (RankGroup rg in sortedRankGroup)
            {
                foreach (int i in rg.Index)
                {
                    toOperateDataTable.Rows[i][rankingValue] = ranking;
                }
                ranking++;
            }            
        }

        bool ExistsIndexes(List<RankGroup> rgc, RankGroup rg)
        {
            foreach (RankGroup trg in rgc)
            {
                foreach (int i in rg.Index)
                {
                    if (trg.Index.Any(t => t == i))
                        return true;
                }
            }
            return false;
        }

        internal class RankGroup
        {
            // string sortByFiled;
            float score;
            List<int> indexes = new List<int>();
            int ranking;

            public float Score
            {
                get { return score; }
                set { score = value; }
            }

            public List<int> Index
            {
                get { return indexes; }
                set { indexes = value; }
            }

            public int Ranking
            {
                get { return ranking; }
                set { ranking = value; }
            }
        }
    }
}

Any help or explanation is much appreciated!

Please make sure your question is clear. "What I think it should." isn't meaningful to those who want to help you. Please see: https://stackoverflow.com/help/dont-ask — , Jul 24 '19 at 13:50
Why not : DataTable sortedTable = testTable.AsEnumerable().OrderBy(x => x[1]).ThenBy(x => x[2]).CopyToDataTable(); — jdweng, Jul 24 '19 at 13:54
@jdweng well, this will be maintained by others and I prefer not to leave them with lambdas — dirkdiggl3r, Jul 24 '19 at 13:55
It looks is being sorted as Text not as Number.. not sure this helps. — Rui Caramalho, Jul 24 '19 at 13:56
@jdweng also it seems to give me the same output https://i.imgur.com/KifmK4p.png — dirkdiggl3r, Jul 24 '19 at 13:58
The lamdas are much quick. Use the column names. Do you really think your 30 plus lines of code is easy to understand and maintained then a one line linq? DataTable sortedTable = testTable.AsEnumerable().OrderBy(x => "FromDimensions").ThenBy(x => "FromAttributeName").CopyToDataTable(); — jdweng, Jul 24 '19 at 14:00
@jdweng well, ideally I can just have a `.Sort = Col1, Col2, Col3`, but yes the lambdas are also outputting the same thing, where 32 is sorted above 80. Thank you though — dirkdiggl3r, Jul 24 '19 at 14:04
This could be a duplicate of https://stackoverflow.com/questions/24053498/how-to-sort-datatable-by-column-with-datetime-datatype (column type is not specified) — ESG, Jul 24 '19 at 14:05
You need to make the columns integers : testTable.Columns.Add("FromDimensions",typeof(int)); testTable.Columns.Add("FromAttributeName", typeof(int)); — jdweng, Jul 24 '19 at 14:06

score 2 · Answer 1 · 2019-07-24T14:18:37.017

2

As was suggested in the comments, the sort order is string based. 1 comes before 3 so 130 appears before 31. This is the default behavior when you add a new column with no other options

In the examples on the DataTable class documentation, take a look at creating a column first, and setting it's data type.

column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
table.Columns.Add(column);

Update: Credit to ESG in comments for pointing out that DataColumnCollection.Add has an overload that will take a type as well.

edited Jul 24 '19 at 14:18

answered Jul 24 '19 at 14:05

3

There's an overload to `Columns.Add` that takes a type if you want to simplify the code a bit – ESG Jul 24 '19 at 14:07

Why doesn't my DataTable.DefaultView.Sort sort things in order?

1 Answers1