Generated SQL with PredicateBuilder, LINQPad and operator ANY

Question

I previously asked a question about chaining conditions in Linq To Entities. Now I use LinqKit and everything works fine. I want to see the generated SQL and after reading this answer, I use LinqPad.

This is my statement:

var predProduct = PredicateBuilder.True<Product>();
var predColorLanguage = PredicateBuilder.True<ColorLanguage>();

predProduct = predProduct.And(p => p.IsComplete);

predColorLanguage = predColorLanguage.And(c => c.IdColorEntity.Products.AsQueryable().Any(expr));

ColorLanguages.Where(predColorLanguage).Dump();

The code works in VS2008, compile and produce the correct result set, but in LinqPad, I've the following error:

NotSupportedException: The overload query operator 'Any' used is not Supported.

How can I see the generated SQL if LINQPad fails?

EDIT

If I write

var predColorLanguage = PredicateBuilder.True<ColorLanguage>();

predColorLanguage = predColorLanguage.And(c => c.IdColorEntity.Products.Any((p => p.IsComplete));

ColorLanguages.Where(predColorLanguage).Dump();

works... WTF?

Underwing

Answer 1

As you're using LINQKit, you can make this work by calling Compile() on the expression that feeds the EntitySet, and then calling AsExpandable() on the main query:

var predProduct = PredicateBuilder.True<Product>();
var predColorLanguage = PredicateBuilder.True<ColorLanguage>();

predProduct = predProduct.And(p => p.IsComplete);

predColorLanguage = predColorLanguage.And (
  c => c.IdColorEntity.Products.Any(predProduct.Compile()));

ColorLanguages.AsExpandable().Where(predColorLanguage).Dump();

As explained in the LINQKit article, the Compile method never actually runs: AsExpandable strips it out and modifies the expression tree so that it works with LINQ to SQL.

Infinitive

The problem is not in Visual Studio, but in LinqPad. In Visual Studio it works with no problem. I've tried your solution with no luck. – Underwing

Are you using Entity Framework? And if so, are you choosing 'Custom EF Context' when connecting in LINQPad? If you instead connect directly to a database in LINQPad, you're using LINQ to SQL which could account for the difference. – Infinitive