LInq-to-sql dynamic sorting problem

Question

I have a linq-to-sql query over entity that has child entityset that I need to sort on some child fields, i.e. use this query:

   var query = from p in context.Patients 
            let order = p.Lab_Orders.First() 
            orderby order.Order_Date 
            select p; 

This query runs fine, but how would I modify it to use DLINQ OrderBy method what would I pass as a sorting parameter in run-time?

Answer 1

If by DLINQ you mean Dynamic Query, then you can't use the query expressions like that, you have to use extension methods with lambdas. You can start with a query expression but you have to eventually switch it over to lambda:

IEnumerable<Patient> GetPatients(string orderSortField)
{
    var query =
        from p in context.Patients
        select new
        {
            Patient = p,
            FirstOrder = p.Lab_Orders.First()
        };
    return p.OrderBy(orderSortField).Select(p => p.Patient);
}

Call it with:

var patientsByOrderDate = GetPatients("FirstOrder.Order_Date");

Answer 2

Try to add the "OrderBy" to the expression tree:

var query = from p in context.Patients 
            let order = p.Lab_Orders.First() 
            select p; 
var x = Expression.Parameter(query.ElementType, "x");
string sortName = "order.Order_Date";
var selector = Expression.Lambda(Expression.PropertyOrField(x, sortName), x);
query = query.Provider.CreateQuery(
         Expression.Call(typeof(Queryable), "OrderBy", 
              new Type[] { query.ElementType, selector.Body.Type },
               query.Expression, selector)
         ) as IQueryable<Patients>;

Needs the namespace "System.Linq.Expressions".

Answer 3

Use the AsQueryable() after the initial statement -

var query = from p in context.Patients
            let order = p.Lab_Orders.First()        
            select p; 

query = query.AsQueryable().OrderBy(x => x.Lab_Orders.First().OrderDate);