如何用LINQ按序号排序且将已删除记录置于末尾？

Got it, let's sort this out for you! The issue with your current code is that using ThenBy(x => x.DELETED_DATE) doesn't reliably push deleted records to the end—database systems handle NULL values differently in sorting (most put NULLs first by default), so we need to explicitly prioritize non-deleted records first.

核心思路 #

First, we'll sort by a "deleted status" flag: non-deleted records (where DELETED_DATE is NULL) get placed at the top, and deleted records (with a non-NULL DELETED_DATE) go to the bottom. Then we sort the non-deleted (and deleted) groups by SEQ_NUMBER as you originally wanted.

修正后的代码 #

Here's the adjusted query that works with both EF Core and EF6:

var records = db.Records
    .Where(x => x.Record.otherId == otherId)
    // First sort by deleted status: non-deleted (false) comes first, deleted (true) last
    .OrderBy(x => x.DELETED_DATE.HasValue)
    // Then sort by sequence number in ascending order
    .ThenBy(x => x.SEQ_NUMBER);

If you prefer a more explicit numeric flag (to avoid any database-specific bool sorting quirks), you can use this version instead:

var records = db.Records
    .Where(x => x.Record.otherId == otherId)
    // 0 = non-deleted, 1 = deleted—ensures non-deleted comes first
    .OrderBy(x => x.DELETED_DATE.HasValue ? 1 : 0)
    .ThenBy(x => x.SEQ_NUMBER);

额外说明 #

• If you need SEQ_NUMBER sorted in descending order, just swap ThenBy with ThenByDescending.
• This approach translates cleanly to SQL (e.g., ORDER BY CASE WHEN DELETED_DATE IS NOT NULL THEN 1 ELSE 0 END, SEQ_NUMBER), so it's efficient and runs on the database side, not in memory.

内容的提问来源于stack exchange，提问作者user3985112