Entity Framework and dynamic order by statements

Question

I have been struggling to get this working. I wish to have an EF statement take in a column to order by. My original statement was this:

var Query = from P in DbContext.People
                   where P.BusinessUnits.Any(BU =>BU.BusinessUnitID == businessUnitId)
                   orderby P.LastName
                   select P;

And I changed this to the following:

var Query = from P in DbContext.People
                   where P.BusinessUnits.Any(BU =>BU.BusinessUnitID == businessUnitId)
                   orderby sortField
                   select P;

Where sortField is the column we wish to sort on, and is a string i.e. LastName. However, it does not appear to work, it does no sorting, and the outputted SQL string is completely wrong. Anyone got this working before?

Answer 1

you could try passing in an expression to your method with the following type:

Expression<Func<Person, object>> expr = p => p.LastName;

and then using linq extensions instead of linq expressions...

var Query = 
 DbContext.People
 .Where(P => P.BusinessUnits.Any(BU =>BU.BusinessUnitID == businessUnitId))
 .OrderBy(expr)
 .ToList();

Answer 2

Your sort does not work because you are sorting on a string literal. It is not illegal, but it is not particularly useful either. You need to provide a sorting field through the API of IQueryable<T>, for example, like this:

var q = from P in DbContext.People
   where P.BusinessUnits.Any(BU =>BU.BusinessUnitID == businessUnitId)
   orderby P.LastName
   select P;
if ("sortField".Equals("FirstName"))
    q = q.OrderBy(p => p.FirstName);
else if ("sortField".Equals("LastName"))
    q = q.OrderBy(p => p.LastName);
else if ("sortField".Equals("Dob"))
    q = q.OrderBy(p => p.Dob);