Looking for a more elegant LINQ solution

Question

I am trying to figure out an efficient way to retrieve the data I am after. I need to get a list of all of the most recent children by ParentId coupled with all parent entries that do NOT have children. I have created a visual guide to illustrate what the response should be.

The query needs to remain as IQueryable until ALL sorting and paging is completed. Last and LastOrDefault are not supported by LINQ to Entities (as stated by the error messages I have received while using them).

Using First or FirstOrDefault will return the error "This method or operation is not implemented"

Original Data:

-------------------------------
- Id - ParentId - CreatedDate -
-------------------------------
-  1 -          -  07/01/2013 -
-  2 -          -  07/01/2013 -
-  3 -          -  07/01/2013 -
-  4 -        1 -  07/02/2013 -
-  5 -        2 -  07/03/2013 -
-  6 -        2 -  07/04/2013 -
-  7 -        1 -  07/05/2013 -
-------------------------------

Data returned by query

-------------------------------
- Id - ParentId - CreatedDate -
-------------------------------
-  3 -          -  07/01/2013 -
-  6 -        2 -  07/04/2013 -
-  7 -        1 -  07/05/2013 -
-------------------------------

Currently, my LINQ query looks like this:

// Retrieves parent records with NO children.
var q1 = myTable
    .Where(x => x.ParentId == null)
    .Except(myTable
                .Where(x => myTable
                                .Any(c => (c.ParentId == x.Id))));

// Retrieves most recent child records for each parentId
var q2 =
    (from a in myTable
     join b in
         (myTable.Where(a => a.ParentId != null)
                            .GroupBy(a => a.ParentId)
                            .Select(b => new { ParentId = b.Key, CreatedDate = b.Max(t => t.CreatedDate) }))
     on a.ParentId equals b.ParentId
     where a.CreatedDate == b.CreatedDate
     select a);

q1 = q1.Union(q2);

The back-end is using Npgsql2 with PostgreSQL. I am looking for a more elegant solution for this query. I am very new to LINQ and would like to optimize this.

Sorting code (sloppy, but jTable returns these strings):

if (string.IsNullOrEmpty(sorting) || sorting.Equals("Name ASC")) {
    q1 = q1.OrderBy(p => p.Customer.Name);
} else if (sorting.Equals("Name DESC")) {
    q1 = q1.OrderByDescending(p => p.Customer.Name);
} else if (sorting.Equals("Date ASC")) {
    q1 = q1.OrderBy(p => p.CreatedDate);
} else if (sorting.Equals("Date DESC")) {
    q1 = q1.OrderByDescending(p => p.CreatedDate);
}

Paging code:

var result = pageSize > 0
           ? q1.Skip(startIndex).Take(pageSize).ToList()
           : q1.ToList();

Answer 1

I can propose a different query, still in two phases

var firstQuery = myTable.Select(p => new { p.ID, ParentID = p.ParentID ?? p.ID, p.CreatedDate })
                                         .GroupBy( p => p.ParentID).Select( q => new
                                         {
                                            el = q.OrderByDescending( k => k.CreatedDate).Take(1)
                                         }).SelectMany(t => t.el);

        var result = dc.TabellaId_ParentId.Where(p => test.Select(q => q.ID).Contains(p.ID));

Answer 2

Use grouping:

Mock data:

public class Entry {
   public int Id { get; set; }
   public int? ParentId { get; set; }
   public DateTime Date { get; set; }   
};

 var list = new List<Entry> {
  new Entry{ Id = 1, ParentId = null, Date = new DateTime(2013, 7, 1) },
  new Entry{ Id = 2, ParentId = null, Date = new DateTime(2013, 7, 1) },
  new Entry{ Id = 3, ParentId = null, Date = new DateTime(2013, 7, 1) },
  new Entry{ Id = 4, ParentId = 1, Date = new DateTime(2013, 7, 2) },
  new Entry{ Id = 5, ParentId = 2, Date = new DateTime(2013, 7, 3) },
  new Entry{ Id = 6, ParentId = 2, Date = new DateTime(2013, 7, 4) },
  new Entry{ Id = 7, ParentId = 1, Date = new DateTime(2013, 7, 5) }
};

Query:

var query = from l in list
            group l by l.ParentId into g
            select new {
                Items = g.OrderBy(x => x.Date).Last()
            };

var res = query.OrderBy(x => x.Items.Id).Select(x => x.Items).ToList(); 

LinqPad result:

Id  ParentId  Date 
3   null      01.07.2013 0:00:00 
6   2         04.07.2013 0:00:00 
7   1         05.07.2013 0:00:00