.Net MVC项目EF查询抛出System.InvalidCastException异常求助

环境配置 #

• Visual Studio 2022
• .NET Framework 4.8
• EntityFramework 6.4.4
• MySql.Data.EntityFramework 8.3.0

连接字符串 #

string connectionString="Server=localhost;Database=testDB;Uid=root;Pwd=Pasword1;port=3306;"

MyWebViewModel 类定义 #

public class MyWebViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Content { get; set; }
    public string Type { get; set; }
    public int WorkId { get; set; }
    [NotMapped]
    public string Networkname { get; set; }
    public int PageId { get; set; }
    [NotMapped]
    public string DataSource { get; set; }
    public string Status { get; set; }
    public string CreatedBy { get; set; }
    public DateTime CreatedDate { get; set; }
    public string UpdatedBy { get; set; }
    public DateTime? UpdatedDate { get; set; }
    [NotMapped]
    public string SelectedContentType { get; set; }
    [NotMapped]
    public List<DerviedContent> DerviedContent { get; set; }
}

抛出异常的EF查询代码 #

List<MyWebViewModel> viewModel = null;  
viewModel = (from w in MyClass.GetApplicationDbContext().Pagelayout.AsEnumerable()
             where w.WorkId == WorkId && w.PageId == PageId
             orderby w.Sequence
             select new MyWebViewModel
             {
                 Id = w.Id,
                 Name = w.Name,
                 Content = w.Content,
                 Type = w.Type,
                 WorkId = w.WorkId,
                 PageId = w.PageId
             }).ToList();   

异常信息 #

System.InvalidCastException: Object must implement IConvertible.
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at MySql.Data.EntityFramework.EFMySqlDataReader.ChangeType(Object sourceValue, Type targetType)
   at MySql.Data.EntityFramework.EFMySqlDataReader.GetValue(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.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.OrderedEnumerable`1.<GetEnumerator>d__1.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

测试可行的Dapper查询代码 #

var v = connection.Query<MyWebViewModel>(@"SELECT Id, Name, Content, Type,  WorkId, 
PageId FROM LayoutViewModelTable where WorkId =11 and PageId = 11");
List<MyWebViewModel> viewModel= v.ToList();

问题背景 #

项目最初基于.NET Framework 4.5和MySQL Server 5.3构建，已尝试升级EF版本，问题未解决。

解决方案 #

1. 检查实体与数据库字段类型匹配 #

异常核心是类型转换失败，重点核对Pagelayout实体类字段与数据库表字段的类型一致性：

• 检查Sequence字段：若数据库中是tinyint/smallint，实体类需对应定义为byte/short，避免强制转int出错；
• 确认Content等文本字段在数据库中是TEXT/LONGTEXT，EF映射时未被错误识别为其他类型；
• 验证DateTime类型字段在数据库中是datetime/datetime2，而非不兼容的字符串或数值类型。

2. 调整EF查询执行逻辑 #

原代码中AsEnumerable()会将全表数据拉到内存再处理，易引发类型转换问题且影响性能，建议先在数据库端完成过滤排序：

viewModel = (from w in MyClass.GetApplicationDbContext().Pagelayout
             where w.WorkId == WorkId && w.PageId == PageId
             orderby w.Sequence
             select new MyWebViewModel
             {
                 Id = w.Id,
                 Name = w.Name,
                 Content = w.Content,
                 Type = w.Type,
                 WorkId = w.WorkId,
                 PageId = w.PageId
             }).ToList();

若必须在内存处理，先投影为匿名类型再映射到ViewModel：

var pagelayouts = MyClass.GetApplicationDbContext().Pagelayout
    .Where(w => w.WorkId == WorkId && w.PageId == PageId)
    .OrderBy(w => w.Sequence)
    .Select(w => new { w.Id, w.Name, w.Content, w.Type, w.WorkId, w.PageId })
    .AsEnumerable()
    .Select(w => new MyWebViewModel
    {
        Id = w.Id,
        Name = w.Name,
        Content = w.Content,
        Type = w.Type,
        WorkId = w.WorkId,
        PageId = w.PageId
    }).ToList();

3. 适配MySQL驱动兼容性 #

MySQL 8.x驱动与EF6存在部分兼容性问题，可尝试以下操作：

• 降级MySql.Data.EntityFramework到6.10.9（与EF6兼容性更稳定的版本）；
• 连接字符串添加Allow User Variables=True参数，规避部分类型转换异常：

  string connectionString="Server=localhost;Database=testDB;Uid=root;Pwd=Pasword1;port=3306;Allow User Variables=True;"
  

4. 确保ViewModel存在无参构造函数 #

EF实例化对象依赖无参构造函数，显式添加默认构造函数：

public class MyWebViewModel
{
    public MyWebViewModel() {}
    // 其他属性定义...
}

内容的提问来源于stack exchange，提问作者Ullan