Linq Input string was not in a correct format in MVC

Question

The original SQL Command coded by the old programmers looks like this

SELECT ooe_general_id, SUM(CAST(CAST(amount AS money) AS float)) AS totalAppro, MAX(date) AS lastDate 
FROM  dbo.pmTA_OoeGeneralAppropriation

Datatype: String

Database Values
----------------
3,200,000.00
2916410
28,710,000.00
0.80000000000291
-1000000

When I try to convert the above code to Linq, an error 'Input string was not in a correct format' shows in this line

totalAppro = g.Sum(p => Convert.ToDouble(p.amount))

I tried doing changing the code to

Convert.Double(string, IFormatprovider)  
Convert.ToDouble(String.Format("{0:c}", p.amount)) 
Convert.ToDecimal(p.amount.Replace(",",""))

from these Currency, Convert, SUM LINQ forums but the error still persists.

The question is what am I doing wrong here? Why did my first two codes work but this doesn't? And lastly, why is there an 'Input string was not in a correct format' error?

Thanks in advance.

UPDATE

var totalAppropriationGeneralFund = (from p in db.iBudget_OoeGeneralAppropriation   
                                 where !p.amount.StartsWith("-")
                                 group p by p.ooe_general_id into g    
                                 select g).AsEnumerable().Select(g => new {
                                 id = g.Key,
                                 totalAppro = g.Sum(p => Convert.ToDecimal(p.amount.Replace(",",""))),
                                 date = g.Max(p => p.date)
                             }).ToList();

Can you copy your whole linq query here - what does g represent? And, may be try double.Parse() or decimal.Parse() — Yatin, Apr 19 '18 at 03:30
One of your values for `amount` is not in a valid format to be parsed by `Convert.ToDouble()`. The examples you have given thus far are fine. Check the rest of your data. It does concern me that `amount` is being cast to `money` in the original query. Why is it *not* being stored as `money`? — Dan Wilson, Apr 19 '18 at 03:31
@DanWilson Same concern I had when I saw it. I couldn't change the datatype in the table because it is linked to another table through foreign key. — John Clarence Castro, Apr 19 '18 at 03:42
@DanWilson What is the range for the Double datatype? As for checking individually, all was fine making me wonder why an error was showing. — John Clarence Castro, Apr 19 '18 at 03:43

TheGeneral · Accepted Answer · 2018-04-19T03:57:10.183

Firstly

Don't store or convert datatypes to string and back again. leave them as what they are, convert them for display purposes only, and definitely don't store numeric types as string in a db (just a tip).

Secondly

If your slightly worried about accuracy, don't use floating point values like double or float. They are notorious for losing precision and adding artefacts, use a decimal

Thirdly

NumberStyles Enumeration : Determines the styles permitted in numeric string arguments that are passed to the Parse and TryParse methods of the integral and floating-point numeric types.

The above was your problem

Working example

var list = new List<string>
   {
      "3,200,000.00",
      "2916410",
      "28,710,000.00",
      "0.80000000000291",
      "-1000000"
   };

var sum = list.Sum(x => decimal.Parse(x, NumberStyles.Any, CultureInfo.InvariantCulture));

Output

33826410.80000000000291

Full Demo here

Further reading

decimal (C# Reference)

Update

As noted in the comments by Bagus Tesa, you cant use parse methods in EF or Linq to sql, this has to be done in memory, i would consider changing you under laying datatype and or query

does EF supports `decimal.Parse`? on worst case you will need to do that in memory — Bagus Tesa, Apr 19 '18 at 03:54
Was having the same thoughts when I first saw the codes and datatype build in the database by the old programmers. I guess I should really change the database. Thanks for the answer and the link sr. Kudos — John Clarence Castro, Apr 19 '18 at 04:05

Linq Input string was not in a correct format in MVC

1 Answers1