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
– Yatin, Commented Apr 19, 2018 at 3: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
– Dan Wilson, Commented Apr 19, 2018 at 3:31

TheGeneral · Accepted Answer · 2018-04-19 03:57:10Z

2

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

edited Apr 19, 2018 at 3:57

answered Apr 19, 2018 at 3:51

TheGeneral

82k9 gold badges110 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bagus Tesa Over a year ago

does EF supports decimal.Parse? on worst case you will need to do that in memory

John Clarence Castro Over a year ago

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

Collectives™ on Stack Overflow

Linq Input string was not in a correct format in MVC

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related