LINQ OrderBy based on row values

Question

Lets say we have two tables Parent "DocumentCodes" and Child "Documents". DocumentCodes table have columns DID,DocumentName,PrintOrder and AscOrDesc Documents table have columns ID,DID and EffectiveDate.We are getting datatable by joining these two tables.

We need to sort this datatable based on below rules.

1. Sort By "PrintOrder" column ascending.
2. If two or more rows have similar "DocumentNames" value then sort by "EffeciveDate" ascending or descending based on "AscOrDesc" value. "AscOrDesc" column accepts only 'A' or 'D'. If value is 'A' we need to sort "EffectiveDate" ascending and if value is 'D' we need to sort "EffectiveDate" descending.

For example,

DocumentCodes

 DID     DocumentName       PrintOrder      AscOrDesc
 1        Test1               1               D
 2        Test2               2               A
 3        Test3               3               D

Documents

ID        DID        EffectiveDate    
 1         2           7/9/2017
 2         1           5/5/2017
 3         2           7/8/2017
 4         3           4/9/2017

After joining above two tables. We have DataTable.

ID      DocumentName EffectiveDate  PrintOrder  AscOrDesc
1         Test2        7/9/2017       2          A
2         Test1        5/5/2017       1          D
3         Test2        7/8/2017       2          A 
4         Test3        4/9/2017       3          D

Now After sorting this DataTable by using above rules. DataTable should look like this.

ID      DocumentName EffectiveDate  PrintOrder  AscOrDesc
1         Test1         5/5/2017      1           D
2         Test2         7/8/2017      2           A
3         Test2         7/9/2017      2           A
4         Test3         4/9/2017      3           D

Note: EffectiveDate is in MM/DD/YYYY format.

I tried with below code but its not working.

var records2 = from q in datatable.AsEnumerable()
               let sortorder= q.Field<string>("AscOrDesc") == "A" ?
               "q.Field<DateTime>(\"EffectiveDate\") ascending": 
               "q.Field<DateTime>(\"EffectiveDate\") descending"
               orderby q.Field<int>("PrintOrder"),sortorder
               select q;

what I am doing wrong in above code ?

Answer 1

The situation is a fairly ugly one, given that two result rows could theoretically be compared which have the same PrintOrder but different AscOrDesc values. It's only the source of the data that's preventing that.

I do have a horrible hack that I believe should work, but I'm really not proud of it. Basically, imagine that the date is a number... ordering by descending date is equivalent to ordering by the negation of the "date number". For DateTime, we can just take the Ticks value, leading to:

var records2 = from q in datatable.AsEnumerable()
               let ticks = q.Field<DateTime>("EffectiveDate").Ticks * 
                   (q.Field<string>("AscOrDesc") == "A" ? 1 : -1)
               orderby q.Field<int>("PrintOrder"), ticks
               select q;

Ugly as heck, but it should work...

Answer 2

Pretty ugly, but couldnt figure out something better that fits your needs. Maybe you have luck and @JonSkeet will come by again. :)

(Used LINQ To Object you would need to rewrite it fit your LINQ to SQL)

static void Main(string[] args)
{
    var lstFoos = new List<Foo>() {
        new Foo() { Id = 1, DocumentName = "Test2", EffectiveDate = new DateTime(2017, 7, 9), PrintOrder = 2, AscOrDesc = "A" },
        new Foo() { Id = 2, DocumentName = "Test1", EffectiveDate = new DateTime(2017, 5, 5), PrintOrder = 1, AscOrDesc = "D" },
        new Foo() { Id = 3, DocumentName = "Test2", EffectiveDate = new DateTime(2017, 7, 8), PrintOrder = 2, AscOrDesc = "A" },
        new Foo() { Id = 4, DocumentName = "Test3", EffectiveDate = new DateTime(2017, 4, 9), PrintOrder = 3, AscOrDesc = "D" },
        };

    var result = lstFoos.OrderBy(x => x.PrintOrder).GroupBy(x => x.DocumentName).SelectMany(x =>
    {
        if (x.Count() > 1)
        {
            var ascOrDesc = x.First().AscOrDesc;
            return new List<Foo>(ascOrDesc == "A" ? x.OrderBy(y => y.EffectiveDate) : x.OrderByDescending(y => y.EffectiveDate));
        }

        return new List<Foo>() {x.First()};
    });

    foreach (var foo in result)
        Console.WriteLine(foo.ToString());

    Console.ReadLine();
}

public class Foo
{
    public int Id { get; set; }
    public string DocumentName { get; set; }
    public DateTime EffectiveDate { get; set; }
    public int PrintOrder { get; set; }
    public string AscOrDesc { get; set; }

    public override string ToString()
    {
        return $"Id: {Id} | DocumentName: {DocumentName} | EffectiveDate: {EffectiveDate} | PrintOrder: {PrintOrder} | AscOrDesc: {AscOrDesc}";
    }
}

Answer 3

Looks like a TYPO, Hope this works

           var records2 = from q in datatable.AsEnumerable()
           orderby q.Field<int>("PrintOrder")
           orderby q.Field<string>("AscOrDesc") == "A" ? q.Field<DateTime>("EffectiveDate") :  q.Field<DateTime>("EffectiveDate") descending
           select q;

Usually my statement used to be like this

 var result = from q in datatable.AsEnumerable()
               orderby q.PrintOrder
               orderby q.AscOrDesc== "A" ? q.EffectiveDate: q.EffectiveDate descending
               select q;