LINQ OrderBy Based on String Values

Question

Suppose we have two tables Parent "DocumentCodes" and Child "Documents". The DocumentCodes table has DID, DocumentName, PrintOrder, and AscOrDesc columns. The document table has the column IDs, DID, and EffectiveDate. We get the data by joining these two tables.

We need to sort the data based on the rules below.

• Sort by "PrintOrder" column in ascending order.
• If two or more lines have the same "DocumentNames" value, then sort by "EffeciveDate" in ascending or descending order based on the value of "AscOrDesc". The AscOrDesc column accepts only A or D. If the value is “A”, we need to sort “EffectiveDate” in ascending order, and if the value is “D”, we need to sort “EffectiveDate” in descending order.

For instance,

DocumentCodes

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

<strong> 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 over two tables. We have a 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 using the rules above. 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. The effective date is in the format MM / DD / YYYY.

I tried with the code below, but it does not work.

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 am i doing wrong in the above code?

Answer 1

, , , PrintOrder, AscOrDesc. , .

, , , , . , , - ... " ". DateTime Ticks, :

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;

, , ...

Answer 2

, - , . , , @JonSkeet .:)

( LINQ To Object, , 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

TYPO, ,

           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;

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