Restrict ObjectSet to a related Entity and sort by a dynamic column name

Question

Given a Parent and a valid columnName, I want to find all the related Children, ordered by a dynamic column name. Here's how I thought my code would look:

Parent.                                             // EntityObject
Children.                                           // EntityCollection
Where(c => c.Gender == 'm').                        // IEnumerable
OrderBy(columnName, ListSortDirection.Ascending).   // -- not available --
Skip(pages * pageSize).Take(pageSize);

IEnumerable.OrderBy(string columnName) doesn't exist. Looking around to accomplish the "sort by dynamic column name", I started with this excellent-looking solution: How do I create an expression tree for run time sorting? , but this operates on an IQueryable

If it did, I assume it would bring the records over the wire to sort and diminish the performance of my pager anyway. So I reordered:

Repository.                                         // Repository
Children.                                           // ObjectSet
Where(c => c.Parent == Parent && c.Gender == 'm').  // ObjectQuery, runtime error
OrderBy(columnName, ListSortDirection.Ascending).   // IOrderedQueryable
Skip(pages * pageSize).Take(pageSize);

ObjectSet and ObjectQuery implement OrderBy(string columnName), and this code compiles, but yields the error:

Unable to create a constant value of type 'DataModel.Parent'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Of course, I can get the parent ID, but the Child.ParentReference is also a non-primitive type.

I can think of a few ways that would result in loading the entire recordset across the wire, but I feel like I must be missing something, because it must not be so hard to get a set of basic directive through to the database using all MS-centric technologies.

edit: pretend I'm http://en.wikipedia.org/wiki/Quiverfull , and need to paginate my children. :) edit2: clarified my need to query a dynamic column name.

score 1 · Answer 1 · answered Aug 09 '12 at 22:26

1

var parents = db.Parents; // Do whatever you need to get your unsorted collection from EF here.

if (sortBy == "Gender")
{
    parents = parents.OrderBy(p => p.Gender);
}
else if (sortBy == "FirstName")
{
    parents = parents.OrderBy(p => p.FirstName);
}

Now, this obviously isn't sorting on multiple columns, just a single column. And you can add in more logic for sort direction as well.

Edit: took out the crap about PredicateBuilder, I was going the wrong way when I started typing this answer, and forgot to take out the old stuff.

answered Aug 09 '12 at 22:26

Gromer

9,861
4
34
55

+1 Thanks for the link, but isn't the .Invoke in PredicateBuilder going to retrieve the record (even those I will Skip())? Also, I was just in the process of coding in a switch. The benefit is more compile-time checking. But I was hoping to slack on line of code instead. – shannon Aug 09 '12 at 22:36
Also, the switch statement still has the wire-performance problem of sorting an IEnumerable. – shannon Aug 09 '12 at 22:40
I think I'm missing something in your question? You're just trying to dynamically sort your data, correct? If so, what I proposed will do exactly that. – Gromer Aug 09 '12 at 22:51
1

I can also order it dynamically just by calling IEnumerable.AsQueryable.OrderBy(string), with no switch block, but that calls the whole recordset over the wire too. The problem is that the need to query a parent relation yields an IEnumerable. I've tried to clarify in my original question. – shannon Aug 09 '12 at 23:42

score 0 · Answer 2 · answered Aug 09 '12 at 21:58

0

Try replacing your OrderBy

OrderBy("age", ListSortDirection.Ascending).

with

OrderBy(x => x.Age).

also the Where

Where(c => c.Parent == Parent && c.Gender = 'm').

should read

Where(c => c.Parent == Parent && c.Gender == 'm').

answered Aug 09 '12 at 21:58

Daniel Elliott

22,647
10
64
82

Thank you. Unfortunately, by "run-time" selection of column name, I meant such as I would use in response to clicking a UI column header. I've updated my example to clarify. Also, thanks for noting the predicate typo in my first example. Fixed! – shannon Aug 09 '12 at 22:05

score 0 · Accepted Answer · edited May 23 '17 at 11:48

So there were a couple issues I was having, both mentioned in the question title.

Sorting by a run-time-selected, or dynamic, column name required some expression building. I used @Slace's popular extension method here.

That required an IQueryable. IQueryable works for me, because every time I was accidentally transforming my query into an enumerable, I was of course bringing all the results back over the wire before paging, which I was trying to avoid. But I still needed a way to get an IQueryable for results with a relationship to an entity I already held.

It was something simple I overlooked, just joining on the Entity.Id worked, and didn't result in redundant joins at the datasource. Not quite as object-oriented as I expected from EF, but it will do.

Repository.                                         // Repository
Children.                                           // ObjectSet
Where(c => c.Parent.Id == Parent.Id).               // ObjectQuery, works fine
OrderBy(columnName, ListSortDirection.Ascending).   // IOrderedQueryable
Skip(pages * pageSize).Take(pageSize);              // Only transfers 1 page

Restrict ObjectSet to a related Entity and sort by a dynamic column name

3 Answers3