Lambda Expression with a statement body can not been converted

Question

I'am trying to get running total.

decimal currentTotal = 0;
var query = test
           .OrderBy(i => i.Date)
           .Select(i =>
           {
               currentTotal += i.Amount;
               return new
               {
                   Date = i.Date,
                   Amount = i.Amount,
                   RunningTotal = currentTotal
               };
           }
                  );

And I've got error in this line .Select(i => How could I resolve this?

Answer 1

You are doing a query to Entity Framework or LINQ2SQL:

decimal currentTotal = 0;

var query = test
            .OrderBy(i => i.Date)
            .Select(i => new
            {
                    Date = i.Date,
                    Amount = i.Amount
            })

            // Above lines executed on SQL
            .AsEnumerable()
            // Below lines executed locally

            .Select(i =>
            {
                currentTotal += i.Amount;
                return new
                {
                    Date = i.Date,
                    Amount = i.Amount,
                    RunningTotal = currentTotal
                };
            }
        );

You can't execute your add to currentTotal on the SQL Server, so you use AsEnumerable() to execute it locally.

Explanation:

When you program with EF/LINQ2SQL, the query you build with IQueryable<T> is translated in the SQL language and executed on the SQL Server. So the .OrderBy() becomes a ORDER BY, all the .Where() become a WHERE and so on. Clearly if in the LINQ query you put something that can't be translated to SQL, then you get an exception when the query is translated. Now, there are two problems in your query.

For some limitations of C#, only expressions written without { ... } blocks (and some other limitations... no if, no return, ...) can be translated to Expression<Func<>>. For this reason your program can't be compiled.

Even if it was possible to compile it directly, the SQL Server doesn't know anything of your currentTotal variable, so you would get a NotSupportedException at runtime.

The solution: you create a "legal" query with only the pieces that can be executed by SQL... You .Select() the columns you'll need. Then with .AsEnumerable() you tell the EF that from the .AsEnumerable() onward it mustn't translate the query to SQL, and must execute the query "locally". So the .OrderBy() and the first .Select() will be executed SQL-side, while the second .Select() (with the currentTotal) will be executed locally.

Answer 2

Error:Lambda Expression with a statement body can not been converted to an expression tree

It means that you can't use lambda expressions with a "statement body" which is the case with linq2sql. which means you can only use simple expression after => operator