Simple.Data

Simple.Data defines a number of commands for retrieving data from a data store. These can then be daisychained in a LINQ-like fashion with further methods to modify the basic query. For example, Simple.Data includes two similar methods to include a count in your query.

Count

Daisychain the Count method to the end of a column selection return the number of non-null items in that group.

  • Unless otherwise aliased, or cast to a scalar value, you can access the count value in the returned SimpleRecord object using the empty string as a key
  • To return the number of null and non-null items using a count(*) statement, please use the GetCount or GetCountBy methods.

Examples

1

To return a count of the non-null values for the OrderId column in OrderDetails, use the following command:

var details = db.OrderDetails.All()
  .Select(
    db.OrderDetails.OrderId.Count());

Simple.Data sends the following SQL to the database when details is evaluated

select 
   Count([dbo].[OrderDetails].[OrderId])
from [dbo].[OrderDetails]

CountDistinct

You can daisychain the Count and Distinct methods together to return a count of the non-duplicate values in a field, or you can use the CountDistinct method to avoid any confusion by getting Distinct and Count the wrong way around. More information on the Distinct method can be found here.

Examples

1

To return a count of the distinct non-null values for the OrderId column in OrderDetails, use one of the following commands:

var details = db.OrderDetails.All()
  .Select(
    db.OrderDetails.OrderId.Distinct().Count());   // NOT .Count().Distinct()!!

var details = db.OrderDetails.All()
  .Select(
    db.OrderDetails.OrderId.CountDistinct());

In either case, Simple.Data sends the following SQL to the database when details is evaluated

select count(distinct([dbo].[OrderDetails].[OrderId])) from [dbo].[OrderDetails]