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

Question

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

Possible duplicate of ["A lambda expression with a statement body cannot be converted to an expression tree"](http://stackoverflow.com/questions/5179341/a-lambda-expression-with-a-statement-body-cannot-be-converted-to-an-expression) — Kris Vandermotten, Sep 17 '16 at 08:41

Kris Vandermotten · Accepted Answer · 2016-09-17T08:45:03.643

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

Thanks a lot for your explanation!! – TanvirArjel Sep 17 '16 at 08:20 — TanvirArjel, Sep 17 '16 at 08:20

Zein Makki · Answer 2 · 2016-09-17T08:20:40.940

1

You can't write a multi-line method inside a linq-2-db query, because this multi-line method can't be transformed into an expression tree than can interpreted by the provider and thus converted into pure SQL statement. You can do this:

var results = (from f in animalFoods
              group f by f.FoodId into groups
              let animalFood = groups.First()
              select new FoodSummaryViewModel()
              {
                 FoodName = animalFood.Food.FoodName,
                 FoodPrice = animalFood.Food.UnitPrice,
                 TotalFoodQuantity = groups.Sum(x => x.Animal.AnimalQuantity * x.FoodQuantity),
                 TotalPrice = groups.Sum(x => x.Animal.AnimalQuantity * x.FoodQuantity) * animalFood.Food.UnitPrice
              }).ToList();

edited Sep 17 '16 at 08:20

answered Sep 17 '16 at 07:44

Zein Makki

29,485
6
52
63

The error OP is getting has nothing to do with db query translation. It's a compile time error. – Ivan Stoev Sep 17 '16 at 07:48
Thanks!!..I know this solution..This is not my Expectation..I would like to introduce variable in "Linq using Lamda expression" not in "SQL Like Linq query" – TanvirArjel Sep 17 '16 at 07:49
@IvanStoev the error is *A lambda expression with a statement body cannot be converted to an expression tree* . Why do you think this compile error was ever created ? – Zein Makki Sep 17 '16 at 08:02
@TanvirArjel You can convert the answer to Method-Syntax but it is not readable and very hard to maintain ? And what do you gain by doing that ? The compiler is going to convert the above syntax to method syntax for you at the end ! So write what is more readable ! This is why query-syntax (written above in my answer) was invented in the first place ! – Zein Makki Sep 17 '16 at 08:03
Because of the current compiler lack of support. You can create such expression trees manually using `Expression` class, so ite definitely can be converted, just the compuler currently can't:) I understand your point, if it is supported, then it most probably will be translated to some runtime error. Note that it will perfectly work with LINQ to Objects queryable provider though. – Ivan Stoev Sep 17 '16 at 08:07
@IvanStoev We're on the same page from the very beginning. With linq-to-objects, any valid C# function matching the signature of the delegate is going to work. Linq-to-Objects requires a delegate (pointer to a method) and doesn't require any expression tree. – Zein Makki Sep 17 '16 at 08:14
@user3185569 Thanks a lot for your for effort!! Now it's clear to me.. – TanvirArjel Sep 17 '16 at 08:15
I know we are on the same page. Also I agree with the solution from the very beginning. Just disagree for the reason of the error :) – Ivan Stoev Sep 17 '16 at 08:19
@IvanStoev I have just found this : http://stackoverflow.com/questions/5179341/a-lambda-expression-with-a-statement-body-cannot-be-converted-to-an-expression It may change your mind :) – Zein Makki Sep 17 '16 at 08:20
I don' think so:). I agree with the linked post because it's specifically tagged LINQ to Entities, which is known for not supporting such expressions. But this question is not tagged (probably should be), so I was speaking generally. Again, generally speaking, it's possible to create such **expression** (note expression, not delegate) and **some** query providers to support it - LINQ To Objects `AsQueryable()` based queries for sure, EF Core query - who knows, they introduced the principle of separate execution - with SQL what is possible and in memory what is not. Take care. – Ivan Stoev Sep 17 '16 at 09:20

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

2 Answers2