1

I was trying to sum up a column of type nvarchar(7) that I need to first convert data to decimal(18, 7).

I used this LINQ query: 

var Data = queryBase.Select(q => new { totalVol = q.Collection.Sum(c => Convert.ToDecimal(c.Volume) }).FirstOrDefault();

However I got an exception

LINQ to Entities does not recognize the method 'System.Decimal ToDecimal(System.String)' method...

Any help will be greatly appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LN.EXE
  • 59
  • 8
  • Possible duplicate of [LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method when attempting to parse a column for inequality comparisons](http://stackoverflow.com/questions/14558837/linq-to-entities-does-not-recognize-the-method-int32-parsesystem-string-meth) – David L Sep 13 '16 at 21:33
  • Adding D Staney's answer below, just convert the values to Decimal before this line (and store in new List). It requires you to make a new List but will solve your problem. However, if you're 100% sure all the Volumes can be converted without null or empty values, just use `q.Collection.Sum(c => (decimal) c.Volume)` – Keyur PATEL Sep 14 '16 at 03:30

4 Answers4

4

The error is pretty clear. EF is not programmed to convert the Convert.ToDecimal call to SQL.

Some options:

  • Write the equivalent SQL manually and call it using ExecuteQuery
  • Create a view on top of your data that converts the strings to numbers and query that.
  • Pull all of the data into memory and to the conversion/summation in Linq-to-Objects.
D Stanley
  • 149,601
  • 11
  • 178
  • 240
1

You can use Cast<double> before doing the Sum This will throw an exception if the database can't convert the string

var Data = 
    queryBase.Select(q => 
       new 
       { 
          totalVol = q.Collection
                      .Select(c => c.Volume)
                      .Cast<double>()
                      .Sum()   
       }
     ).FirstOrDefault();
Aducci
  • 26,101
  • 8
  • 63
  • 67
0

As the Convert.ToDecimal must be converted to SQL, EF doesn't know how to do it.

So, do a queryBase.ToList() first (this will execute the SQL and all the data is in memory, as objects).

To minimize the size of the retrieved data do: queryBase.Select(/*here filter the string field*/).ToList().

Then, do the sum, using Convert or Decimal.Parse

Romias
  • 13,783
  • 7
  • 56
  • 85
0

try some like this:

var Data = queryBase.Select(c =>c.Volume).AsEnumerable().Sum(d => Convert.ToDecimal(d.Volume));
M.Reyes
  • 1
  • 2
  • What is there that suggests that the OP is using nullable ints? They are almost certainly mapping to a string. – David L Sep 13 '16 at 21:26