实体框架核心：输入字符串格式不正确 [英] Entity framework core: input string was not in a correct format

问题描述

我得到模糊的

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.

Int32在这里很有意思，因为我的定义是Int16，然后是异常：

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	

数据库是MySQL



我尝试过：



从查询中删除Valid From和Valid To，使用临时字段来避免在查询中出现可为空的字段。 br />


我也尝试了一个基本的直接SQL查询并得到了相同的错误结果：

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
                                   ");

推荐答案

stackoverflow建议不成功，可能是因为我的解决方案中的枚举是在用户定义的类型中更远的上游映射到表中列的属性。



我通过使用以下方法查询实体框架代码外的数据库来解决这个问题：

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
 }

这篇关于实体框架核心：输入字符串格式不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！