Combine two counting queries into one logical query

Question

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

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

return count1 > count2;

Note that I'm interested in writing a single request that returns a boolean and gets evaluated once on the server.

Note that I'm interested in ways to write this using LINQ, not SQL.

Answer 1

Try

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

You should not try to run two unrelated requests 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 that you can force LINQ to create such a query. Instead, you will need to call the stored procedure.

Answer 2

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

return result; // result is of type bool

:

, DataContext.ExecuteQuery .