Get a subdirectory in the form of List <class>

Question

I have a LINQ to EF query that returns data in the form of a class. The class has a List<RecipeCategories> property that I need to populate. The RecipeCategories table is the relationship table between the recipe table and the RecipeCategories table and can be many. I found enough information to get the code to compile, but these are runtime errors, and I could not figure out how to do this correctly.

 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 am getting.

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

The part I'm working on is the line.

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

RecipeCategories is a property of List<RecipeCategoryItem> .

I'm trying to do this, and if so, how?

Thanks.

Answer 1

You call ToList inside what turns into a larger request. Remove the .ToList () call.

The problem is that everything in your query turns into a large expression tree, which the Entity Framework is trying to translate into an SQL statement. "ToList" doesn't make sense from an SQL perspective, so you shouldn't call it anywhere inside your query.

In most cases, you want to call ToList in your general query before returning it to make sure that the query is evaluated and the results are loaded into memory. In this case, you return only one object, so calling First does essentially the same thing.

How important is RecipeCategories to List<RecipeCategoryItem> ? If you can make it IEnumerable, then you can remove the ToList call without any problems.

If you absolutely need to have a List , you first need to extract all the information using the initial Entity Framework query and anonymous types (without calling ToList), and then convert the received data to the type of object you want before returning it.

Or you can build your RecipeInfo object in parts of several queries, for example:

 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 result in two database outages, but will result in less data being sent to the wire.

Answer 2

I think I have a solution to the problem. Use a dynamic type.

 public class BoxImageViewDetailDto { public Guid PropertyId { get; set; } public string Title { get; set; } public string SubTitle { get; set; } public string Description { get; set; } public string SubDescription { get; set; } public decimal? PropertyValue { get; set; } public byte? UnitsFloor { get; set; } public dynamic ImagensRowsVar { get; set; } public List<ImageViewDto> ImagensRows { get { return (List<ImageViewDto>)this.ImagensRowsVar; } } public int ImagensRowsTotal { get; set; } } CorretorDaVez.DTO.UserControls.BoxImageViewDetailDto c = (from p in entities.rlt_Property join pc in entities.rlt_PropertyPicture on p.PropertyId equals pc.PropertyId where p.PropertyId == propertyId orderby p.CreateDate descending select new CorretorDaVez.DTO.UserControls.BoxImageViewDetailDto { PropertyId = p.PropertyId, Title = p.Title, PropertyValue = p.PropertyValue, Description = p.Description, UnitsFloor = p.UnitsFloor, ImagensRowsTotal = p.rlt_PropertyPicture.Count, ImagensRowsVar = p.rlt_PropertyPicture.Select(s => new CorretorDaVez.DTO.UserControls.ImageViewDto { PropertyId = p.PropertyId, ImagePath = pc.PhotoUrl}) }).FirstOrDefault(); 

Answer 3

to try:

 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, RecipeCategories = from rc in recipeData.RecipeCategories where rc.Recipes.Any(r => r.ID == recipeId) select new RecipeCategoryItem{ Id = rc.ID, CategoryName = rc.categoryName } ).First();
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, RecipeCategories = from rc in recipeData.RecipeCategories where rc.Recipes.Any(r => r.ID == recipeId) select new RecipeCategoryItem{ Id = rc.ID, CategoryName = rc.categoryName } ).First();