Using First() with OrderBy and DynamicQuery in One-To-Many related tables

Question

Note: I realize this question is similar to another question, however that question was asked in the context of Genom-e and remains unanswered. My question is in the context of LINQ DynamicQuery.

I'm using the String extension method overload for OrderBy provided by System.Linq.Dynamic. Passing a String Literal to OrderBy works great. I can sort on primary entity fields ie: person.OrderBy("LastName") and I can sort on children entity fields as long as there is a one-to-one relationship between the parent and child ie: person.OrderBy("Mother.LastName").

However, I am unable to sort on a child's field if there is a one-to-many relationship with the parent and child ie: person.OrderBy("Children.LastName"). This throws an error: No property or field 'Children' exists in type 'Person'.

Obviously this fails because the interpreter doesn't know which child's LastName I'm trying to use in the sort operation. I can solve this problem easily by creating an expression like this (Function(p As Person) p.Children.First.LastName).

But how can I get similar First behavior when using the string literal extension of OrderBy? Ultimately I want to be able to do something like this: person.OrderBy("it.Lastname desc, Children.First().FirstName asc")

Edit: I'm using the Repository pattern and this is what my Find function looks like:

Public Function Find(ByVal predicate As Expression(Of Func(Of TEntity, Boolean)), ByVal orderBy As String, ByVal skip As Integer, ByVal take As Integer) As IEnumerable(Of TEntity) Implements ILinqSqlRepository(Of TEntity, TContext).Find

        If String.IsNullOrEmpty(orderBy) Then
            Return Find(predicate, skip, take)
        Else
            Dim qry = Context.GetTable(Of TEntity)().Where(predicate).OrderBy(orderBy).Skip(skip).Take(take)
            Return qry.ToList()
        End If

    End Function

Edit: This is the relationship between Person and Child tables:

Person Table
  PersonId (pk)
  MotherId (fk to Mother Table)
  LastName
  FirstName

Child Table
  ChildId (pk)
  PersonId (fk to Person Table)
  LastName
  FirstName

Mother Table
  MotherId (pk)
  LastName
  FirstName

Each Person may have 0 or more Children. You can see there can be 0 or 1 Mother rows for each Person.

Obviously these are not the exact table names I'm using in my real project but you can see how relationships between Person and Mother, and with Person and Child are different.

Selector
I've tried overloading Find() to accept a Selector which selects p = Person and child = First().Child but the return type changes to Anonymous, instead of the Generic type. Is there some way to use the Selector in the OrderBy process but still return a IEnumerable(of TEntity)?

Answer 1

You could do (C#, sorry...):

people.Select(p => Person = p, Child = p.Children.First())
      .OrderBy("Child.LastName")
      .Select(p => p.Person);

BTW, without "it. you're using System.Linq.Dynamic, and with "it. you're using ESQL. Either one is OK, but you should not use both!