LINQ Sort DataTable based on 2 columns

user_12062207 0
Viewed 49

I'm trying to Sort a DataTable with LINQ based on 2 columns. I want to sort it by:

  1. Date column first
  2. Category column second, and in this column to be a custom sort: Here I have "Altele", "Ingrasaminte","Pesticide", "Seminte","Motorina". And I would like the order to be "Ingrasaminte", "Motorina", "Pesticide", "Seminte" and "Altele" last.

This is what I have so far: (From x In dataTable.AsEnumerable() Order By Convert.ToString(x("Date")), Convert.ToString(x("Category")) Select x).CopyToDataTable

This puts "Altele" first in Category column and I would like it to be last.

DataTable Example

Date             Category      TURN INTO =>    Date             Category
21.05.2022       Altele                        21.05.2022       Ingrasaminte
22.05.2022       Altele                        21.05.2022       Motorina
21.05.2022       Pesticide                     21.05.2022       Pesticide
22.05.2022       Pesticide                     21.05.2022       Seminte
21.05.2022       Ingrasaminte                  21.05.2022       Altele
22.05.2022       Ingrasaminte                  22.05.2022       Ingrasaminte
21.05.2022       Motorina                      22.05.2022       Motorina
22.05.2022       Motorina                      22.05.2022       Pesticide
21.05.2022       Seminte                       22.05.2022       Seminte
22.05.2022       Seminte                       22.05.2022       Altele

Can you help please ?

linq c#
1 Answers

First you must create a custom Comparer class

class Comparer : IComparer<string>
{
    public static Comparer Instance = new Comparer();

    public int Compare(string x, string y)
    {
        if (x == "Altele" && y != "Altele")
        {
            return 1;
        }
        if (x != "Altele" && y == "Altele")
        {
            return -1;
        }
        return string.Compare(x, y);
    }
}

Then apply following Linq query against your DataTable

OrderBy(r => r.Date).ThenBy(r => r.Category, Comparer.Instance)

Following test code is from my Console Project. I created a DataSet1 class in DataSet Designer with DataTable1 class with Date and Category columns.

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApp2
{
    class Program
    {
        class Comparer : IComparer<string>
        {
            public static readonly Comparer Instance = new Comparer();

            public int Compare(string x, string y)
            {
                if (x == "Altele" && y != "Altele")
                {
                    return 1;
                }
                if (x != "Altele" && y == "Altele")
                {
                    return -1;
                }
                return string.Compare(x, y);
            }
        }

        static void Main(string[] args)
        {
            DataSet1 dataSet1 = new DataSet1();
            var format = "dd.MM.yyyy";
            var data =
            @"21.05.2022       Altele
22.05.2022       Altele
21.05.2022       Pesticide
22.05.2022       Pesticide
21.05.2022       Ingrasaminte
22.05.2022       Ingrasaminte
21.05.2022       Motorina
22.05.2022       Motorina
21.05.2022       Seminte
22.05.2022       Seminte".Split(new string[] { "\r", "\n", "       " }, StringSplitOptions.RemoveEmptyEntries);

            for (int i = 0; i < data.Length; i += 2)
            {
                dataSet1.DataTable1.AddDataTable1Row(DateTime.ParseExact(data[i], format, null), data[i + 1]);
            }

            Console.WriteLine(dataSet1.DataTable1.Select(r => $"{r.Date.ToString(format)}\t{r.Category}").Aggregate((a, b) => $"{a}\n{b}"));

            var array = dataSet1.DataTable1.OrderBy(r => r.Date).ThenBy(r => r.Category, Comparer.Instance).ToArray();

            Console.WriteLine();

            Console.WriteLine(array.Select(r => $"{r.Date.ToString(format)}\t{r.Category}").Aggregate((a, b) => $"{a}\n{b}"));

            Console.ReadKey();
        }

    }
}
user_37095370
Related