Entity framework core: input string was not in a correct format

Question

0.00/5 (No votes)

See more:

I am getting the nebulous

input string was not in a correct format

and I can see it was asked before, but I have attempted to get the type of the variable in order but to no avail. I would appreciate some assistance.

Here are my log messages:

WARN  07:05:02 The LINQ expression 'where (((Convert([e].p_Expiry_ID, Int32) == Convert(__tmpExpiryID_0, Int32)) AndAlso ([e].p_Valid_From <= DateTime.Now)) 
AndAlso ((ToDateTime([e].p_strValid_To) >= DateTime.Now) OrElse ([e].p_strValid_To == null)))' could not be translated and will be evaluated locally.

The Int32 is interesting here, because my definitions are Int16, then comes the exception:

SELECT `e`.`Expiry_ID`, `e`.`Valid_From`, `e`.`Day`, `e`.`Description`, `e`.`Expiry_Type`, `e`.`Export`, `e`.`Fixed_Interval_ID`, `e`.`Number_Interval_Units`, `e`.`Unit_ID`, `e`.`Valid_To`
FROM `ExpiryMaster` AS `e`
ORDER BY `e`.`Valid_From`
ERROR 07:05:02 An exception occurred in the database while iterating the results of a query for context type 'VT_OnlineCore.Models.ApplicationDbContext'.
System.InvalidOperationException: An exception occurred while reading a database value. See the inner exception for more information. ---> System.FormatException: Input string was not in a correct format.
   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.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at MySql.Data.MySqlClient.MySqlDataReader.GetInt32(Int32 i)
   at lambda_method(Closure , DbDataReader )
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
   at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)
   at lambda_method(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass17_1`1.<CompileQueryCore>b__0(QueryContext qc)

Now, here is the code that makes the call. Because the parameter variable is nullable I have tried moving it to a fixed type beforehand:

CExpiryMasterBase ExpiryRecord = new CExpiryMasterBase();
  Int16 tmpExpiryID = (Int16)workingTicket.ExpiryID;
   ExpiryRecord = repositoryExpiryMaster.ExpiryMaster
                               .Where(e => e.p_Expiry_ID == tmpExpiryID
                 && e.p_Valid_From <= DateTime.Now
                  && ((Convert.ToDateTime(e.p_strValid_To) >= DateTime.Now) || (e.p_strValid_To == null)))
                  .OrderBy(e => e.p_Valid_From)
                  .FirstOrDefault();

This is the property used:

   public Int16 p_Export
    {
        get
        {
            return m_Export;
        }
        set
        {
            m_Export = value;
        }
    }
private Int16   m_Expiry_ID;

This is the field definition:

Expiry_ID	tinyint(2)	NO	PRI

The database is MySQL

What I have tried:

Removing Valid From and Valid To from the query, using a temp field to avoid having a nullable field in the query.

I have also tried an elementary direct SQL query and got the same error result:

IQueryable<CExpiryMasterBase> data = context.ExpiryMaster
                               .FromSql(@"SELECT * FROM ExpiryMaster
                                   WHERE Expiry_ID = 1
                                   ");

Posted 13-Dec-18 20:19pm

Ger Hayden

Updated 16-Dec-18 8:16am

v3

Add a Solution

Comments

F-ES Sitecore 14-Dec-18 5:49am

Your code;

Convert.ToDateTime(e.p_strValid_To)

is probably causing this error because e.p_strValid_To can't be converted to date time. You might have another problem if ExpiryMaster is an entity framework entity as your "Where" needs to be converted to SQL and if the underlying MySQL provider can't convert "Convert.ToDateTime" into SQL it won't be able to run your query. If p_strValid_To contains date information then ensure it is a date format and not a string\varchar so it doesn't need conversion. EF can't save you from bad database design.

Ger Hayden 14-Dec-18 6:11am

That particular nasty originated in 2005 when it was not possible (or I did not know how) to have a null value in a MySQL Datetime field and has carried forward since. From what I can see there are still some issue around the same. In the meantime I will strip out the date conditions and hardcode the expiry ID to 1 then see what happens and report back

F-ES Sitecore 14-Dec-18 6:22am

In situations like that people generally used the minimum value for the field as being "null".

Ger Hayden 16-Dec-18 14:16pm

I've just posted a workaround

Ger Hayden 14-Dec-18 12:29pm

Something I will consider going forward, but for now it is not relevant to the problem at hand because it persists even when the date fields are removed from the query. This is one of many similar queries where all the others perform as expected. It is a subtle difference that remains to be spotted.

Richard Deeming 14-Dec-18 13:20pm

Sounds like you've got a string column in your database mapped to a non-string property in your model, and at least one of the rows you're trying to load has an invalid value in that column.

Ger Hayden 14-Dec-18 16:04pm

Checked that. Since I wrapped up for the day, it occurred to me that this is my first time accessing a table with an enumerated data type. That's trouble! I found something that might work on Stackoverflow https://stackoverflow.com/questions/44262314/how-can-i-make-ef-core-database-first-use-enums/48237275

If that fails then I may have to dust off my data handler module for that table that is part of the suite that have served the winform part of the system since the .NET migration in 08. They were only set aside to make room for me to learn EF core.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ger Hayden · Accepted Answer · 2018-12-16T08:17:00

The stackoverflow suggestion was unsuccessful, possibly because the enums in my solution are in user defined types further upstream from the property that maps to the column on the table.

I have worked around it by querying the database outside Entity Framework code using:

MySqlCommand cmd = new MySqlCommand();
 cmd.Connection = m_Connection; // Passed in from the controller
 cmd.CommandType = CommandType.Text;

            cmd.CommandText =
                 String.Format(@"select Expiry_Type,
                                Unit_ID,
                    Fixed_Interval_ID,
                    Day,
                    Number_Interval_Units
                                from ExpiryMaster
                    where Expiry_ID = {0}
                    and valid_from <= '{1}'
                    and(valid_to >= '{1}' or valid_to is NULL)",
                    arg_Param_ID, m_OrigDate);

MySqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
 {
    // Process the result
 }