Sum List of IQueryable

Question

Given a list IQueryables, how can you sum the count of each, without having multiple statements executed in the database?

 return queries
               .Sum(qy=> qy.Count());

The above works, but hits the database for each query.

Answer 1

You can first use the Aggregate function with Concat to combine the IQueryable's and then Count the total like this:

 return queries.Aggregate((x,y) => x.Concat(y)).Count()

Answer 2

Starting from this idea Sum(q1,q2) = q1.Concat(q2).Count() I've tested the following extensions:

public static class LinqExtensions
{
    public static IQueryable<object> ConcatAny<T,R>(this IQueryable<T> q1, IQueryable<R> q2)
    {
        return q1.Select(c=>(object)null).Concat(q2.Select(c=>(object)null));
    }

    public static IQueryable<object> ConcatAll(this IEnumerable<IQueryable<object>> queries)
    {
        var resultQuery = queries.First();
        foreach (var query in queries.Skip(1))
        {
            resultQuery = resultQuery.ConcatAny(query);
        }
        return resultQuery;
    }
}

I assumed you have heterogeneous queries like IQueryable<T>, IQueryable<R> so on and you are interested in counting all rows no matter which the source is.

So you might use these extensions like

var q1 = Table1.AsQueryable();
var q2 = Table2.AsQueryable();
var q3 = Table3.AsQueryable();

var queries = new IQueryable<object>[] {q1,q2,q3}; // we use here the covariance feature

return queries.ConcatAll().Count();

The generated SQL might look like this

SELECT COUNT(*) AS [value]
FROM (
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT NULL AS [EMPTY]
        FROM [Table1] AS [t0]
        UNION ALL
        SELECT NULL AS [EMPTY]
        FROM [Table2] AS [t1]
        ) AS [t2]
    UNION ALL
    SELECT NULL AS [EMPTY]
    FROM [Table3] AS [t3]
    ) AS [t4]

I don't think is very effective though