0

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.

Community
  • 1
  • 1
marapet
  • 54,856
  • 12
  • 170
  • 184

1 Answers1

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

Marc Cals
  • 2,963
  • 4
  • 30
  • 48
  • I agree about the readability. And even though the queries are executed in one roundtrip, it still means executing one query per column. For now I'm using raw sql/views, but I won't accept this answer just yet in case someone comes up with a EF solution. – marapet Jun 11 '15 at 06:44