Convert EF LINQ method syntax to query syntax

Question

How would you write this exact same query using the LINQ query syntax?

var q2 = list.GroupBy(x => x.GroupId)
             .Select(g => g
                 .OrderByDescending(x => x.Date)
                 .FirstOrDefault());

I though this should work but it didn't:

var q1 = from x in list
         group x by x.GroupId into g
         from y in g
         orderby y.Date descending
         select g.FirstOrDefault();

Here's a test program if you want to play around with it:

public class MyClass
{
    public int Id { get; set; }
    public string GroupId { get; set; }
    public DateTime Date { get; set; }

    public override bool Equals(object obj)
    {
        var item = obj as MyClass;
        return item == null ? false : item.Id == this.Id;
    }
}

static void Main(string[] args)
{
    var list = new List<MyClass> 
    {
        new MyClass { GroupId = "100", Date=DateTime.Parse("01/01/2000"), Id = 1},
        new MyClass { GroupId = "100", Date=DateTime.Parse("02/01/2000"), Id = 2},
        new MyClass { GroupId = "200", Date=DateTime.Parse("01/01/2000"), Id = 3},
        new MyClass { GroupId = "200", Date=DateTime.Parse("02/01/2000"), Id = 4},
        new MyClass { GroupId = "300", Date=DateTime.Parse("01/01/2000"), Id = 5},
        new MyClass { GroupId = "300", Date=DateTime.Parse("02/01/2000"), Id = 6},
    };

    var q1 = from x in list
                group x by x.GroupId into g
                from y in g
                orderby y.Date descending
                select g.FirstOrDefault();

    var q2 = list.GroupBy(x => x.GroupId)
                    .Select(g => g
                        .OrderByDescending(x => x.Date)
                        .FirstOrDefault());

    Debug.Assert(q1.SequenceEqual(q2));
}

Answer 1

Reflector gives me the following:

var q2 = from x in list
         group x by x.GroupId into g
         select (
             from x in g
             orderby x.Date descending
             select x).FirstOrDefault();

which looks about right.

(As Lazy says, there's no need for FirstOrDefault in place of First as the groups won't be empty, but it doesn't hurt.)

Answer 2

There is no FirstOrDefault operator in comprehension query syntax (if items are grouped, then at least one item will present in group, so you need First), but other stuff is translated like this:

var q1 = from c in list
         orderby c.Date descending
         group c by c.GroupId into g
         select g.First();

Trick is that items in group will be ordered, if you do grouping on ordered sequence. And voilà:

q1.SequenceEqual(q2) returns true

BTW according to LINQ Pad this query generates exactly same SQL, as query with nested from x in g orderby x.Date descending select x

UPDATE Also my bad - First will work with LINQ to Objects, or LINQ to SQL, but with LINQ to Entities you should use FirstOrDefault. And second note - these queries (my and with nested query) will generate same SQL for LINQ to SQL, but with EF my ordering is not applied.