对项目的实体框架导航属性伯爵一个排序约束ASP.NET的GridView [英] Sorting a bound ASP.NET GridView on the Count of items in an Entity Framework navigation property

问题描述

我有绑定到EntityDataSource GridView控件（参见下面的简化code）的ASP.NET页面。网格是显示的父项目的列表，包括一个栏，显示孩子的.Count之间的这一父。我可以得到网格正确显示计数，但我想不出用什么的ASP：的TemplateField SORTEX pression值可以设置排序对儿童计数

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.

下面是我的code样子（简化为清楚起见）...

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:

计数不是一个构件
  Transient.collection [FooEntities.Child（可空= TRUE，默认值=）。
  要提取集合元素的属性，使用子查询
  遍历集合。

'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.

我的问题是：如何启用排序的.Count之间的（）所组成的子对象的集合的导航属性的

是否有与SORTEX pression指定这个办法还是我打破，做我所有的传呼和手动排序？ （我会很明显preFER避免！）

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!)

推荐答案

我已复制了错误（在一个简单的例子），我认为这是不可能找到任何 SORTEX pression ，将执行对孩子的数量排序。

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:

• 当您单击列标题引发的异常是 EntitySqlException


• 在其中最后抛出异常的堆栈跟踪的最后一个方法是 EntityDataSourceView.ExecuteSelect（DataSourceSelectArguments参数）

• 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 是一个的抽象方法的DataSourceView 这是由各种特定数据源重写控制来执行实际工作，以将数据加载数据商店。在情况下的 EntityDataSource 相应的视图是 EntityDataSourceView 和抛出的异常 - 一个 EntitySqlException - 我的结论是的ExecuteSelect 建立使用查询的 实体SQL 。

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.

参数 DataSourceSelectArguments 包含定义的参数 EntityDateSource ，也是一个 SORTEX pression 这很可能你的模板列仅指定排序前pression。这些用于组成最终的查询在实体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.

我将认为 SORTEX pression 只是作为一个 ORDER通过了中的实体条款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

但是，这是无效的实体SQL。你可以用虚线路径，导航参考，但不能使用任何LINQ样的方法或属性（如计数）的实体SQL导航集合。

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.

有可能通过孩子们的计数写一个有效的实体SQL这样的排序。据<一个href=\"https://adonetsamples.svn.$c$cplex.com/svn/EntityFramework/Beta3/Entity%20Framework%20Query%20Samples/EntitySQLSamples.cs\"相对=nofollow>这个例子（搜索订购 - 相关实体的文件中）一个正确的实体的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"

（我知道这很难读，我就更不知道该怎么缩进code语义正确的。）

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

我觉得这个 anyelement的（SELECT ... 结构是子查询异常谈论并希望有才能算孩子们集合中的元素

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.

显然，你不能传递 p2.childCount 到 SORTEX pression 无子查询整个定义 P2 。

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

我的结论：没有希望找到工作 SORTEX pression 为孩子计数。

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

也许有不使用 SORTEX pression 办法 - 例如通过捕捉在标题点击事件，然后在事件手动构建完整的查询处理程序，但我真的不知道，是否以及如何是可能的。

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.

为什么在 EntityDataSource 和 GridView控件的消费者要弄清楚，自己呢？你有没有在任何地方看到记载：当数据源的类型为 EntityDataSource 的 SORTEX pression 的模板列在 GridView控件必须是一个有效的实体SQL ORDER BY 子句。的我没有。仅仅是这样的：在 SORTEX pression 是排序的前pression

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

不幸的是，因为它是无处说清楚与 SORTEX pression ，什么是正确的语法和支持什么样的前pressions发生了什么还是不行，这个答案是不止一个答案猜测。

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.

这篇关于对项目的实体框架导航属性伯爵一个排序约束ASP.NET的GridView的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！