C# – A lambda expression with a statement body cannot be converted to an expression tree(Lambda and Linq)

clambdalinqnet

Search A lot but did not find any suitable solution for me. I have introduced a variable in lambda expression and that's why showing this error message . Moreover I can solve the problem writing "SQL Like Linq Query" but this is not my expectation..My expectation is to introduce variable in "Linq Using Lambda Expression". Is it Possible or not??

A lambda expression with a statement body cannot be converted to an expression tree.

Here is my code: 

IQueryable<IGrouping<int, AnimalFood>> animalFoods = db.AnimalFoods.GroupBy(x => x.FoodId);
IQueryable<FoodSummaryViewModel> foodSummaryViewModel = animalFoods.Select(g =>
{
    var animalFood = g.FirstOrDefault();
    return new FoodSummaryViewModel()
    {
        FoodName = animalFood.Food.FoodName,
        FoodPrice = animalFood.Food.UnitPrice,
        TotalFoodQuantity = g.Sum(x => x.Animal.AnimalQuantity * x.FoodQuantity),
        TotalPrice = g.Sum(x => x.Animal.AnimalQuantity * x.FoodQuantity) * animalFood.Food.UnitPrice
    };
});
return foodSummaryViewModel.ToList();

Best Answer

The error message is quite clear: you have a lambda expression with a statement body, and that simply cannot be converted to an expression tree (at least not automatically by the compiler). Because of that, your linq provider cannot create a query from it to send to the database (and even if you created the expression tree manually, which is not trivial, your linq provider would not be able to convert it to a SQL query).

You have two options. Option one is to rewrite your query such that it does not contain a statement body, as others have shown.

The other option is to execute part of the query in memory, using linq to objects. You have to be careful with this approach, and avoid getting too much data from the database. But the way to do it would be:

IEnumerable<IGrouping<int, AnimalFood>> animalFoods = 
    db.AnimalFoods.GroupBy(x => x.FoodId).AsEnumerable();
IEnumerable<FoodSummaryViewModel> foodSummaryViewModel = animalFoods.Select(g =>
{
    var animalFood = g.FirstOrDefault();
    return new FoodSummaryViewModel()
    {
        FoodName = animalFood.Food.FoodName,
        FoodPrice = animalFood.Food.UnitPrice,
        TotalFoodQuantity = g.Sum(x => x.Animal.AnimalQuantity * x.FoodQuantity),
        TotalPrice = g.Sum(x => x.Animal.AnimalQuantity * x.FoodQuantity) * animalFood.Food.UnitPrice
    };
});
return foodSummaryViewModel.ToList();

That may give you what you think you want, but it may not be a good idea though. Your selector is using the AnimalFood.Animal.AnimalQuatity property chain, and that may cause lazy loading, depending on your Linq provider. And if you have configured it to use eager loading, you may not be better of, because you may be loading way too much data.

So you probably would be better of rewriting your query. Are you sure something like this doesn't do the job:

var q = from food in db.Foods
        select new FoodSummaryViewModel
        {
            FoodName = food.FoodName,
            FoodPrice = food.UnitPrice,
            TotalFoodQuantity = (from fa in food.AnimalFoods 
                                 select fa.Animal.AnimalQuantity).Sum() * food.FoodQuantity
            TotalPrice = (from fa in food.AnimalFoods 
                          select fa.Animal.AnimalQuantity).Sum() * food.FoodQuantity * food.UnitPrice
        };
return q.ToList();
Related Topic