How to obtain ToTraceString for IQueryable.Count

Question

I use ((ObjectQuery)IQueryable).ToTraceString() to obtain and tweak SQL code that is going to be executed by LINQ.

My problem is that unlike most IQueryable methods IQueryable.Count as defined like this:

    public static int Count(this IQueryable source) {
        return (int)source.Provider.Execute(
            Expression.Call(
                typeof(Queryable), "Count",
                new Type[] { source.ElementType }, source.Expression));
    }

executes query without compiling and returning IQueryable. I wanted to do the trick by something like this:

public static IQueryable CountCompile(this IQueryable source) {
    return source.Provider.CreateQuery(
        Expression.Call(
            typeof(Queryable), "Count",
            new Type[] { source.ElementType }, source.Expression));
}

But then CreateQuery gives me the following exception:

LINQ to Entities query expressions can only be constructed from instances that implement the IQueryable interface.

Answer 1

You cannot create a query object for 'Count' since it is does not return an IQueryable (which makes sense - it returns a single value).

You have two options:

• (Recommended) Use eSQL:

  context.CreateQuery<YourEntity>("select count(1) from YourEntitySet").ToTraceString()
  

• Use Reflection to call a private method that doesn't perform the IQueryable check (this is wrong for obvious reasons but if you just need it for debugging, it may be handy):

  public static IQueryable CountCompile(this IQueryable source)
  {
      // you should cache this MethodInfo
      return (IQueryable)source.Provider.GetType().GetMethod("CreateQuery", BindingFlags.NonPublic | BindingFlags.Instance, null,
                                          new[] {typeof (Expression), typeof (Type)}, null)
          .Invoke(source.Provider, new object[]
                                       {
                                           Expression.Call(
                                               typeof (Queryable), "Count",
                                               new[] {source.ElementType}, source.Expression),
                                           source.ElementType
                                       });
  }
  

Answer 2

Here's an actual working answer I came up with when I tried to so the same. The exception says "can only be constructed from instances that implement the IQueryable interface", so the answer seems simple: return a queryable something. Is that possible when returning a .Count()? Yes!

public partial class YourObjectContext
{
    private static MethodInfo GetMethodInfo(Expression<Action> expression)
    {
        return ((MethodCallExpression)expression.Body).Method;
    }
    public IQueryable<TResult> CreateScalarQuery<TResult>(Expression<Func<TResult>> expression)
    {
        return QueryProvider.CreateQuery<TResult>(
            Expression.Call(
                method: GetMethodInfo(() => Queryable.Select<int, TResult>(null, (Expression<Func<int, TResult>>)null)),
                arg0: Expression.Call(
                    method: GetMethodInfo(() => Queryable.AsQueryable<int>(null)),
                    arg0: Expression.NewArrayInit(typeof(int), Expression.Constant(1))),
                arg1: Expression.Lambda(body: expression.Body, parameters: new[] { Expression.Parameter(typeof(int)) })));
    }
}

To use it:

var query = context.CreateScalarQuery(() => context.Entity.Count());
MessageBox.Show(((ObjectQuery)query).ToTraceString());

Basically, what this does is wrap a non-IQueryable query in a subselect. It transforms the query into

from dummy in new int[] { 1 }.AsQueryable()
select context.Entity.Count()

except lets the context's QueryProvider handle the query. The generated SQL is pretty much what you should expect:

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Entity] AS [Extent1]
)  AS [GroupBy1]