Sorting a bound ASP.NET GridView on the Count of items in an Entity Framework navigation property

Question

I have an ASP.NET page with a GridView control bound to an EntityDataSource (see simplified code below). The grid is showing a list of Parent items and includes a column to show the .Count of Children for this parent. I can get the grid to show the count properly, but I can't figure out what to use for the asp:TemplateField SortExpression value to be able to set the sort to the count of children.

Here 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 fine. However, when I click on the header of the Children column, this error is thrown:

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

My question is: How do I enable sorting on the .Count() of a navigation property which consists of a collection of child objects?

Is there a way to specify this with SortExpression or do I have to break down and do all my paging and sorting manually? (Which I'd obviously prefer to avoid!)

Answer 1

In your position, since the entity classes are declared as partial, I would try creating a supplemental partial class code file for the Parent entity, and add a ChildCount read-only property. This property would reference the Children navigation property. I would then sort on that.

I am assuming, here, that an EntityDataSource can work with what I'll call derived properties on an entity. I have not tested this.

Answer 2

I have reproduced the error (in a simpler example) and I believe it is not possible to find any SortExpression that would perform a sorting on the children's count.

I have seen two important additional information:

• The exception thrown when you click on the column header is an EntitySqlException
• The last method in the stack trace which finally throws the exception is EntityDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)

ExecuteSelect is an abstract method of DataSourceView which is overriden by the various specific data source controls to perform the actual work to load data from the data store. In case of the EntityDataSource the corresponding view is the EntityDataSourceView and from the exception thrown - an EntitySqlException - I would conclude that ExecuteSelect builds the query using Entity SQL.

The argument DataSourceSelectArguments contains parameters defined in the EntityDateSource and also a SortExpression which is very likely just the sort expression you specified on the TemplateField. Those are used to compose the final query in Entity SQL.

I would assume that the SortExpression is just passed as an ORDER BY clause in the Entity SQL statement. This would look like:

ORDER BY Children.Count

But this is invalid Entity SQL. You can use dotted paths for a navigation reference, but you cannot use any "LINQ-like" methods or properties (like Count) of a navigation collection in Entity SQL.

It is possible to write a valid Entity SQL so sort by the children's count. According to this example (search for "Order By - Related Entities" in the file) a correct Entity SQL statement would be:

"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"

(This is so hard to read that I even don't know how to indent the code semantically correct.)

I think this ANYELEMENT(SELECT... construct is the "subquery" the exception is talking about and wants to have in order to count the elements of the children collection.

Obviously you can't pass p2.childCount into SortExpression without the whole subquery which defines p2.

My conclusion: There is no hope to find a working SortExpression for the children count.

Maybe there is a way without using the SortExpression - for example by catching a click event on the header and then building the complete query manually in the event handler, but I do not really know, if and how it's possible.

Why do consumers of the EntityDataSource and GridView have to figure out that themselves? Did you anywhere see documented: "When the data source is of type EntityDataSource the SortExpression of a TemplateField in a GridView must be a valid Entity SQL ORDER BY clause." I didn't. Just something like: "The SortExpression is an expression for sorting."

Unfortunately, because it is nowhere clearly said what happens with the SortExpression, what is the correct syntax and what kind of expressions are supported or not, this answer is more a guess than an answer.