CODEWITHSUNDEEP
linq-to-sql
jmoreno
jmoreno

Reputation: 13561

Sum List of IQueryable

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.

Upvotes: 5

Views: 1284

Answers (4)

Aducci
Aducci

Reputation: 26664

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()

Upvotes: 4

Siderite Zackwehdex
Siderite Zackwehdex

Reputation: 6570

Ok, a few minutes late, but I got 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 queries is an IEnumerable<IQueryable<object>> as in Adrian's answer or even simple IEnumerable<IQueryable>.

Here is a sample 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

Which is the representation of 

    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 numbers of properties/columns and that the expressions are random Where filters.

Upvotes: 2

Adrian Iftode
Adrian Iftode

Reputation: 15663

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

Upvotes: 2

Igor
Igor

Reputation: 62213

If you are using Entity Framework you can use an extension called EntityFramework.Extended. There is a built in extension called Future Queries. This will allow you to specify that a query should be executed the next time a trip to the database is made.

NuGet command:

Install-Package EntityFramework.Extended

Sample code:

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();
}

Upvotes: 1

Related Questions