Combine Aggregate functions over multiple tables using Entity Framework

Question

I currently get 2 aggregate function results this way:

var fooCount = ctx.Foo.Count();
var barCount = ctx.Bar.Count();

This produces 2 different SQL queries. I am looking for a way to combine these into one query.

In plain SQL I can combine these two queries this way:

SELECT 
  (SELECT COUNT(*) FROM Foo) AS FooCount,
  (SELECT COUNT(*) FROM Bar) AS BarCount

How can we do this using Entity Framework? All I have found was for multiple aggregate function in the same table. But in my case it are different tables.

I tried something like this with no success:

var query = from together in (new
{
    FooCount = db.Foo.Count(),
    BarCount = db.Bar.Count()
}) select together; //<-- visual studio let me not write "select together;"

You are creating a Anonymous types, not a IEnumerable, why not new int[] {ctx.Foo.Count(), ctx.Bar.Count()} — Arturo Menchaca
– Arturo Menchaca, Commented May 5, 2016 at 14:33
@ArturoMenchaca Each call to Count is going to create a separate SQL call and the OP wants to combine them into one. — juharr
– juharr, Commented May 5, 2016 at 14:34

juharr · Accepted Answer · 2016-05-05 14:52:21Z

3

I think what you want is something like this. Basically grouping by a constant allows you to do the count aggregation as an IQueryable<int> and then you can cross join them. But I don't know that it's worth it. Basically you're trading readability for presumably some increase in performance.

var counts = (from fc in (
                 from f in ctx.Foo
                 group f by 1 into fgrp
                 select fgrp.Count())
             from bc in (
                 from b in ctx.Bar
                 group b by 1 into bgrp
                 select bgrp.Count())
             select new { FooCount = fc, BarCount = bc }).Single();

edited May 5, 2016 at 14:52

answered May 5, 2016 at 14:40

juharr

32.4k4 gold badges60 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Christian Gollhardt Over a year ago

Thank you. It seems like it is generating a cross join, but I assume this is no problem, because it is calculating Sum before?

juharr Over a year ago

Yeah the cross join is going to be against two sets that each have one row. I would expect the SQL from this to roughly have the same query plan as your SQL, but don't hold me to that. It's best to test this against doing the counts separately to determine which is faster, assuming that's what your goal is.

Collectives™ on Stack Overflow

Combine Aggregate functions over multiple tables using Entity Framework

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related