Get sub collection in List<class> form

Question

Hello,

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();

This is the error I get.

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 is a List<RecipeCategoryItem> property.

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

Thank you.

Answer 1

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

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.

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.

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.

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.

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.