Linq Error Input string was not in a correct format. System.Exception {System.FormatException}

Question

When I'm trying to lookup a record by field name uuid I'm getting below error:

{Input string was not in a correct format.}

More note: It seems parsing ENUM from MySQL is big hassle in c#

it worked without error when I changed

public BankAccountReconciled? Reconciled { get; set; }

to:

public string Reconciled { get; set; }

Now, I need to know how to instruct the Linq to parse the Enum from Mysql back into C# Enum:

    public enum BankAccountReconciled
    {
        [Display(Name = "true")]
        True,
        [Display(Name = "false")]
        False
    }

Error at Try/Catch:

{"Input string was not in a correct format."}
-       e   {"Input string was not in a correct format."}   System.Exception {System.FormatException}

When press SHIFT+f9 and evaluate I'm gettting below error:

"Method System.Data.Entity.Core.Objects.ObjectQuery`1[T] MergeAs(System.Data.Entity.Core.Objects.MergeOption) contains generic parameters"

public XeroBankAccount FindAccountByUuid(String uuid)
        {
            try
            {
                using (SyncBankDbContext dbContext = new SyncBankDbContext())
                {
                    string tempUuid = uuid;
                    var result = dbContext.XeroBankAccounts.SingleOrDefault(x => x.AccountUuid == tempUuid);
                    return result;
                }
            }
            catch (Exception e)
            {
                string errorMessage = e.Message;
                return null;
            }
        }

StackTrace:

   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at MySql.Data.Entity.EFMySqlDataReader.ChangeType(Object sourceValue, Type targetType)
   at MySql.Data.Entity.EFMySqlDataReader.GetValue(Int32 ordinal)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetUntypedValueDefault(DbDataReader reader, Int32 ordinal)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
   at lambda_method(Closure , Shaper )
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
   at lambda_method(Closure , Shaper )
   at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__2[TResult](IEnumerable`1 sequence)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
   at SyncBank.Service.XeroAccountService.FindAccountByUuid(String uuid)

XeroBankAccount.cs:

using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using SyncBank.Xero;

namespace SyncBank.Models
{
    [Serializable]
    [Table("xero_bank_account")]
    public class XeroBankAccount
    {

        [Key]
        [Column("id")]
        public int Id { get; set; }

        [ForeignKey("XeroOrganisation")]
        [Column("organisation_id")]
        public int XeroOrganisationId { get; set; }

        public XeroOrganisation XeroOrganisation { get; set; }

        [Column("bank_id")]
        public int? BankId { get; set; }


        [Column("title")]
        [MinLength(1), MaxLength(128)]
        [Required]
        public String AccountTitle { get; set; }

        [Column("number")]
        [StringLength(50)]
        public String AccountNumber { get; set; }

        [Column("balance_statement")]
        public double? BalanceStatement { get; set; }

        [Column("balance_xero")]
        public double? BalanceXero { get; set; }

        [Column("last_statement_date")]
        public DateTime? LastStatementDate { get; set; }

        [Column("reconciled")]
        public BankAccountReconciled? Reconciled { get; set; }

        [Required]
        [Column("uuid")]
        [StringLength(256)]
        public string AccountUuid { get; set; }

        [Column("orders")]
        public int Orders { get; set; }

        public override int GetHashCode()
        {
            return Id.GetHashCode();
        }

        public override bool Equals(Object obj)
        {
            // TODO: Warning - this method won't work in the case the id fields are not set
            if (!(obj is XeroBankAccount))
            {
                return false;
            }
            XeroBankAccount other = (XeroBankAccount)obj;
            return Id == other.Id;
        }

        public override String ToString()
        {
            return "org.syncbank.entity.XeroBankAccount[ accountId=" + Id + " ]";
        }
    }
}

SQL xero_bank_account:

CREATE TABLE `xero_bank_account` (
  `id` int(11) NOT NULL,
  `organisation_id` int(11) NOT NULL,
  `bank_id` int(11) DEFAULT NULL,
  `title` varchar(128) NOT NULL,
  `number` varchar(50) DEFAULT NULL,
  `balance_statement` decimal(12,2) DEFAULT NULL,
  `balance_xero` decimal(12,2) DEFAULT NULL,
  `reconciled` enum('true','false') NOT NULL DEFAULT 'false',
  `last_statement_date` datetime DEFAULT NULL,
  `uuid` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `orders` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `xero_bank_account`
--
ALTER TABLE `xero_bank_account`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `account_uuid` (`uuid`),
  ADD KEY `account_title_caption` (`number`),
  ADD KEY `bank_id` (`bank_id`);

Is variable ID in database as string or a number? You are expecting a string. I think the database is a number. string tempUuid = uuid; — jdweng
– jdweng, Commented Jan 25, 2019 at 12:13
could you please show what you pass in uuid in FindAccountByUuid(String uuid) and how this uuid store in database column? — er-sho
– er-sho, Commented Jan 25, 2019 at 12:22
also, did you change the data type for any column after adding some records? because of this probably you will get this type of exception. — er-sho
– er-sho, Commented Jan 25, 2019 at 12:27
More note: It seems parsing ENUM from MySQL is big hassle in c# it worked without error when I changed — Mason.Chase
– Mason.Chase, Commented Jan 25, 2019 at 12:31
try to remove ? from property declaration like public BankAccountReconciled Reconciled { get; set; } and let me know — er-sho
– er-sho, Commented Jan 25, 2019 at 12:41

er-sho · Accepted Answer · 2019-01-25 14:30:54Z

2

The exception is because of you saved your mysql enum value as string true, false and this is not parsed by enum in c# so that error comes.

So if there is possible then change the enum in MySQL like

`reconciled` enum(1,0) NOT NULL DEFAULT 0

And then set your c# enum member value to 0,1 like

public enum BankAccountReconciled
{
    [Display(Name = "true")]
    True = 1,                    //<= Set 1 for true that equivalent to mysql enum value 1
    [Display(Name = "false")]
    False = 0                    //<= Set 0 for false that equivalent to mysql enum value 0
}

Edit:

One way is that just keep you mysql enum as it is like

`reconciled` enum('true','false') NOT NULL DEFAULT 'false',

And keep your class property datatype to string

public string Reconciled { get; set; }

And keep your c# enum with respect to mysql enum

public enum BankAccountReconciled
{
    [Display(Name = "true")]
    True,                      
    [Display(Name = "false")]
    False                       
}

And now just add one more property to your class object that can cast your string to enum like

public BankAccountReconciled EnumReconciled
{
    get
    {
        return GetEnumValue<BankAccountReconciled>(Reconciled);
    }
}

And above property need a helper function that can convert your string to enum

public static T GetEnumValue<T>(string str) where T : struct, IConvertible
{
    Type enumType = typeof(T);
    if (!enumType.IsEnum)
    {
        throw new Exception("T must be an Enumeration type.");
    }
    T val;
    return Enum.TryParse<T>(str, true, out val) ? val : default(T);
}

Output: (from Debugger)

edited Jan 25, 2019 at 14:30

answered Jan 25, 2019 at 13:05

er-sho

9,8212 gold badges17 silver badges26 bronze badges

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

13 Comments

Mason.Chase Over a year ago

How can we reverse "true" to "true" instad of storing numerical value in Database? in other casses I may have "ACTIVE", "SUSPENDED", "DISABLED" and I want to be able to see the DB value without lookup to a file or reference

er-sho Over a year ago

if it 1 then your c# enum will get you True enum value automatically

Mason.Chase Over a year ago

Yes, but I want to avoid using numerical representation of actual value, for true/false it make sense but not for "ACTIVE", "SUSPENDED" , "DISABLED"

er-sho Over a year ago

in this case you can declare your c# enum like => ACTIVE = 0, SUSPENDED = 1, DISABLED = 2 and your mysql enum will be enum(0,1,2)...

er-sho Over a year ago

I know you want like this => ACTIVE = "active", SUSPENDED = "suspended" and your my sql enum like enum(active, suspended) but unfortunately c# enum value member is not compatible with string means you cannot assign ACTIVE = "active" but you can Active = 0 or Active = 1000.

|

Ricardo Peres · Accepted Answer · 2019-01-25 12:16:48Z

0

The orders column is marked as null on the database, but not as a nullable type in .NET (property Orders).

answered Jan 25, 2019 at 12:16

Ricardo Peres

14.8k5 gold badges64 silver badges78 bronze badges

3 Comments

Mason.Chase Over a year ago

No, that was not the issue, it seems parsing ENUM from MySQL is big hassle in c#

Ricardo Peres Over a year ago

OK, glad you sorted it out!

Mason.Chase Over a year ago

No, it's not sorted, I still need to figure out how to reverse it

Collectives™ on Stack Overflow

Linq Error Input string was not in a correct format. System.Exception {System.FormatException}

2 Answers 2

13 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related