Click here to Skip to main content
15,883,705 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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)

This is the property used:

   public Int16 p_Export
            return m_Export;
            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
Updated 16-Dec-18 8:16am
F-ES Sitecore 14-Dec-18 5:49am    
Your code;


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.

1 solution

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,
                                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
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900