Sort related ASP.NET GridView by number of elements in Entity Framework navigation function

Question

I have an ASP.NET page with a GridView control associated with an EntityDataSource (see simplified code below). The grid displays a list of Parent elements and includes a column to display .Count Children for this parent. I can get a grid to show the score correctly, but I can’t figure out what to use for asp: TemplateField SortExpression to be able to set the sort on the number of children.

This is what my code looks like (simplified for clarity) ...

 <asp:EntityDataSource ID="edsParentList" runat="server" ConnectionString="name=FooEntities" DefaultContainerName="FooEntities" EnableFlattening="False" EntitySetName="Parents" EntityTypeFilter="Parent" Include="Children" OrderBy="it.Name" Where="(it.Name LIKE '%' + @ParentNameLike + '%') > <WhereParameters> <asp:Parameter Name="ParentNameLike" Type="String" DefaultValue="_" /> </WhereParameters> </asp:EntityDataSource> <asp:GridView ID="grdParents" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="edsParentList" PageSize="20" onpageindexchanged="grdParents_PageIndexChanged" onsorted="grdParents_Sorted" > <Columns> <asp:TemplateField HeaderText="Name" SortExpression="Name"> <ItemTemplate> <a href="Parent.aspx?id=<%# Eval("ParentID") %>"><%# Eval("Name") %></a> </ItemTemplate> </asp:TemplateField> <asp:BoundField DataField="BirthDate" HeaderText="Birth Date" DataFormatString="{0:yyyy-MM-dd HH:mm}" SortExpression = "BirthDate" /> <asp:TemplateField HeaderText="Children" SortExpression="Children.Count"> <ItemTemplate> <asp:Label ID="lblChildCount" runat="server" Text='<%# Eval("Children.Count") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> 

This displays the grid in order. However, when I click on the column heading "Children", this error occurs:

'Count' is not a member of 'Transient.collection [FooEntities.Child (Nullable = True, DefaultValue =)]'. To retrieve a property of a collection item, use a subquery to iterate through the collection.

My question is: How do I enable sorting in .Count () of a navigation property consisting of a set of child objects?

Is there a way to specify this using SortExpression, or do I need to break and complete all my search calls and manual sorting? (Which I obviously would prefer to avoid!)

Answer 1

In your position, since entity classes are declared as partial, I would try to create an additional incomplete class code file for the parent and add the Read-Only property of ChildCount. This property will refer to the navigation property for children. Then I would figure it out.

I assume that EntityDataSource can work with what I will call derived properties for the object. I have not tested this.

Answer 2

I reproduced the error (in a simpler example), and I find it impossible to find a SortExpression that would sort by the children’s score.

I saw two important additional data:

• The exception you selected when you clicked on the column heading is EntitySqlException
• The last method in the stack trace, which ultimately throws an exception: EntityDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)

ExecuteSelect is an abstract DataSourceView method that is overridden by various control elements of the data source to do the actual work of loading data from the data warehouse. In the case of EntityDataSource corresponding view is EntityDataSourceView and from the exception throw - a EntitySqlException - I would conclude that ExecuteSelect constructing the query using Entity SQL .

The DataSourceSelectArguments argument contains the parameters defined in the EntityDateSource as well as the SortExpression , which is most likely just the sort expression you specify in the TemplateField . They are used to compose the final query in Entity SQL.

I would suggest that SortExpression simply passed as an ORDER BY in an Entity SQL statement. It will look like this:

 ORDER BY Children.Count 

But this is not valid Entity SQL. You can use dotted paths to refer to navigation, but you cannot use any “LINQ-like” methods or properties (like Count ) of the navigation collection in Entity SQL.

You can write a valid SQL query, sort by the number of children. According to this example (search in "Order By - Related Entities" in the file) the correct Entity SQL statement is:

 "SELECT VALUE p2.p FROM (SELECT p, ANYELEMENT(SELECT VALUE Count(c.ChildId) FROM p.Children AS c) AS childCount FROM Parents AS p) AS p2 ORDER BY p2.childCount" 

(It's so hard to read that I don’t even know how to reverse the code semantically correct.)

I think this ANYELEMENT(SELECT... construct ANYELEMENT(SELECT... is the "subquery" in question, and wants to have in order to count the elements of the children's collection.

Obviously, you cannot pass p2.childCount to SortExpression without the entire subquery that defines p2 .

My conclusion: there is no hope of finding a working SortExpression for counting children.

Perhaps there is a way without using SortExpression - for example, by catching a click event on the header, and then building the complete request manually in the event handler, but I really don't know if and how it is possible.

Why should consumers of EntityDataSource and GridView figure this out themselves? You have seen somewhere documented: "When the data source is of type EntityDataSource , the SortExpression TemplateField in the GridView must be a valid Entity SQL ORDER BY ." I haven't done that. Just something like: " SortExpression is an expression for sorting."

Unfortunately, since nowhere is it clear what is happening with SortExpression , what is the correct syntax and which expressions are supported or not, this answer is more of an assumption than an answer.