How do I sort on a column that isn't in the database with LINQ to entities?

Question

LINQ to Entities 3.5 doesn't support String.Join so I'm binding my gridview to a property I define outside the Select statement. Obviously it's not letting me sort on RecipientNames because it's just an IEnumerable and that doesn't make sense. How can I use LINQ to Entities to sort on my new column? If possible I'd like to get rid of RecipientNamesList altogether and create something that LINQ will be able to handle for sorting.

IQueryable<NotificationDetail> resultsFlattened = results.Select(n => new NotificationDetail() 
{
..
RecipientNames = n.NotificationRecipients.Select(nr => nr.Recipient.RecipientNameFirst + " " + nr.Recipient.RecipientNameLast).Where(s => s.Trim().Length > 0)});
});

IQueryable<NotificationDetail> resultsPaged = ApplySortingPaging(resultsFlattened,SortPageOptions);

return resultsPaged.ToEntityList(results.Count()); //blows up here, obviously

public string RecipientNamesList
{
    get
    {
          return String.Join(", ", RecipientNames.ToArray());
    }
}

your code format is horrible, looks like some closures are missing as well, however as Danil said, OrderBy should suffice — Kris Ivanov
You can do complex order by scenarios also, as long as the result implements IComparable. — Nick Larsen

Geoff Appleford Geoff Appleford · Accepted Answer · 2011-03-01T13:45:43

It blows up because the sort cannot be translated into SQL. The easy solution is to make sure you perform the sort locally and not in SQL Server. Just add a ToList or AsEnumerable after the first query.

resultsFlattened = resultsFlattened.ToList();

You'll want to ensure that you do your paging before this, otherwise you could be pulling down a large number of rows from the database.

Or you use the Linq.Translations library developed by by Damien Guard and others. It enables you to use local, calculated properties just as you require.

How do I sort on a column that isn't in the database with LINQ to entities?

1 Answers