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. Use the Having command to specify any number of search criteria for a group or an aggregate.

Having

Use the Having method to add grouping to your data and filtering based on that grouping. If criteria already exist, old and new criteria are combined with the AND operator.

Syntax

public SimpleQuery Having(
		SimpleExpression criteria
)

Parameters

criteria: Type: SimpleExpression
A (concatenated sequence of) search criteria for a group or an aggregate function. For example, db.Albums.AlbumId.Count() > 2.

Return Value

Type: SimpleQuery
A SimpleQuery objct containing a Having clause

Exceptions

Exception	Condition
`NullReferenceException`	`Having` has been called directly on a table reference without a base command (All, FindAllBy etc)
`ArgumentException`	`criteria` contains zero or more than one `SimpleExpression`.
`UnresolvableObjectException`	`criteria` contains a reference to a column not in the select clause of the query.
`Simple.Ado.AdoAdapterException`	The `SimpleExpression` supplied to `Having` has no aggregate function in it

Note that issues #285 - #288 are still open with regards to exceptions thrown by Having.

Remarks

There is no GroupBy method in Simple.Data. Use the Having method instead to set aggregate search conditions on columns and Simple.Data will generate the appropriate Group By clause for those searched-for columns not in the Having clause.

The following aggregate methods are supported in SimpleExpressions: Min, Max, Avg, Sum, and Count.

For example:

//Max
db.Artists.All().Having(db.Artists.Albums.Price.Max() > 10.99);

//Min
db.Artists.All().Having(db.Artists.Albums.Price.Min() > 5.99);

//Avg
db.Artists.All().Having(db.Artists.Albums.Price.Avg() > 8.99);

//Sum
db.Orders.All().Having(db.Orders.OrderDetails.UnitPrice.Sum() > 35);

//Count
db.Orders.All().Having(db.Orders.OrderDetails.AlbumId.Count() > 2);

Having takes a single SimpleExpression with an aggregate function on it as a parameter. Complex search conditions can be constructed by appending multiple Having clauses to the base command - in which case the new criteria is appending to the old using the default AND operator - or building up complex SimpleExpressions as demonstrated here.

Examples

SIMPLE HAVING CLAUSE

The following example retrieves the name of any artist and the number of their albums in the database if it is greater than two.

SimpleQuery albums = db.Albums.All()
    .Select(db.Albums.Artists.Name, 
        db.Albums.AlbumId.Count().As("NumberOfAlbums"))
    .Having(db.Albums.AlbumId.Count() > 2);

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

select 
   [dbo].[Artists].[Name],
   Count([dbo].[Albums].[AlbumId]) AS [NumberOfAlbums] 
from [dbo].[Albums] 
   LEFT JOIN [dbo].[Artists] ON ([dbo].[Artists].[ArtistId] = [dbo].[Albums].[ArtistId]) 
GROUP BY 
   [dbo].[Artists].[Name] 
HAVING Count([dbo].[Albums].[AlbumId]) > @p1

@p1 (Int32) = 2

COMPLEX HAVING CLAUSE

The following example retrieves the name of any artist, number of albums they have recorded and cost to buy them all if the number of albums is more than two OR the cost to buy is greater than 16.99

SimpleQuery albums = db.Albums.All()
    .Select(db.Albums.Artists.Name, 
        db.Albums.AlbumId.Count().As("NumberOfAlbums"), 
        db.Albums.Price.Sum().As("TotalCost"))
    .Having(db.Albums.AlbumId.Count() > 2 || db.Albums.Price.Sum() >= 16.99);

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

select 
   [dbo].[Artists].[Name],
   Count([dbo].[Albums].[AlbumId]) AS [NumberOfAlbums],
   Sum([dbo].[Albums].[Price]) AS [TotalCost] 
from [dbo].[Albums] 
   LEFT JOIN [dbo].[Artists] ON ([dbo].[Artists].[ArtistId] = [dbo].[Albums].[ArtistId]) 
GROUP BY
   [dbo].[Artists].[Name] 
HAVING (Count([dbo].[Albums].[AlbumId]) > @p1 OR Sum([dbo].[Albums].[Price]) >= @p2)

@p1 (Int32) = 2
@p2 (Double) = 16.99

MULTIPLE HAVING CLAUSE

The following example uses multiple Having methods to concatenate several aggregate conditions together.

SimpleQuery albums = db.Albums.All()
    .Select(db.Albums.Artists.Name, 
        db.Albums.AlbumId.Count().As("NumberOfAlbums"), 
        db.Albums.Price.Sum().As("TotalCost"))
    .Having(db.Albums.AlbumId.Count() > 2)
    .Having(db.Albums.Price.Sum() >= 16.99);

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

select 
   [dbo].[Artists].[Name],
   Count([dbo].[Albums].[AlbumId]) AS [NumberOfAlbums],
   Sum([dbo].[Albums].[Price]) AS [TotalCost] 
from [dbo].[Albums] 
   LEFT JOIN [dbo].[Artists] ON ([dbo].[Artists].[ArtistId] = [dbo].[Albums].[ArtistId]) 
GROUP BY
   [dbo].[Artists].[Name] 
HAVING (Count([dbo].[Albums].[AlbumId]) > @p1 AND Sum([dbo].[Albums].[Price]) >= @p2)

@p1 (Int32) = 2
@p2 (Double) = 16.99