Specified method is not supported MySql Entity Framwork 6

Question

I am trying to run the following Linq query from MySQL client

    query = query.Where(c => c.CustomerRoles
                              .Select(cr => cr.Id)
                              .Intersect(customerRoleIds)
                              .Any()
                       );

This code looks okay, but gives the error:

System.NotSupportedException: Specified method is not supported.at MySql.Data.Entity.SqlGenerator.Visit(DbIntersectExpression expression)

This looks to me like an issue with .Intersect. Can anybody tell me the cause of this error and how to fix it?

Answer 1

i think @GertArnold's post is a correct and best of the answers, but i'm wonder why have you gotten NotSupportedException yet ? so the problem should not be from intersect probably.

where is customerRoleIds come from ? is it IQueryable<T> ?

break the query, and complete it step by step.

if you don't get exception at this lines:

var a = query.Select(c => new { 
        c, 
        CustomerRoleIDList = c.CustomerRoles.Select(cr => cr.Id).AsEnumerable()
    })
    .ToList();

var b = customerRoleIds.ToList();

you must get the result by this:

var b = query.Where(c => c.CustomerRoles.any(u => customerRoleIds.Contains(u.Id)))
    .ToList();

if you get exception by above query, you can try this final solution to fetch data first, but note by this, all data will be fetched in memory first:

var a = query.Select(c => new { 
        c, 
        CustomerRoleIDList = c.CustomerRoles.Select(cr => cr.Id).AsEnumerable() 
    })
    .ToList();

var b = a.Where(c => c.CustomerRoleIDList.any(u => customerRoleIds.Contains(u)))
    .Select(u => u.c)
    .ToList();