Simple.Data

Where

Use the Where method to add further filtering criteria to a SimpleQuery. If criteria already exist, old and new criteria are combined with the AND operator.

Syntax

public SimpleQuery Where(
		SimpleExpression criteria
	)

Parameters

criteria

Type: SimpleExpression
A (concatenated sequence of) search criteria. For example, dbo.Album.GenreId == 1.

Return Value

Type: SimpleQuery
A SimpleQuery object containing a Where clause.

Exceptions

Note that issues #321, and #322 are still open with regards to exceptions thrown by Where.

Remarks

Using the Where method allows you to add to the filtering criteria you may have already given your Find*, Add and Get queries. For example, the following commands all produce the same SQL.

db.Albums
    .FindAllByGenreId(1)
    .Select(db.Albums.Title)
    .Where(db.Albums.AlbumId > 400);
    
db.Albums
    .FindAll(db.Albums.GenreId == 1 && db.Albums.AlbumId > 400)
    .Select(db.Albums.Title);
    
db.Albums
    .All()
    .Select(db.Albums.Title)
    .Where(db.Albums.GenreId == 1 && db.Albums.AlbumId > 400);

Building Complex SimpleExpressions

Where takes a single SimpleExpression as a parameter. If the command it is qualifying already has filtering criteria on it, the new criteria is appending to the old using the AND operator. For example, both these commands:

db.Albums
    .All()
    .Select(db.Albums.Title)
    .Where(db.Albums.GenreId == 1 || db.Albums.GenreId == 2)
    .Where(db.Albums.ArtistId == 120);
    	
    db.Albums
    .FindAll(db.Albums.GenreId == 1 || db.Albums.GenreId == 2)
    .Select(db.Albums.Title)
    .Where(db.Albums.ArtistId == 120);

produce the following SQL. Note that parentheses are generated to maintain the correct interpretation of each filter criteria.

select [dbo].[Album].[Title]
FROM [dbo].[Album] 
WHERE (
    ([dbo].[Album].[GenreId] = @p1 OR [dbo].[Album].[GenreId] = @p2) 
	AND [dbo].[Album].[ArtistId] = @p3)

@p1 (Int32) = 1
@p2 (Int32) = 2
@p3 (Int32) = 120

If you wish to build up a set of filter criteria based on a set of business rules however, you may find it easier to build up a SimpleExpression separately and then add it to the command with a single call to Where rather than multiple calls. For example,

var expr1 = db.Albums.GenreId == 1;
var expr2 = db.Albums.GenreId == 2;
var expr3 = db.Albums.ArtistId == 120;
return db.Albums.All().Where((expr1 || expr2) && expr3);

This approach has the added advantage that you can concatenate SimpleExpressions with OR as well as AND.

Operators

Simple.Data supports the use of the following operators within SimpleExpressions

Arithmetic Operators

• + (Add)
• - (Subtract)
• * (Multiply)
• / (Divide)
• % (Modulo)

For example:

// Add
db.Albums.All().Where(db.Albums.AlbumId + db.Albums.ArtistId > 120);

// Subtract
db.Albums.All().Where(db.Albums.AlbumId - db.Albums.ArtistId < 130);

// Multiply
db.OrderDetails.All()
   .Where(db.OrderDetails.Quantity * db.OrderDetails.UnitPrice >= 50);

// Divide
db.OrderDetails.All()
   .Where(db.OrderDetails.UnitPrice / db.OrderDetails.Quantity <= 3);

// Modulo
db.OrderDetails.All()
   .Where(db.OrderDetails.UnitPrice % db.OrderDetails.Quantity != 4);

Comparison Operators

• == (equals)
• != (not equals)
• < (less than)
• <= (less than or equal to)
• > (greater than)
• >= (greater than or equal to)

For example:

// Equals
db.Albums.All().Where(db.Albums.ArtistId == 120);

// Not Equals
db.Albums.All().Where(db.Albums.GenreId != 1);

// Less than
db.Albums.All().Where(db.Albums.Price < 8.99);

// Less than or equal to
db.Albums.All().Where(db.Albums.Price <= 8.99);

// Greater than
db.Albums.All().Where(db.Albums.Price > 7.99);

// Greater than or equal to
db.Albums.All().Where(db.Albums.Price >= 7.99);

IN

Simple.Data supports the use of arrays to set a list of values which should (not) contain a field value, generating an IN or NOT IN clause as appropriate. As this translates to equating a field to an array, we can generate an IN clause in several ways. For example

// Embedded in a FindAllBy* or FindBy* method
db.Albums.FindAllByTitle(new []{"Nevermind", "Ten"}); 

// As the SimpleExpression parameter for Find or FindAll.
db.Albums.FindAll(db.Albums.Title == new[] {"Nevermind", "Ten" });

// As part of the SimpleExpression parameter for the Where method
db.Albums.All().Where(db.Albums.Title == new[] {"Nevermind", "Ten"});

All three calls generate the following SQL statement

select * from [dbo].[Album] 
WHERE [dbo].[Album].[Title] IN (@p1_0,@p1_1)

@p1_0 (String) = Nevermind
@p1_1 (String) = Ten

A NOT IN clause is written as an inequality statement so can only be created in two ways as FindAllBy* and FindBy* only support equality expressions.

// As the SimpleExpression parameter for Find or FindAll.
db.Albums.FindAll(db.Albums.GenreId != new[] {1, 3, 7});

// As part of the SimpleExpression parameter for the Where method
db.Albums.All().Where(db.Albums.GenreId != new[] {1, 3, 7});

These two calls generate the following SQL statement

select * from [dbo].[Album] 
WHERE [dbo].[Album].[GenreId] NOT IN (@p1_0,@p1_1,@p1_2)

@p1_0 (Int32) = 1
@p1_1 (Int32) = 3
@p1_2 (Int32) = 7

BETWEEN

Simple.Data supports the use of the to method to create integer and datetime ranges which should (not) contain a field value, generating a BETWEEN or NOT BETWEEN clause as appropriate.

//Add this using statement to use the to function
using Simple.Data;

// Embedded in a FindAllBy* or FindBy* method
db.Albums.FindAllByAlbumId(400.to(410)));

// As part of the SimpleExpression parameter for Find or FindAll
db.Albums.FindAll(db.Albums.AlbumId == 400.to(410)));

// As part of the SimpleExpression parameter for Where
db.Albums.All().Where(db.Albums.AlbumId == 400.to(410)));

All three calls generate the same SQL statement

select * from [dbo].[Album] 
WHERE [dbo].[Album].[AlbumId] BETWEEN @p1_start AND @p1_end

@p1_start (Int32) = 400
@p1_end (Int32) = 410

A NOT BETWEEN clause is written as an inequality statement so can only be created in two ways as FindAllBy* and FindBy* only support equality expressions.

//Add this using statement to use the to function
using Simple.Data;

// As part of the SimpleExpression parameter for Find or FindAll
db.Orders.FindAll(
   db.Orders.OrderDate != SqlDateTime.MinValue.Value.to(DateTime.Now)));

// As part of the SimpleExpression parameter for Where
db.Orders.All().Where(
   db.Orders.OrderDate != SqlDateTime.MinValue.Value.to(DateTime.Now)));

Both calls generate the same SQL statement

select * from [dbo].[Order] 
WHERE [dbo].[Order].[OrderDate] NOT BETWEEN @p1_start AND @p1_end
	
@p1_start (DateTime) = 01/01/1753 00:00:00
@p1_end (DateTime) = 26/06/2012 13:49:54

[ADOAdapter Only]

Note that Simple.Data will throw a System.Data.SqlTypes.SqlTypeException if the dates in the range are not valid SQL dates.

LIKE

Simple.Data uses the Like and NotLike functions to allow substring searches in string fields. Wildcard characters are supported

For example

// LIKE
db.Albums.All().Where(db.Albums.Title.Like("%Side Of The%");

// NOT LIKE
db.Albums.All().Where(db.Albums.Title.NotLike("%a%");

Null values

Simple.Data supports the generation of IS NULL and IS NOT NULL criteria using the following comparisons

• == null (IS NULL)
• != null (IS NOT NULL)

For example

// Embedded in a FindAllBy* or FindBy* method
db.Albums.FindAllByGenreId(null);
db.Albums.FindAllby(GenreId:null);

// As part of the SimpleExpression parameter for Find or FindAll
db.Albums.FindAll(db.Albums.GenreId == null);

// As part of the SimpleExpression parameter for Where
db.Albums.All().Where(db.Albums.GenreId == null);

All four calls generate the following SQL statement

select * from [dbo].[Album] 
WHERE [dbo].[Album].[GenreId] IS NULL

An IS NOT NULL clause is written as an inequality statement so can only be created in two ways as FindAllBy* and FindBy* only support equality expressions.

// As part of the SimpleExpression parameter for Find or FindAll
db.Albums.FindAll(db.Albums.GenreId != null);

// As part of the SimpleExpression parameter for Where
db.Albums.All().Where(db.Albums.GenreId != null);

Both these calls generate the following SQL statement

select * from [dbo].[Album] 
WHERE [dbo].[Album].[GenreId] IS NOT NULL