LINQ distinct and sorting-CodePudding

I have the following query, which I wish to be distinct on "RegistrationNumber", and sorted by "DriverToLoad".

Distinct/grouping works fine. However, my query sorts by "RegistrationNumber" apparently ignoring the "OrderBy":

            Drive = await _db.Drive
                .Where(m => m.StatusId == 5 || m.StatusId == 1010 || m.StatusId == 1012)
                .Include(s => s.DriveStatus)
                .Include(d => d.Location)
                .Include(f => f.Item)
                .GroupBy(m => m.RegistrationNumber)
                .Select(g => g.OrderBy(m => m.DriverToLoad).First())
                .ToListAsync(),

Any LINQ experts who can point out what the problem is, and how to solve it? I'm working with .NET7. Thanks a lot in advance.

CodePudding user response：

OrderBy in g.OrderBy(m => m.DriverToLoad).First() should be applied only to the item group, from which you are selecting only one. You have no explicit ordering provided for the final query results - try adding one:

Drive = await _db.Drive
    .Where(m => m.StatusId == 5 || m.StatusId == 1010 || m.StatusId == 1012)
    .Include(s => s.DriveStatus)
    .Include(d => d.Location)
    .Include(f => f.Item)
    .GroupBy(m => m.RegistrationNumber)
    .Select(g => g.OrderBy(m => m.DriverToLoad).First())
    .OrderBy(m => m.DriverToLoad)
    .ToListAsync();

CodePudding user response：

I'm going to suggest you want something like this:

var Drive =
    await _db.Drive
        .Where(m => m.StatusId == 5 || m.StatusId == 1010 || m.StatusId == 1012)
        .Include(s => s.DriveStatus)
        .Include(d => d.Location)
        .Include(f => f.Item)
        .GroupBy(m => m.RegistrationNumber, m => m.DriverToLoad)
        .SelectMany(g => g.OrderBy(x => x).Take(1))
        .ToListAsync();

Or like this:

var Drive =
    await
    (
        from m in _db.Drive
            .Include(s => s.DriveStatus)
            .Include(d => d.Location)
            .Include(f => f.Item)
        where m.StatusId == 5 || m.StatusId == 1010 || m.StatusId == 1012
        group m.DriverToLoad by m.RegistrationNumber into g
        from d in g.OrderBy(x => x).Take(1)
        select d
    )
        .ToListAsync();

CodePudding user response：

I have solved it, but probably not the nicest way (...more like a "hack" I would say).

        var drive = await _db.Drive
        .Where(m => m.StatusId == 5 || m.StatusId == 1010 || m.StatusId == 1012)
        .Include(s => s.DriveStatus)
        .Include(d => d.Location)
        .Include(f => f.Item)
        .GroupBy(m => m.RegistrationNumber)
        .Select(g => g.OrderBy(m => m.DriverToLoad).First())
        .ToListAsync();

        DriveListViewModel model = new DriveListViewModel()
        {
            Drive = drive.OrderBy(a => a.DriverToLoad),
        };

This way I get the sorting I wish, and can return that to the view.

Haven't yet seen the power of LINQ, so maybe I should aim for using ADO and SQL directly in my controllers, and somehow convert that to lists to be returned to my views ;-)

Thank you all for all your great suggestions. I really appreciate it.