CODEWITHSUNDEEP
linq-to-sqlentity-framework-6
BillN
BillN

Reputation: 35

Entity Framework and Union Statement

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?

Upvotes: 0

Views: 201

Answers (2)

jjj
jjj

Reputation: 4997

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");

Upvotes: 1

jjj
jjj

Reputation: 4997

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

Upvotes: 0

Related Questions