Lambda expression with statement body error in previously working code

Question

I need help with this, I have a very important piece of code that I got help with from this thread

The code is as follows:

var pln = db.tabStockPlanners
            .Where(y => y.ExpectedHarvestDate < addoneyear)
            .Where(r => r.Published == 1)
            .Where(f => f.Available == 1);

var gruppedList = pln.GroupBy(x => x.ItemID, (key, enumerable) =>
        {
            return new tabStockPlanner { ItemID = key, ExpectedYieldInTonnes = enumerable.Sum(k => k.ExpectedYieldInTonnes) };
        }).OrderByDescending(t => t.ExpectedYieldInTonnes).ToList();

But I get an error

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

I do not know if this is something that has changed with EF. Can someone help me with this? EF is version 6. Any issues with the current code?

UPDATE

What has worked perfectly is:

 var gplist = from x in db.tabStockPlanners
                     where x.Published == 1
                     where x.Available == 1
                     where x.ExpectedHarvestDate < addoneyear
                     group x.ExpectedYieldInTonnes by new { x.ItemID }
                     into g
                     select new { g.Key.ItemID, ExpectedYieldInTonnes = g.Sum() };

But I would like to also have the correct answer as per this syntax. Maybe someone can repost with the LINQ syntax. Thank you.

UPDATE Hello Friends; @YosefBinmal solution worked well, along with @NetMage proposal for AsEnumerable() I am posting Yosef's solution with modifications to make it anonymous and return just the 2 columns needed. I removed the tabStockPlanner so its new { and it is solid. So marking that as correct. Any objections? It works well.

Here's the modified code

  var pln = db.tabStockPlanners
          .Where(y => y.ExpectedHarvestDate < addoneyear)
          .Where(r => r.Published == 1)
          .Where(f => f.Available == 1);

        var gruppedList = pln
            .AsEnumerable()
.GroupBy(i => i.ItemID)
.Select(g => new { ItemID = g.Key, ExpectedYieldInTonnes = g.Sum(i => i.ExpectedYieldInTonnes) })
.OrderByDescending(t => t.ExpectedYieldInTonnes)
.ToList();

Can you have a look at this https://stackoverflow.com/questions/5179341/a-lambda-expression-with-a-statement-body-cannot-be-converted-to-an-expression — Shahid Manzoor Bhat, Dec 26 '19 at 16:36
Try replacing the tuple (key, enumerable) with new { key, enumerable }. It didn't work for me with tuples in EF6 — Gleb, Dec 26 '19 at 16:43
This has *never* been supported in EF6 and earlier. You must have changed something. The difference with the other question is that the answer concludes the first query by `ToList()`. — Gert Arnold, Dec 26 '19 at 21:06

Yosef Bernal · Accepted Answer · 2019-12-27T12:40:54.323

3

Amend: The GroupBy extension method you are using takes an expression lambda as it's second parameter. Your lambda contains a { return ... } statement that can't be translated into an Expression tree. Please see CS0834.

You could avoid this error by writing:

var gruppedList = pln
    .GroupBy(i => i.ItemID) 
    .Select(g => new tabStockPlanner { ItemID = g.key, ExpectedYieldInTonnes = g.Sum(i => i.ExpectedYieldInTonnes) })
    .OrderByDescending(t => t.ExpectedYieldInTonnes)
    .ToList();

I like to use the overload of GroupBy that only takes keySelector (without the element selector). This way the LINQ pipeline becomes more readable in my opinion as each function has a single task.

Hope it helps you

edited Dec 27 '19 at 12:40

answered Dec 26 '19 at 17:09

Yosef Bernal

680
4
17

1

This isn't really correct - the error means an `Expression` tree can't be created from the lambda, and that has nothing to do with SQL conversion. – NetMage Dec 26 '19 at 19:31
Thanks so much, unfortunately this didnt work... Error: The entity or complex type 'maxmallModel2.tabStockPlanner' cannot be constructed in a LINQ to Entities query. – noetico Dec 27 '19 at 11:43
1

That's a different issue. Perhaps [this](https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query) can help you? – Yosef Bernal Dec 27 '19 at 12:45
@NetMage You're right, got mixed up there! Amended the answer – Yosef Bernal Dec 27 '19 at 12:46
You may want to repeat your comment to @noetico tagging the user so they will be notified you commented to them. It is a very good comment. – NetMage Dec 27 '19 at 18:22
ok thanks for this! After putting `AsEnumerable()` before the `GroupBy` as NetMage proposed; it worked, one only problem being that it returned all the table fields, unlike the one I posted in the original thread that just returns `ItemID` and `ExpectedYieldInTonnes` which is good for graphs and a small grid display. I believe a small tweak @YosefBernal then we have a perfect option and if we all agree, a good solution. – noetico Dec 27 '19 at 19:04

score 1 · Answer 2 · answered Dec 26 '19 at 16:48

1

Replace with

var gruppedList = pln.GroupBy(x => x.ItemID, (key, enumerable) => new tabStockPlanner { ItemID = key, ExpectedYieldInTonnes = enumerable.Sum(k => k.ExpectedYieldInTonnes) })
.OrderByDescending(t => t.ExpectedYieldInTonnes).ToList();

answered Dec 26 '19 at 16:48

Mattias Santoro

140
1
5

Thanks so much. This code still returned this: The entity or complex type 'maxmallModel2.tabStockPlanner' cannot be constructed in a LINQ to Entities query. – noetico Dec 27 '19 at 11:42
@noetico If you put `AsEnumerable` before the `GroupBy`, that will pull all the data at that point and do the `GroupBy`, etc. on the client side, and should work. – NetMage Dec 27 '19 at 18:18
Ok so @NetMage ... Pln.AsEnumerable.GroupBy... ok I will give it a run. – noetico Dec 27 '19 at 18:43

Lambda expression with statement body error in previously working code

2 Answers2

Linked