EFCore SelectMany - InvalidOperation Error

Question

I am using Efcore with Mysql. below is the code:

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

Console.WriteLine("Hello, World!");

using (var context = new BlogDataContext())
{
    var t = context.Assignments.SelectMany(r => r.Investors).ToList();
}

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

    public DbSet<Employee> Employee { get; set; }
    public DbSet<Assignments> Assignments { get; set; }

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

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

}

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

Employee has Investors assigned to him. So Icollection<< int>> Investors saves list of int of Investors assigned to him. The Primary requirement was always to get investors assigned to an employee. So We designed the table like this. But now the requirement is to get who an Investor is assigned to.

This is a replica I have tried to create and is not actual Project code.

I am getting error at SelectMany Line:

System.InvalidOperationException: The LINQ expression 'EF.Property<ICollection>(StructuralTypeShaperExpression: Assignments ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False , "Investors")' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'

Further, if this is successful, I will use that list to get InvestorID and name of employee he is assigned to. I checked official doc of select many with Enumerables as well as Queryables. The syntax they have used is same.

Answer 1

The confusion was caused because SelectMany's working with in memory array vs EF Table / DbSet.

For example, if I have a class like below:

public class Employee
{
    public int id { get; set; }
    public string Name { get; set; } = "";
    public int[] Investors { get; set; }

}

and if I do:

Employee[] e = new Employee[]  {
    new Employee() { Name = "Suraj", investors = new int[] { 1, 2, 3, 4 } },
    new Employee() { Name = "Pranav", investors = new int[] { 5, 6} }
};

var sm = e.SelectMany(e => e.investors.Select( i => new { name = e.Name, inv = i } ));

foreach(var s in sm)
{
    Console.WriteLine(s.name + " " + s.inv);
}

So I was expecting it to work same if Employee was DBSet. But it doesn't.

If Employee is a DBset, it stores the Investors array as string like this - [1,2,3,4] and if I try to to SelectMany on it, it tries to search the relation for it, for Inner Join. Which in my case, isn't any.

Also, that's why this example works. In their case, Invoice is separate table, the relation exists.

The solution would be to fetch whole Employee table in memory, and then to SelectMany.

Like this:

using (var context = new BlogDataContext())
{
    var emp = context.Employee.ToList();
    var empSelectMany = emp.SelectMany(e => e.investors.Select(i => new { name = e.Name, inv = i }));

    foreach (var s in empSelectMany)
    {
        Console.WriteLine(s.name + " " + s.inv);
    }
}