Doing a UNION ALL on two different COUNTs using Linq-To-Sql

Question

Is it possible to write a linq-to-sql statement that will return two different COUNTs that have been put into a single dataset using UNION ALL?

I know this is syntactically wrong but here's what I'm trying to do:

(from t1 in TableOne select t1).Count().Union(
    (from t2 in TableTwo select t2).Count()
)

Here's the sql I would like would like to have generated:

select count(*) from TableOne
union all
select count(*) from TableTwo

I realize that Count() returns an int and does not have a Union method on it and therein lies my question. Can Linq-to-Sql be written that will achieve what I'm after?

Answer 1

You could just manually put both counts into a list, like so:

var counts = new List<int>() { db.TableOne.Count(), db.TableTwo.Count() };

More generally, if your result-sets are collections rather than single values, the UNION ALL equivalent in LINQ would be Enumerable.Concat - you want to concatenate the results together.