Sum the difference of two columns in Entity Framework

Question

I am trying to compute the sum of the difference of two columns:

var result = model.TableExample
                  .Where(condition here)
                  .Select(s => s.Column1 - s.Column2)
                  .Sum();

but it is rising an exception:

Index and length must refer to a location within the string

I can't understand this. There is no string anywhere, columns are integer in model and database. Is there something wrong?

I am using MySql provider.

Answer 1

Looks like a bug in the MySQL Linq provider... The error probably comes from internal string manipulations in the provider.

You could try to work around it by retrieving the rows and computing the sum in memory, like this:

var result = model.TableExample
              .Where(condition here)
              .Select(s => s.Column1 - s.Column2)
              .AsEnumerable()
              .Sum();

Or this:

var result = model.TableExample
              .Where(condition here)
              .AsEnumerable()
              .Sum(s => s.Column1 - s.Column2);

Note that the performance will suffer if there are many rows, because this code fetches each rows instead of just fetching the sum.