IQueryable Amount List

Question

Given the list of IQueryables, how can you summarize the number of each of them without having multiple statements executed in the database?

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

The above works, but gets into the database for each query.

Answer 1

You can use the Aggregate function first with Concat to combine IQueryable and then Count , as shown below:

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

Answer 2

Starting with this idea, Sum(q1,q2) = q1.Concat(q2).Count() , I 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 that you have heterogeneous queries like IQueryable<T> , IQueryable<R> and so on, and you are interested in counting all the rows, regardless of the source.

So you can use these extensions, for example

 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 do not think it is very effective, though

Answer 3

If you are using the Entity Framework, you can use the EntityFramework.Extended extension. There is a built-in extension called Future Queries . This will allow you to indicate that the request should be executed the next time you start the trip to the database.

NuGet Team:

 Install-Package EntityFramework.Extended 

Code example:

 static void Main(string[] args) { using (var context = new MyDbContext()) { var modelSet1 = context.Models.Where(x => x.ModelId < 25).FutureCount(); var modelSet2 = context.Models.Where(x => x.ModelId > 25 && x.ModelId < 32).FutureCount(); var modelSet3 = context.Models.Where(x => x.ModelId > 32).FutureCount(); var queries = new [] {modelSet1, modelSet2, modelSet3}; var countQueries = queries.Sum(x => x.Value); Console.WriteLine(countQueries); } Console.ReadLine(); } 

Answer 4

Ok, a few minutes late, but I get it! Here is the code:

  public static class LinqExtensions { public static int CountAll(this IEnumerable<IQueryable<object>> queries) { if (queries == null || !queries.Any()) { throw new ArgumentException("Queries parameter cannot be null or empty"); } Expression ex = Expression.Constant(0); foreach (var qy in queries) { // create count expression var expression = Expression.Call( typeof(Queryable), "Count", new[] { qy.ElementType }, qy.Expression ); ex = Expression.Add(ex, expression); } return queries.First().Provider.Execute<int>(ex); } } 

You use it as queries.CountAll() , where the queries are IEnumerable<IQueryable<object>> , as in Adrian's answer, or even just IEnumerable<IQueryable> .

Here is an example SQL result from the profiler:

 exec sp_executesql N'SELECT @p0 + (( SELECT COUNT(*) FROM [A] AS [t0] WHERE [t0].[i1] >= @p1 )) + (( SELECT COUNT(*) FROM [B] AS [t1] WHERE [t1].[i2] >= @p2 )) + (( SELECT COUNT(*) FROM [C] AS [t2] WHERE [t2].[i3] >= @p3 )) AS [value]',N'@p0 int,@p1 int,@p2 int,@p3 int',@p0=0,@p1=2,@p2=2,@p3=2 

What is the view

  var a = db.GetTable<A>(); var b = db.GetTable<B>(); var c = db.GetTable<C>(); var q1 = a.Where(v => v.i1 >= 2); var q2 = b.Where(v => v.i2 >= 2); var q3 = c.Where(v => v.i3 >= 2); var queries = new IQueryable<object>[] { q1,q2,q3 }; 

Note that A, B and C are different objects / tables with different property / column numbers and that expressions are random, where are the filters.