LINQ versus LINQ to Entities, keep Sorting when Grouping

Question

This is normal LINQ, and this Test succeeds:

using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Collections.Generic;
using System.Linq;

namespace TestLINQ.Tests
{
[TestClass]
public class UnitTest2
{

    [TestMethod]
    public void TestGroupingAndOrdering()
    {
        var persons = GetTestPersons();

        //Try get oldest Man and oldest Woman.
        var Oldest = persons
            .Where(p => p.Sex == TestGender.Male || p.Sex == TestGender.Female)
            .OrderBy(p => p.DateOfBirth)
            .GroupBy(p => p.Sex)
            .Select(g => g.First());

        //Try get youngest Man and youngest Woman. 
        var youngest = persons
            .Where(p => p.Sex == TestGender.Male || p.Sex == TestGender.Female)
            .OrderByDescending(p => p.DateOfBirth) //reversed sorting.
            .GroupBy(p => p.Sex)
            .Select(g => g.First());                

        Assert.AreEqual(Oldest.ToList().Count, 2);
        Assert.AreEqual(youngest.ToList().Count, 2);

        Assert.AreEqual(Oldest.First().Name, "Navya"); //Oldest Woman
        Assert.AreEqual(Oldest.Last().Name, "Pranav"); //Oldest Man (Note: last() gets the second grouping)

        Assert.AreEqual(youngest.First().Name, "Aditya"); // Youngest Man.
        Assert.AreEqual(youngest.Last().Name, "Ananya"); // Youngest Woman.
    }

    public class TestPerson
    {
        public string Name { get; set; }
        public DateTime DateOfBirth { get; set; }
        public TestGender Sex { get; set; }

        public TestPerson(string name, DateTime dob, TestGender sex)
        {
            Name = name;
            DateOfBirth = dob;
            Sex = sex;
        }
    }

    public enum TestGender
    {
        Male,
        Female,
        Unknown
    }

    private List<TestPerson> GetTestPersons()
    {
        var list = new List<TestPerson>();
        //LOL @ using indian names.
        list.Add(new TestPerson("Advik", new DateTime(625909337000000000), TestGender.Male));
        list.Add(new TestPerson("Navya", new DateTime(608385600000000000), TestGender.Female));
        list.Add(new TestPerson("Ananya", new DateTime(626631005000000000), TestGender.Female));
        list.Add(new TestPerson("Aditya", new DateTime(630061565000000000), TestGender.Male));
        list.Add(new TestPerson("Veer", new DateTime(614074365000000000), TestGender.Male));
        list.Add(new TestPerson("Ishaan", new DateTime(617700836000000000), TestGender.Male));
        list.Add(new TestPerson("Pranav", new DateTime(610170773000000000), TestGender.Male));
        list.Add(new TestPerson("Purusha", new DateTime(629134727000000000), TestGender.Unknown));
        list.Add(new TestPerson("Avani", new DateTime(624015444000000000), TestGender.Female));
        list.Add(new TestPerson("Pari", new DateTime(625879085000000000), TestGender.Female));
        list.Add(new TestPerson("Nirguna", new DateTime(630489769000000000), TestGender.Unknown));
        return list;
    }
}
}

But when you insert the data into a database and try to do the same when using LINQ to Entities, it seems like the resulting SQL is the same, regardless of the sorting you apply.

    [TestMethod]
    public void TestGroupingAndOrdering()
    {
        using (var context = new TestCRM())
        {
            var persons = context.Persons;

            var result = persons
                .Where(p => p.Sex == Gender.Male || p.Sex == Gender.Female)
                .OrderBy(p => p.DateOfBirth)  // REGARDLESS of what you do here, the resulting SQL is the same.
                .GroupBy(p => p.Sex)
                .Select(g => g.FirstOrDefault());

            var EndResult = result.ToList();

            Assert.AreEqual(EndResult.Count, 2);
        }            
    }

Can somebody please help me? -> Please show me how to keep my sorting when grouping when using LINQ to Entities.

Answer 1

REGARDLESS of what you do here, the resulting SQL is the same.

This is because grouping destroys the order in SQL. ORDER BY clause comes last in the syntax of SQL's SELECT. Even if you manage to squeeze it into a subquery, the standard allows RDBMS to re-order data after GROUP BY. LINQ-to-Entities code that works with IQueryable<T> recognizes this, and ignores all ordering prior to GROUP BY.

To address this problem, move OrderBy to the part of your query that runs after grouping, for example

var result = persons
    .Where(p => p.Sex == Gender.Male || p.Sex == Gender.Female)
    .GroupBy(p => p.Sex)
    .Select(g => g.OrderBy(p => p.DateOfBirth).FirstOrDefault());