Combine two counting queries into one Boolean query

Question

I can't think of a good way to write this as a single query.

int count1 = query1.Count();
int count2 = query2.Count();

return count1 > count2;

Please note that I'm interested in ways to write a single query that returns a Boolean value and gets evaluated once on the server.

Please note that I'm interested in ways to write this with LINQ, not with SQL.

Answer 1

Try

return query1.Count() > query2.Count();

It's not worth trying to run two unrelated queries in the same server call, but here it is:

SELECT CASE WHEN (SELECT COUNT(*) FROM Products) 
        > (SELECT COUNT(*) FROM Orders) 
            THEN CAST(1 as bit) 
            ELSE CAST(0 AS BIT)

However, I seriously doubt you can get LINQ to create this kind of query. You would have to call a stored procedure instead.

Answer 2

What's wrong with

var result= query1.Count() > query2.Count();

return result; // result is of type bool

EDIT:

If you really want to execute it as one query on the server, you could use DataContext.ExecuteQuery to execute a dynamic query.