Entity Framework and Union Statement

Question

I have this query in SQL Server:

SELECT 'QueueA', COUNT(*) FROM QueueA_Table
UNION
SELECT 'QueueB', COUNT(*) FROM QueueB_Table 

How do I do the equivalent with the Entity Framework? The best I could do was:

using (var db = new dbContext())
{
    return new QueueCount[] {
      new QueueCount("QueueA", db.QueueA_Table.Count()),
      new QueueCount("QueueB", db.QueueB_Table.Count())
    };  
}

However, this results in two separate Queries to the database according to LINQPad.

SELECT COUNT(*) AS [value]
FROM [QueueA_Table] AS [t0]
GO
SELECT COUNT(*) AS [value]
FROM [QueueB_Table] AS [t0]

Is there a way to write it so that only one query is sent to the database?

Answer 1

The query is ugly, and it's pretty much a hack:

(from s in QueueA_Table.Take(1)
 select new List<int> {
    QueueA_Table.Count(),
    QueueB_Table.Count()
 }).First()

or

(from s in QueueA_Table.Take(1)
 select new  {
     QueueA = QueueA_Table.Count(),
     QueueB = QueueB_Table.Count()
 })

Answer 2

Maybe not the prettiest or most maintainable way to do it:

db.Database.SqlQuery<int>(@"
    SELECT COUNT(*) FROM QueueA_Table
    UNION
    SELECT COUNT(*) FROM QueueB_Table");

or if you want to populate a class:

public class QueueCount
{
    public string Name { get; set; }
    public int Count { get; set; }
}

db.Database.SqlQuery<QueueCount>(@"
    SELECT [Name] = 'QueueA', [Count] = COUNT(*) FROM QueueA_Table
    UNION
    SELECT [Name] = 'QueueB', [Count] = COUNT(*) FROM QueueB_Table");