Grouping and Aggregating When Querying LINQ to SQL - Querying with LINQ “COUNT” in different contexts
(Page 2 of 5 )
One of the most frequently used functions is COUNT. It comes in different flavors. The following three have different meanings:
SELECT COUNT(*) FROM Orders
SELECT COUNT(ShipRegion) FROM Orders
SELECT COUNT(DISTINCT ShipRegion) FROM Orders
The first returns the total number of rows available in the Orders table. The second returns the number of non-null values in the “ShipRegion” column of the Orders table. The third returns the number of values in the “ShipRegion” column without counting the repeated and null values.
From the point of view of LINQ, it always counts objects (and not values) in the collection. So, both of the following would return the same count values (i.e., total number of objects in Orders).
Dim q = (From p In db.Orders _
Select p).Count
Dim q = (From p In db.Orders _
Select p.ShipRegion).Count
In both of the above cases, the SQL SELECT which gets generated from the above LINQ would be same (with COUNT(*)). There exists no direct way in LINQ to achieve the above type of SQL SELECT query. But we can do it in an indirect manner.
I would like to rewrite the following query:
SELECT COUNT(ShipRegion) FROM Orders
in the following manner:
SELECT COUNT(*) FROM Orders WHERE ShipRegion IS NOT NULL
The same can be represented in LINQ (VB.NET) as follows:
Dim q = (From p In db.Orders _
Where p.ShipRegion IsNot Nothing _
Select p.ShipRegion).Count
You can observe that I am explicitly filtering nulls in the above LINQ query. The same can be achieved in C# LINQ as follows:
var q = (from p in db.Orders
where p.ShipRegion != null
select p.ShipRegion).Count();
And now, the following SQL SELECT:
SELECT COUNT(DISTINCT ShipRegion) FROM Orders
can be written in VB.NET LINQ as follows:
Dim q = (From p In db.Orders _
Where p.ShipRegion IsNot Nothing _
Select p.ShipRegion).Distinct.Count
And of course, in C# LINQ as follows:
var q = (from p in db.Orders
where p.ShipRegion != null
select p.ShipRegion).Distinct().Count();
Note that during the execution of above LINQ queries, the SQL SELECT which gets generated would be entirely different from what I have posted above. But, finally, the result would be same.
Next: Aggregations in LINQ (like GROUP BY of SQL in LINQ) >>
More .NET Articles
More By Jagadish Chaterjee
/ 9 
