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

Solution 1

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

Solution 2

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

Comments

[Ivan Stoev]

The error OP is getting has nothing to do with db query translation. It's a compile time error.

[TanvirArjel]

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"

[Zein Makki]

@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]

@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 !

[Ivan Stoev]

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.

[Zein Makki]

@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.

[TanvirArjel]

@user3185569 Thanks a lot for your for effort!! Now it's clear to me..

[Ivan Stoev]

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 :)

[TanvirArjel]

Thanks a lot for your explanation!!

[Zein Makki]

@IvanStoev I have just found this : stackoverflow.com/questions/5179341/… It may change your mind :)

[Ivan Stoev]

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.