-1

I have two columns as Date and PersonInCharge, I want to sort the list by descending by date then return the list of ONLY person's name without duplicates. I used Distinct() to filter the final name list but I don't know why the list order is always alphabetical instead of descending date.

Here is my code so far:

  IList<string> model.StaffNames = await _context.FarmDiary
    .Select(f => new { f.PersonInCharge, f.Date })
    .OrderByDescending(x => x.Date)
    .Select(x => x.PersonInCharge)
    .Distinct()
    .ToListAsync();
Hoàng Nguyễn
  • 1,121
  • 3
  • 33
  • 57
  • If this is Linq2Entities which resolves to some SQL-command it´s pure natural, as the order by-statement allways comes after any distinct or grouping. Anyway the order of the statements highly depends on the underlying linq-provider. For Linq2Objects one should expect order should be preserved. – MakePeaceGreatAgain Aug 02 '18 at 09:19
  • In most DBMS distinct is implemented via ordering and comparing to the previous, which means distinct may overthrow any ordering you did previously. A reasonable approach by the provider would then be to ignore all orderings before the .Distinct() call (since .net .Distinct() does not guarantee preservation of order either they wouldn't even be inconsistent) – DevilSuichiro Aug 02 '18 at 09:25
  • The query that is executed is simplified to `SELECT DISTINCT PersonInCharge FROM FarmDiary`. Since distinct can use indexes to speed up the query, the order of results will depend on the indexes available (for reasons see other comments). – Caramiriel Aug 02 '18 at 09:28

1 Answers1

1

Distinct() is about returning distinct rows, it doesn't promise any order but still orders on what it operates. For ordering there is OrderBy, OrderByDescending. In your case, since there is no Date in your final list, you cannot order by date.

EDIT:

void Main()
{
    List<FarmDiary> farmDiary = new List<FarmDiary> {
        new FarmDiary{Id=1, PersonInCharge="Bob",Date=new DateTime(2018,6,1)},
        new FarmDiary{Id=2, PersonInCharge="John",Date=new DateTime(2018,6,2)},
        new FarmDiary{Id=3, PersonInCharge="Bob",Date=new DateTime(2018,6,15)},
        new FarmDiary{Id=4, PersonInCharge="David",Date=new DateTime(2018,7,1)},
        new FarmDiary{Id=5, PersonInCharge="Zachary",Date=new DateTime(2018,6,10)},
    };

    List<string> staffNames = farmDiary
    .GroupBy(d => d.PersonInCharge)
    .OrderByDescending(x => x.OrderByDescending(d => d.Date).First().Date)
    .Select(x => x.Key)
    .ToList();

    staffNames.Dump();
}

public class FarmDiary
{ 
    public int Id { get; set; }
    public string PersonInCharge { get; set; }
    public DateTime Date { get; set; }
}
Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39