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));
}

Have you tried using the let operator? – frenchie Nov 13 '12 at 08:41 — frenchie, Nov 13 '12 at 08:41
@frenchie no, I haven't. – Eren Ersönmez Nov 13 '12 at 08:42 — Eren Ersönmez, Nov 13 '12 at 08:42

score 3 · Answer 1 · edited Feb 04 '19 at 11:45

3

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.

edited Feb 04 '19 at 11:45

Mark Cooper

6,738
5
54
92

answered Nov 13 '12 at 08:43

Sergey Berezovskiy

232,247
41
429
459

+1. This does give the same result (and probably is more efficient), however, I was curious to know the exact functional equivalent, which this not. – Eren Ersönmez Nov 13 '12 at 09:10
@ErenErsönmez I also was curious, thus verified both queries with LINQ Pad :) – Sergey Berezovskiy Nov 13 '12 at 09:12
The question was really about linq-to-objects. But I'm also looking at the SQL executed by Entity Framework using SQL Profiler, and it looks like EF by-passes the `orderby` (no ORDER BY in the executed SQL) -- so it makes no difference if you add `descending` or not – Eren Ersönmez Nov 13 '12 at 09:23
@ErenErsönmez yep, that what I was talking about in my last update – Sergey Berezovskiy Nov 13 '12 at 09:28

score 3 · Accepted Answer · answered Nov 13 '12 at 08:48

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.)

Convert EF LINQ method syntax to query syntax

2 Answers2