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?

Amir Sherafatian · Accepted Answer · 2014-05-04T17:37:24.207

3

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();

edited May 04 '14 at 17:37

answered May 04 '14 at 17:30

Amir Sherafatian

2,083
2
20
32

I think `Intersect` is not supported, hence the `NotSupportedException`. But I can't find any documentation of supported LINQ methods. – Gert Arnold May 06 '14 at 07:05
all function which your provide can translate to `expression tree` will be supported in linq, some functions which is called `canonical functions` is supported by default by all providers, you can read about this functions here: http://msdn.microsoft.com/en-us/library/vstudio/bb738626(v=vs.100).aspx – Amir Sherafatian May 06 '14 at 07:41
i think probably the problem is from `intersect` too, but you told, got same error when you done GertArnold's solution, and his solution was without `intersect` !!! – Amir Sherafatian May 06 '14 at 07:44
`Intersect` is a set function, not a canonical SQL function. Each provider can choose whether and how to support it. The same goes for `Any`. It's sad when a provider doesn't offer clear documentation on this part. – Gert Arnold May 06 '14 at 07:47
@GertArnold yes, you right, i posted canonical function documentation link for more information – Amir Sherafatian May 06 '14 at 09:09

score 2 · Answer 2 · edited May 23 '17 at 12:26

2

Using Intersect or Except is probably always troublesome with LINQ to a SQL backend. With Sql Server they may produce horrible SQL queries.

Usually there is support for Contains because that easily translates to a SQL IN statement. Your query can be rewritten as

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

I don't think that customerRoleIds will contain many items (typically there won't be hundreds of roles), otherwise you should take care not to hit the maximum number of items allowed in an IN statement.

edited May 23 '17 at 12:26

Community

1
1

answered Apr 29 '14 at 13:22

Gert Arnold

105,341
31
202
291

Which version of, I guess, dotConnect are you using? – Gert Arnold Apr 29 '14 at 20:02
mysql connector 6.8.3 – Muhammad Faizan Khatri Apr 30 '14 at 06:14
1

Is `Any` not supported by connector? – Gert Arnold Apr 30 '14 at 06:50

Matas Vaitkevicius · Answer 3 · 2014-04-29T10:27:11.053

1

Try adding toList() before intersect, that should enumerate results locally instead running on MySql, you will get performance hit thought.

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

edited Apr 29 '14 at 10:27

answered Apr 28 '14 at 15:25

Matas Vaitkevicius

58,075
31
238
265

still giving the Same error, while filtering other fields , when trying to inspect the "Query" object its showing the My Sql Query in it, but when this (Intersect) statement is Run, its showing that "Query" as object. – Muhammad Faizan Khatri Apr 29 '14 at 08:32
your customerRoleIds are enumerated right? if not toList() them too. – Matas Vaitkevicius Apr 29 '14 at 08:35
@fez I have made an edit try that instead and please let me know how it goes. – Matas Vaitkevicius Apr 29 '14 at 09:41
Now giving syntax Error: Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'bool' – Muhammad Faizan Khatri Apr 29 '14 at 10:24
Remove .Any() at the end – Matas Vaitkevicius Apr 29 '14 at 10:27
giving error here:c.CustomerRoles.Select(cr => cr.Id)) not on Any() – Muhammad Faizan Khatri Apr 29 '14 at 10:33
So the problem is not with .Intersect() I presume, as logic now does the following - SelectIds and make them into list = SELECT Id FROM [TableName], there must be some other underlying problem. Could you just to confirm run this var x = query.Where(c => c.CustomerRoles.Select(cr => cr.Id)); – Matas Vaitkevicius Apr 29 '14 at 10:39
giving same error : Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'bool' – Muhammad Faizan Khatri Apr 29 '14 at 15:25
sorry my bad try this var x = query.SelectMany(c => c.CustomerRoles.Select(cr => cr.Id)); – Matas Vaitkevicius Apr 29 '14 at 15:39

score 1 · Answer 4 · answered Apr 28 '14 at 15:39

1

query.Where(c => c.CustomerRoles
                 .Any(v=>customerRoleIds.Any(e=>e == v.Id))
     .Select(cr => cr.Id))
     .ToList();

answered Apr 28 '14 at 15:39

Honorable Chow

3,097
3
22
22

giving syntax error :bool does not contain a definition for 'Select' and no extention method – Muhammad Faizan Khatri Apr 29 '14 at 08:29

Specified method is not supported MySql Entity Framwork 6

4 Answers4