实体框架，MVC 3，排序依据的LINQ到实体 [英] Entity Framework, MVC 3, OrderBy in LINQ To Entities

问题描述

我有以下查询：

  model.Page = db.Pages
    。凡（P =＆GT; p.PageId ==编号）
    .INCLUDE（P =＆GT; P.系列车
                   。选择（C =＆GT; c.Comics
                                 。选择（COL =＆GT; col.Collection）））
    .SingleOrDefault（）;
 

这个伟大的工程，但我现在需要通过一个名为'ReadingOrder'。酒店责令漫画

我试过：

  model.Page = db.Pages
    。凡（P =＆GT; p.PageId ==编号）
    .INCLUDE（P =＆GT; p.Series.Select（C =＆GT; c.Comics.OrderBy（O = GT; o.ReadingOrder）
                          。选择（COL =＆GT; col.Collection）））
    .SingleOrDefault（）;
 

不过，这将导致以下错误：

  

包含路径前pression必须指向一个导航属性
  的类型来定义。借鉴导航路径点
  属性和集合导航选择运营商
  属性。参数名称：路径

任何想法此错误意味着什么？

在此先感谢

编辑：

我的模型：

 公共类页
{
    公众诠释的PageId {搞定;组; }
    公共字符串名称{搞定;组; }
    公众的ICollection＆LT;＆系列GT;系列{搞定;组; }
}公共类系列
{
    公众诠释SeriesId {搞定;组; }
    公众诠释的PageId {搞定;组; }
    公共字符串名称{搞定;组; }
    公共页面{搞定;组; }
    公众的ICollection＆LT;漫画＆GT;漫画{搞定;组; }
}公共类漫画
{
    公众诠释ComicId {搞定;组; }
    公共字符串名称{搞定;组; }
    公众诠释ReadingOrder {搞定;组; }
    公共字符串字幕{搞定;组; }
    公众诠释CollectionId {搞定;组; }
    公共收藏{搞定;组; }}公共类集合
{
    公众诠释CollectionId {搞定;组; }
    公共字符串名称{搞定;组; }
    公众的ICollection＆LT;漫画＆GT;漫画{搞定;组; }
}
 

解决方案

例外的 ...包含路径前pression必须指向一个导航属性​​... 的基本抱怨 c.Comics.OrderBy 不是一个导航属性​​。 （这是一个合法的投诉，我想。）

其实这不是由EF支持的应用排序（和过滤）的预先加载语句（包含）。

那么，你该怎么办？

选项1：

排序在内存中完成装载后的实体：

model.Page = db.Pages
    。凡（P =＆GT; p.PageId ==编号）
    .INCLUDE（P =＆GT; p.Series.Select（C =＆GT; c.Comics
                          。选择（COL =＆GT; col.Collection）））
    .SingleOrDefault（）;如果（model.Page！= NULL）
{
    的foreach（在model.Page.Series VAR系列）
        series.Comics = series.Comics.OrderBy（C =＆GT; c.ReadingOrder）.ToList（）;
}


丑陋，而是因为你是通过ID载入显然只有一个页对象是可能更快（LINQ到内存中的对象），比下面的选项（如系列和漫画集合是不是特别长）。

选项2：

细分查询零件其中混合渴望和explicite加载：

model.Page = db.Pages
    。凡（P =＆GT; p.PageId ==编号）
    .INCLUDE（P =＆GT; P.系列车）//只收集系列包括
    .SingleOrDefault（）;如果（model.Page！= NULL）
{
    的foreach（在model.Page.Series VAR系列）
        db.Entry（系列）.Collection（S = GT; s.Comics）.Query（）
          .INCLUDE（C =＆GT; c.Collection）
          .OrderBy（C =＆GT; c.ReadingOrder）
          。加载（）; //一种新的数据库查询中循环每个系列
}


选项3：

投影？

<一个href=\"http://stackoverflow.com/questions/5521749/how-many-include-i-can-use-on-objectset-in-entityframework-to-retain-performance/5522195#5522195\">Here和<一个href=\"http://stackoverflow.com/questions/6848634/entity-framework-include-extension-returns-a-ton-of-data/6849124#6849124\">here是一些关于复的危害方式包括多个导航属性​​的链。它可以装载复制大量的数据。 包含确保您只有一个数据库往返，但可能在更多的转移数据的成本。 Explicite装载有多次往返，但在总的可能更少的数据。

（我知道，我给你这包括...选择...选择...选择...链条，但我怎么能知道你会带我认真:)。嗯，这取决于您的收藏嵌套的大小，它仍然是最好的选择。）

I've got the following query:

model.Page = db.Pages
    .Where(p => p.PageId == Id)
    .Include(p => p.Series
                   .Select(c => c.Comics
                                 .Select(col => col.Collection)))
    .SingleOrDefault();

This works great, although I now need to order the Comics by a property called 'ReadingOrder'.

I've tried:

model.Page = db.Pages
    .Where(p => p.PageId == Id)
    .Include(p => p.Series.Select(c => c.Comics.OrderBy(o => o.ReadingOrder)
                          .Select(col => col.Collection)))
    .SingleOrDefault();

But this results in the following error:

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path

Any ideas what this error means?

Thanks in advance

EDIT:

My models:

public class Page
{
    public int PageId { get; set; }
    public string Title { get; set; }
    public ICollection<Series> Series { get; set; }
}

public class Series
{
    public int SeriesId { get; set; }
    public int PageId { get; set; }
    public string Title { get; set; }
    public Page Page { get; set; }
    public ICollection<Comic> Comics { get; set; }
}

public class Comic
{
    public int ComicId { get; set; }
    public string Title { get; set; }
    public int ReadingOrder { get; set; }
    public string Subtitle { get; set; }
    public int CollectionId { get; set; }
    public Collection Collection { get; set; }

}

public class Collection
{
    public int CollectionId { get; set; }
    public string Title { get; set; }
    public ICollection<Comic> Comics { get; set; }
}

解决方案

The exception "...Include path expression must refer to a navigation property..." basically complains that c.Comics.OrderBy is not a navigation property. (It's a legitimate complaint, I think.)

Actually it's not supported by EF to apply sorting (and also filtering) in eager loading statements (Include).

So, what can you do?

Option 1:

Sort in memory after you have loaded the entity:

model.Page = db.Pages
    .Where(p => p.PageId == Id)
    .Include(p => p.Series.Select(c => c.Comics
                          .Select(col => col.Collection)))
    .SingleOrDefault();

if (model.Page != null)
{
    foreach (var series in model.Page.Series)
        series.Comics = series.Comics.OrderBy(c => c.ReadingOrder).ToList();
}

Ugly, but because you are loading apparently only a single Page object by id it's possibly faster (LINQ to Objects in memory) than the following options (if Series and Comics collections are not extraordinarily long).

Option 2:

Break down the query in parts which mix eager and explicite loading:

model.Page = db.Pages
    .Where(p => p.PageId == Id)
    .Include(p => p.Series) // only Series collection is included
    .SingleOrDefault();

if (model.Page != null)
{
    foreach (var series in model.Page.Series)
        db.Entry(series).Collection(s => s.Comics).Query()
          .Include(c => c.Collection)
          .OrderBy(c => c.ReadingOrder)
          .Load(); // one new DB query for each series in loop
}

Option 3:

Projection?

Here and here is by the way something about the dangers of complex Include chains of multiple navigation properties. It can load huge amounts of duplicated data. Include ensures that you only have one DB roundtrip but possibly at the cost of much more transfered data. Explicite loading has multiple roundtrips but with possibly less data in total.

(I know, I gave you this Include...Select...Select...Select... chain, but how could I know that you would take me serious :). Well, depending on the size of your nested collections it can still be the best option.)

这篇关于实体框架，MVC 3，排序依据的LINQ到实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！