Order by multiple properties with linq to sql

Question

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.

@vc74 sorry, I don't understand what your status mean. all the refunds in the collection have different refundstatus — Z.Chen, Mar 22 '19 at 05:51
@Z. Chen your question is not crystal clear to me, an example with sample data would help... — vc 74, Mar 22 '19 at 06:20

Michał Turczyn · Accepted Answer · 2019-03-22T06:12:52.393

2

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.

edited Mar 22 '19 at 06:12

answered Mar 22 '19 at 06:07

Michał Turczyn

32,028
14
47
69

It's a good idea but I'm not sure `Ticks` can be translated into SQL. – vc 74 Mar 22 '19 at 06:15
@vc74 This is handled by .NET and Linq2Sql framework internally. So you should not worry about it :) – Michał Turczyn Mar 22 '19 at 06:17
Not all linq queries can be translated into SQL, especially those using .net features. But it may work, let's wait for the OP to try it... – vc 74 Mar 22 '19 at 06:18
it's a good idea,but it doesn't work.throw a exception Object reference not set to an instance of an object. at at lambda_method(Closure , AnonymousObject ) at System.Linq.Internal.Lookup`2.CreateForJoinAsync(IAsyncEnumerable`1 source, Func`2 keySelector, IEqualityComparer`1 comparer, CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Lookup.cs:line 301 – Z.Chen Mar 22 '19 at 07:00
@Z.Chen Check [NullReferenceException](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – Michał Turczyn Mar 22 '19 at 07:07
@MichałTurczyn I put Skip/Take after OrderBy-ThenBy. it run successfully .thanks~ – Z.Chen Mar 27 '19 at 01:31
@MichałTurczyn I tracked LINQ to SQL logs.@vc 74 may be correct . logs output in my update – Z.Chen Apr 01 '19 at 08:08

score 0 · Answer 2 · answered Mar 22 '19 at 05:54

0

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();

answered Mar 22 '19 at 05:54

Jerry

1,477
7
14

I'm sorry, it doesn't work .throw a exception Argument types do not match – Z.Chen Mar 22 '19 at 07:04

score 0 · Answer 3 · answered Mar 22 '19 at 08:53

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

it worked on linq,but it can not be translated into SQL – Z.Chen Mar 26 '19 at 09:37 — Z.Chen, Mar 26 '19 at 09:37

Order by multiple properties with linq to sql

3 Answers3