使用MySql.EntityFrameworkCore 10.0.0-rc时Contains查询报类型映射错误求助

问题场景 #

使用MySql.EntityFrameworkCore 10.0.0-rc版本，执行针对字符串字段Plz的Contains查询时抛出异常；但针对同表的int类型Id字段执行相同逻辑的查询却能正常运行。核心查询代码如下：

var list = new List<string> { "1", "2", "3", "4", "5", "6", "7", "8", "9" };
var arr = list.ToArray();

var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
optionsBuilder.UseMySQL(gemasConnectionString);
            
using var context = new MyDbContext(optionsBuilder.Options);
var query = context.MyTable
    .Where(x => x.Plz != null && arr.Contains(x.Plz));
var result = await query.ToListAsync();

尝试使用List、Array或IEnumerable作为集合源，结果一致报错。

表定义 #

CREATE TABLE IF NOT EXISTS `MyTable` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `A` int(10) DEFAULT '0',
  `B` varchar(10) DEFAULT NULL,
  `C` varchar(50) DEFAULT NULL,
  `D` varchar(50) DEFAULT NULL,
  `E` int(10) DEFAULT '0',
  `F` varchar(10) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `PLZ` varchar(10) DEFAULT NULL,
  `Nr` int(10) DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `PLZ` (`PLZ`)
) ENGINE=InnoDB AUTO_INCREMENT=789 DEFAULT CHARSET=utf8;

实体类 #

public partial class MyTable
{
    public int Id { get; set; }
    public int? A { get; set; }
    public string? B { get; set; }
    public string? C { get; set; }
    public string? D { get; set; }
    public int? E { get; set; }
    public string? F { get; set; }
    public DateTime Ts { get; set; }
    public string? Plz { get; set; }
    public int? Nr { get; set; }
}

异常信息 #

System.InvalidOperationException
  HResult=0x80131509
  Message=Expression '@list' in the SQL tree does not have a type mapping assigned.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.InExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlBinaryExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.Process(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.ProcessTypeMappings(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at MySql.EntityFrameworkCore.Query.Internal.MySQLQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__67`1.MoveNext()
   at Program.<TryContainsQuery>d__1.MoveNext() in C\...\Program.cs:line 80

原因分析 #

这是MySql.EntityFrameworkCore 10.0.0-rc版本的预发布Bug。RC版本作为候选发布版存在功能不完善问题，此处字符串集合的Contains查询在SQL树生成时，EF Core未能正确为集合参数分配类型映射；而int类型字段的类型映射逻辑更成熟，因此未触发该问题。

解决方案 #

1. 升级到正式版：优先等待官方发布10.0.0正式版，或切换到9.x系列稳定版本，正式版会修复这类预发布阶段的Bug。
2. 内存过滤绕过：如果暂时无法升级，可先将符合Plz != null条件的数据加载到内存，再进行集合过滤（仅适合小数据集，大数据量会导致性能问题）：

   var result = await context.MyTable
       .Where(x => x.Plz != null)
       .ToListAsync()
       .ContinueWith(t => t.Result.Where(x => arr.Contains(x.Plz)).ToList());
   

3. 显式配置类型映射：在DbContext的OnModelCreating方法中，为Plz字段显式指定数据库类型，帮助EF Core正确识别映射关系：

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
       modelBuilder.Entity<MyTable>()
           .Property(x => x.Plz)
           .HasColumnType("varchar(10)")
           .HasCharSet("utf8");
   }
   

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