Count non null values in multiple columns with LINQ

Question

The following SQL query counts non-null values of multiple columns in a single query (as in this answer):

SELECT COUNT(Id) AS Total,
    COUNT(Column_1) AS Column_1_Non_Null_Count,
    COUNT(Column_2) AS Column_2_Non_Null_Count,
    COUNT(Column_3) AS Column_3_Non_Null_Count,
    ...
FROM MyTable

Is there a corresponding Linq query which executes a SQL query similar to this one (without a subquery for each column count)?

Counting null values instead of non-null values would also be ok.

Answer 1

I'm not sure that exist a good way to do it with Entity Framework, I think that is better to do it with raw sql.

But assuming that you want to do it with Entity Framework, may be one way to do it is creating several queries using FutureCount method from EF.Extended library. Using Future methods from EF.Extended, all queries are postponed until is accessed the result of one of the queries and the data will be retrieved in one round trip to the database server.

var queryColumn1 = MyDBContext.MyTable.Where(q => q.Column1 == null).FutureCount();
var queryColumn2 = MyDBContext.MyTable.Where(q => q.Column2 == null).FutureCount();
...

int countColumn1 = queryColumn1.Value;
int countColumn2 = queryColumn2.Value

What I dislike of this solution is the readibility of the code, as I said I think that the good approach is do it using raw sql or using a stored procedured