在获取列表与LT子集;类>形成 [英] Get sub collection in List<class> form

问题描述

我有一个LINQ到EF查询中的一类形式返回数据。这个类有一个列表与LT; RecipeCategories>我需要填充属性。该RecipeCategories表是配方表和RecipeCategories表之间的关系的表，并且可以是多对多。我找到足够的信息来让代码编译，但在运行时错误，我一直无法弄清楚如何得到这个权利。

I have a LINQ to EF query that returns data in a class form. The class has a List<RecipeCategories> property that I need to populate. The RecipeCategories table is a relationship table between the Recipe table and the RecipeCategories table, and can be many to many. I found enough information to get the code to compile, but it errors at runtime and I haven't been able to figure out how to get this right.

ri = (from r in recipeData.Recipes
              where r.ID == recipeId
              select new RecipeItem
              {
                  Id = r.ID,
                  ProductId = r.Product.ID,
                  RecipeName = r.recipeName,
                  RecipeDescription = r.recipeDescription,
                  Servings = r.servings.HasValue ? r.servings.Value : 0,
                  CreatedDate = r.createdDate,
                  PrepTime = r.prepTime.HasValue ? r.servings.Value : 0,
                  CookTime = r.cookTime.HasValue ? r.servings.Value : 0,
                  Approved = r.approved,
                  RecipeInstructions = r.recipeInstructions,
                  RecipeIngredients = r.recipeIngredients,
                  RecipeCategories = r.RecipeCategories.Select(i => new RecipeCategoryItem { Id = i.ID, CategoryName = i.categoryName }).ToList()
              }).First();

这是我的错误。

LINQ到实体无法识别方法System.Collections.Generic.List 1 [RecipeCategoryItem]了ToList [RecipeCategoryItem]（System.Collections.Generic.IEnumerable 1 [RecipeCategoryItem]）的方法，而这种方法不能被翻译成店的表情。

LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[RecipeCategoryItem] ToList[RecipeCategoryItem](System.Collections.Generic.IEnumerable1[RecipeCategoryItem])' method, and this method cannot be translated into a store expression.

这是我的一部分。工作是这一行

The part that I am working on is this line.

RecipeCategories = r.RecipeCategories.Select(i => new RecipeCategoryItem { Id = i.ID, CategoryName = i.categoryName }).ToList()

RecipeCategories是清单< RecipeCategoryItem方式> 属性

RecipeCategories is a List<RecipeCategoryItem> property.

就是我试图做的可能的，如果是这样，如何

Is what I am trying to do possible, and if so, how?

感谢您。

推荐答案

您正在调用了ToList里面得到什么变成了一个大的查询。取下调用.ToList（）。

You're calling ToList inside of what gets turned into a larger query. Remove the call to .ToList().

的问题是，一切都在您的查询被变成了一个大的表达式树，实体框架试图转换成SQL语句。 了ToList没有从SQL的角度来看的意义，所以你不应该在任何地方的称之为的内部查询。

The problem is that everything in your query gets turned into a big expression tree, which Entity Framework tries to translate into a SQL statement. "ToList" has no meaning from a SQL standpoint, so you shouldn't call it anywhere inside your query.

在大多数情况下，想返回之前调用了ToList您的整体查询，以确保查询评价结果被加载到内存中。在这种情况下，你只返回一个对象，因此调用首先做本质上是一回事。

In most cases, you want to call ToList on your overall query before returning it, to ensure that the query is evaluated and the results are loaded into memory. In this case, you're only returning one object, so the call to First does essentially the same thing.

如何重要的是它的RecipeCategories是列表与LT; RecipeCategoryItem> ？如果你可以把它的IEnumerable相反，那么你就可以删除调用了ToList 没有任何问题。

How important is it that RecipeCategories be a List<RecipeCategoryItem>? If you could make it IEnumerable instead, then you can remove the call to ToList without any problem.

如果它是绝对必要的，你有一个列表，那么你首先需要拉在使用初始实体框架查询和匿名类型（而不调用了ToList）中的所有信息，然后转换。您收到到你返回之前想要的对象类型的数据。

If it's absolutely necessary that you have a List, then you will first need to pull all the information in using an initial Entity Framework query and anonymous types (without calling ToList), and then convert the data you receive into the object type you want before returning it.

或者你可以建立自己的RecipeInfo来自多个查询的零碎对象，像这样：

Or you could build your RecipeInfo object piecemeal from multiple queries, like so:

var ri = (from r in recipeData.Recipes
            where r.ID == recipeId
            select new RecipeItem
            {
                Id = r.ID,
                ProductId = r.Product.ID,
                RecipeName = r.recipeName,
                RecipeDescription = r.recipeDescription,
                Servings = r.servings.HasValue ? r.servings.Value : 0,
                CreatedDate = r.createdDate,
                PrepTime = r.prepTime.HasValue ? r.servings.Value : 0,
                CookTime = r.cookTime.HasValue ? r.servings.Value : 0,
                Approved = r.approved,
                RecipeInstructions = r.recipeInstructions,
                RecipeIngredients = r.recipeIngredients,
            }).First();
var rc = from c in recipeData.RecipeCategories
         where c.Recipes.Any(r => r.ID == recipeId)
         select new RecipeCategoryItem 
         {
            Id = c.ID, CategoryName = c.categoryName
         };
ri.RecipeCategories = ri.ToList();

请注意，最后一个示例将导致两个数据库人次，但会导致整个发送数据较少丝。

Note that this last example will cause two database trips, but will cause less data to be sent across the wire.

这篇关于在获取列表与LT子集;类>形成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！