Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I'm having trouble with counting null values using Entity SQL.

Code in SQL:

SELECT Table.City, COUNT(*)
FROM Table
GROUP BY Table.City

and in Linq to Entities:

var query1 = from g in context.Table
             group g by g.City into mygroup
             select new { mygroup, c = mygroup.Count() };

both return correct values:

NULL    4
Boston  7
Canberra    1
London  5
Melbourne   5
New York    4
Paris   15
Sydney  4

However, Entity SQL code written like:

string queryString1 = "SELECT city, COUNT(g.City)" +
                      "FROM Entities.Table AS g " +
                      "GROUP BY g.City as city";

returns:

NULL    0
Boston  7
Canberra    1
London  5
Melbourne   5
New York    4
Paris   15
Sydney  4

Why are the NULLs not counted properly? How can I fix it?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

How Entity SQL Differs from Transact-SQL

Entity SQL does not support the count(*) aggregate. Use count(0) instead.

Usually COUNT(*) counts rows while COUNT(g.City) gives you NOT NULL values amount.

share|improve this answer
 
That works fine. Thanks. –  user2302010 Jul 20 at 14:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.