EF Core搭配MySQL使用SelectMany触发InvalidOperationException错误求助

错误原因 #

你遇到的InvalidOperationException是因为EF Core无法将针对ICollection<int>类型字段的SelectMany操作转换为MySQL可执行的SQL。关系型数据库中并没有直接对应内存集合的字段类型，这类字段通常会被EF Core映射为JSON格式存储，但EF Core的LINQ查询提供者不支持对JSON集合字段直接执行SelectMany的服务器端翻译。

解决方案一：调整数据模型（推荐，符合关系型数据库设计规范） #

原设计用集合存储投资者ID不符合关系型数据库的关联设计，应该创建中间关联表记录员工与投资者的分配关系，既避免查询翻译问题，也能更好支持双向查询。

修改后的模型代码 #

using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;

public class Employee
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; } = "";

    // 导航属性：员工对应的所有分配关系
    public ICollection<EmployeeInvestorAssignment> EmployeeInvestorAssignments { get; set; } = new List<EmployeeInvestorAssignment>();
}

public class Investor
{
    [Key]
    public int Id { get; set; }
    // 可添加投资者其他属性
}

// 中间关联表：记录员工与投资者的分配关系
public class EmployeeInvestorAssignment
{
    public int EmployeeId { get; set; }
    public Employee Employee { get; set; }

    public int InvestorId { get; set; }
    public Investor Investor { get; set; }
}

public class BlogDataContext : DbContext
{
    static readonly string connectionString = "Server=localhost; User ID=root; Password=test; Database=SelectMany";

    public DbSet<Employee> Employees { get; set; }
    public DbSet<Investor> Investors { get; set; }
    public DbSet<EmployeeInvestorAssignment> EmployeeInvestorAssignments { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 配置中间表的复合主键
        modelBuilder.Entity<EmployeeInvestorAssignment>()
            .HasKey(ea => new { ea.EmployeeId, ea.InvestorId });

        // 配置员工与分配记录的关联
        modelBuilder.Entity<EmployeeInvestorAssignment>()
            .HasOne(ea => ea.Employee)
            .WithMany(e => e.EmployeeInvestorAssignments)
            .HasForeignKey(ea => ea.EmployeeId);

        // 配置投资者与分配记录的关联
        modelBuilder.Entity<EmployeeInvestorAssignment>()
            .HasOne(ea => ea.Investor)
            .WithMany() // 若投资者无需反向导航属性，此处留空
            .HasForeignKey(ea => ea.InvestorId);
    }
}

实现反向查询（获取投资者ID及对应员工姓名） #

using (var context = new BlogDataContext())
{
    // 查询所有投资者对应的员工信息
    var investorEmployeeMap = context.EmployeeInvestorAssignments
        .Include(ea => ea.Employee)
        .Select(ea => new 
        {
            InvestorId = ea.InvestorId,
            EmployeeName = ea.Employee.Name
        })
        .ToList();

    // 按投资者ID分组，获取每个投资者对应的所有员工
    var groupedResult = context.EmployeeInvestorAssignments
        .Include(ea => ea.Employee)
        .GroupBy(ea => ea.InvestorId)
        .Select(g => new 
        {
            InvestorId = g.Key,
            AssignedEmployees = g.Select(ea => ea.Employee.Name).ToList()
        })
        .ToList();
}

解决方案二：保留原模型（仅适用于数据量较小的场景） #

如果必须保留原有Assignments模型设计，需先将数据加载到内存再执行SelectMany（启用客户端评估）。注意：此方式会加载所有Assignments数据到内存，数据量大时存在性能隐患。

修改后的查询代码 #

using (var context = new BlogDataContext())
{
    // 先加载所有Assignments到内存，再执行SelectMany
    var allInvestors = context.Assignments
        .AsEnumerable() // 切换到客户端评估
        .SelectMany(r => r.Investors)
        .ToList();

    // 实现反向查询：获取投资者ID及对应员工姓名
    var investorEmployeeMap = context.Assignments
        .Include(a => a.Employee)
        .AsEnumerable()
        .SelectMany(a => a.Investors.Select(investorId => new 
        {
            InvestorId = investorId,
            EmployeeName = a.Employee.Name
        }))
        .ToList();
}

补充：原模型需添加导航属性 #

要实现反向查询，原Assignments类需添加与Employee的关联：

public class Assignments
{
    [Key]
    public int id { get; set; }
    public int rmid { get; set; }
    public ICollection<int> Investors { get; set; }

    // 添加导航属性，关联员工
    public Employee Employee { get; set; }
}

同时在BlogDataContext的OnModelCreating中配置关联：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Assignments>()
        .HasOne(a => a.Employee)
        .WithMany() // 若Employee无需反向导航，此处留空
        .HasForeignKey(a => a.rmid);
}

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