c# lambda linq order by multiple column's index

Question

I have a query just want to order by if ticketStatus = Attended first then Issue, then Unpaid, then Cancelled. Thanks

var duplicate = dt.AsEnumerable()
            .OrderBy((x,index) => index(ticketStatusOrder.IndexOf(x["TicketStatus"].ToString())))
            //.OrderBy(x => x["TicketStatus"].ToString() == "Attended")
            //.ThenBy(x => x["TicketStatus"].ToString() == "Issued")
            //.ThenBy(x => x["TicketStatus"].ToString() == "Unpaid")
            //.ThenBy(x => x["TicketStatus"].ToString() == "Cancelled")
            .GroupBy(x => new {EventID = x["EventID"].ToString(), ContactID = x["ContactID"].ToString()})
            .Select(x => x.FirstOrDefault()).CopyToDataTable();

Use a single `OrderBy`. Inside it, convert the string to an `TicketStatusOrdered` `enum` which is in the order you want. https://stackoverflow.com/questions/16100/how-should-i-convert-a-string-to-an-enum-in-c — mjwills, Nov 15 '18 at 06:14

Mohsin · Answer 1 · 2018-11-15T08:41:46.137

As suggested by @mjwills answer, create an enum. But!

You can't do an in-place sorting of a datatable, See this answer.

So, follow below two steps to sort your datatable.

Create an enum

enum TicketStatuses { Attended = 1, Issued = 2, Unpaid = 3, Cancelled = 4 }

I tried below code in VS2017 and .Net Core 2 and is sorting properly as per your requirements.

class Program
{
    static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        DataColumn dc = new DataColumn("id", typeof(String));
        dt.Columns.Add(dc);

        dc = new DataColumn("TicketStatus", typeof(TicketStatuses));
        dt.Columns.Add(dc);

        DataRow dr;
        //Adding test data of 6 rows
        for (int i = 0; i <= 6; i++)
        {
            dr = dt.NewRow();

            dr[0] = i + 2; //Just a random column value - Could be anything here.
            dt.Rows.Add(dr);
        }
        // Setting column values for Column "TicketStatus" of our choice.
        dt.Rows[0][1] = TicketStatuses.Unpaid;
        dt.Rows[1][1] = TicketStatuses.Cancelled;
        dt.Rows[2][1] = TicketStatuses.Cancelled;
        dt.Rows[3][1] = TicketStatuses.Issued;
        dt.Rows[4][1] = TicketStatuses.Attended;
        dt.Rows[5][1] = TicketStatuses.Attended;
        dt.Rows[6][1] = TicketStatuses.Issued;

        //sorting datarows
        DataRow[] dataRows = dt.Select().OrderBy(u => u["TicketStatus"]).ToArray();

        DataTable sortedDatatable = dataRows.CopyToDataTable();
    }
}

Cheers!

Those are for adding test data. Edited and added code comments. Thanks for pointing out. — Mohsin, Nov 15 '18 at 08:43
the ticketstatues are storing in the database cannot create one just like that. thanks — Dev, Nov 19 '18 at 02:15

score 0 · Answer 2 · answered Nov 15 '18 at 10:53

You can use custom comparer (but i guess that using enum is way better). Also you can't use this comparer if your OrderBy should be translated to SQL query.

Sample:

public class TicketStatusComparer : IComparer<string>
{
    private int GetIntValue( string value )
    {
        switch ( value )
        {
            case "Attended":
                return 1;
            case "Issue":
                return 2;
            case "Unpaid":
                return 3;
        }

        return 0;
    }

    public int Compare( string x, string y )
    {
        return GetIntValue( x ) - GetIntValue( y );
    }
}

and then use it like this:

var list = new[]
{
    new { A = 1, TicketStatus = "Issue" },
    new { A = 2, TicketStatus = "Attended" },
    new { A = 3, TicketStatus = "Unpaid" },
    new { A = 4, TicketStatus = "Attended" },
    new { A = 5, TicketStatus = "Unpaid" },
};

var xxx = list.OrderBy( x => x.TicketStatus, new TicketStatusComparer() ).ToList();

score 0 · Accepted Answer · answered Nov 19 '18 at 02:16

0

I just found out i can use with condition

       .OrderBy(x => x["TicketStatus"].ToString())
        .ThenBy(x => x["TicketStatus"].ToString() == "Attended")
        .ThenBy(x => x["TicketStatus"].ToString() == "Issued")
        .ThenBy(x => x["TicketStatus"].ToString() == "Unpaid")
        .ThenBy(x => x["TicketStatus"].ToString() == "Cancelled")

answered Nov 19 '18 at 02:16

Dev

171
2
18

You can reduce a `ThenBy` iteration by moving "Attended" to "OrderBy" like `.OrderBy(x => x["TicketStatus"].ToString() == "Attended")` – Mohsin Nov 20 '18 at 08:06

c# lambda linq order by multiple column's index

3 Answers3