CODEWITHSUNDEEP
c#linqlinq-to-sql
user3592246
user3592246

Reputation: 167

Linq - Unsupported overload used for query operator 'Where' using a Predicate

I'm trying to fix some badly performing Linq queries but I'm having trouble with Linq syntax. I am much more adept at SQL than Linq.

The current working version just uses Joins but this is inefficent as it's returning multiple rows that are then DISTINCTed. I want to convert the join to an EXISTS clause. It almost works but chokes when I add a predicate to the Where clause. The code compiles but I get the above error. There are questions on SO with similar error messages but they all seem to be dealing with slightly different scenarios.

This code works:

query = from contact in query
        where Context.TblJobToBrandLink.Join(Context.TblBrandSpends
                                                    //.Where(spendExpression)
                                                    .Where(x => x.IntBrandiD != 0), 
                                             x => x.IntBrandId, 
                                             y => y.IntBrandiD, 
                                             (x, y) => x
                                            ).Any(jobToBrand => jobToBrand.IntJobId == contact.IntJobId)
        select contact;

The SQL produced is exactly what I expected.

If I uncomment the line with .Where(spendExpression) I get the unsupported overload error.

spendExpression is of type Linq.Expressions.Expression<Func<TblBrandSpend, bool>>

It works in the version using joins:

query = from contact in query
        join jobToBrand in Context.TblJobToBrandLink on contact.IntJobId equals jobToBrand.IntJobId
        join brandSpend in Context.TblBrandSpends.Where(spendExpression) on jobToBrand.IntBrandId equals brandSpend.IntBrandiD
        where brandSpend.IntBrandiD != 0
        select contact;

Any ideas?

Upvotes: 2

Views: 1663

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109185

I can reproduce this in a similar query expression. The stack trace of the exception looks like this:

at System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitWhere(Expression sequence, LambdaExpression predicate)
...
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

So it's the LINQ-to-SQL expression visitor tripping over some unexpected complexity. Which means this is just a bug, and you have to work around it.

What works (in my case) is using the expression directly:

query = from contact in query
        where Context.TblJobToBrandLink
                     .Join(Context.TblBrandSpends
                     .Where(x => ...) // spendExpression's Expression here
                     ...

or taking the part Context.TblBrandSpends.Where(spendExpression).Where(x => x.IntBrandiD != 0) outside the query:

var brandSpends = Context.TblBrandSpends
                         .Where(spendExpression)
                         .Where(x => x.IntBrandiD != 0);

query = from contact in query
        where Context.TblJobToBrandLink
                     .Join(brandSpends,
                         x => x.IntBrandId, 
                         y => y.IntBrandiD, 
                         (x, y) => x)
                     .Any(jobToBrand => jobToBrand.IntJobId == contact.IntJobId)
        select contact;

It works in Entity Framework, by the way.

Upvotes: 3

Related Questions