CODEWITHSUNDEEP
c#linqlinq-to-sql
Z.Chen
Z.Chen

Reputation: 129

Order by multiple properties with linq to sql

I have list which I want to order by like this

public class Refund {
    public int RefundStatus { get; set; }
    public DateTime SumbitTime { get; set; }
}

Order by RefundStatus first, then:

if RefundStatus == 1 then by SumbitTime ascending,

if RefundStatus != 1 then by SumbitTime descending.

How should i do by linq to sql?

UPDATE: I made changes followed Michal Turczyn .but log output display could not be translated

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'orderby [p].RefundStatus asc, ([p].SumbitTime.Ticks * Convert(IIF(([p].RefundStatus == 1), 1, -1), Int64)) asc, EF.Property(?[p]?, "Id") asc' could not be translated and will be evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'orderby [p].RefundStatus asc, ([p].SumbitTime.Ticks * Convert(IIF(([p].RefundStatus == 1), 1, -1), Int64)) asc, EF.Property(?[p]?, "Id") asc' could not be translated and will be evaluated locally.

Upvotes: 0

Views: 285

Answers (3)

Anu Viswan
Anu Viswan

Reputation: 18163

You could use the following.

var sorted = list.GroupBy(refund => refund.RefundStatus)
                 .OrderBy(x=>x.Key)
                 .SelectMany(
                             group => group.Key == 1 ? 
                             group.OrderBy(p => p.SubmitTime)
                             : group.OrderByDescending(p => p.SubmitTime));

Complete Example.

        var random = new Random();
        var list = Enumerable.Range(1, 10)
            .Select(x => 
                new Refund
                {
                    RefundStatus = random.Next(0,2),
                    SubmitTime = DateTime.Now.AddMinutes(x)
                });

Where Refund is defined as

public class Refund
{
    public int RefundStatus { get; set; }
    public DateTime SubmitTime { get; set; }
}

Sample Output

**Before Sorting

Refund = 0 - SubmitTime = 22-03-2019 14:22:16
Refund = 1 - SubmitTime = 22-03-2019 14:23:16
Refund = 0 - SubmitTime = 22-03-2019 14:24:16
Refund = 0 - SubmitTime = 22-03-2019 14:25:16
Refund = 0 - SubmitTime = 22-03-2019 14:26:16
Refund = 1 - SubmitTime = 22-03-2019 14:27:16
Refund = 0 - SubmitTime = 22-03-2019 14:28:16
Refund = 1 - SubmitTime = 22-03-2019 14:29:16
Refund = 0 - SubmitTime = 22-03-2019 14:30:16
Refund = 1 - SubmitTime = 22-03-2019 14:31:16

After Sorting

Refund = 0 - SubmitTime = 22-03-2019 14:31:16
Refund = 0 - SubmitTime = 22-03-2019 14:29:16
Refund = 0 - SubmitTime = 22-03-2019 14:28:16
Refund = 0 - SubmitTime = 22-03-2019 14:26:16
Refund = 0 - SubmitTime = 22-03-2019 14:23:16
Refund = 1 - SubmitTime = 22-03-2019 14:22:16
Refund = 1 - SubmitTime = 22-03-2019 14:24:16
Refund = 1 - SubmitTime = 22-03-2019 14:25:16
Refund = 1 - SubmitTime = 22-03-2019 14:27:16
Refund = 1 - SubmitTime = 22-03-2019 14:30:16

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37490

I would do that in following way:

List<Refund> refundList = new List<Refund>();
// populate your list
refundList = refundList
    // here you could also use:
    //.OrderBy(r => r.RefundStatus)
    // but I don't know if you want it this way
    .OrderBy(r => (r.RefundStatus == 1 ? 1 : -1))
    .ThenBy(r => r.SubmitTime.Ticks * (r.RefundStatus == 1 ? 1 : -1))
    .ToList();

The idea is that when you multiply by -1 number of ticks in your DateTime, it will order it in descending order without having to split your collection to two parts.

Upvotes: 2

Jerry
Jerry

Reputation: 1527

This should do what you are looking for if I understand your question properly.

var sorted = refundList.GroupBy(refund => refund.RefundStatus)
    .SelectMany(
        group => group.Key == 1 
        ? group.OrderBy(p => p.SumbitTime)
        : group.OrderByDescending(p => p.SumbitTime))
    .ToList();

Upvotes: 0

Related Questions