ADO.NET Data Services Logical Operators

In this post we will look at Logical Operators in ADO.NET Data Services. These operators can be used as filter expressions in our requests and ADO.NET Data Services applies the filter before results are returned. We will use Data Services generated over Adventureworks Lite database. If you are new to ADO.NET Data Services and would like to know how to generate ADO.NET Data Service then please see this article.

One thing to keep in mind is that filter expressions are case sensitive. And the syntax of applying filters in ADO.NET Data Services is

<url>?$filter=<your filter>

Let’s now look at all logical operators

Equal To

Parameter: eq

Equal To evaluates true when both operands have the same value. Here I am retrieving products where the value of Color matches “Yellow”.

http://localhost:6803/DataService.svc/Product?$filter=Color eq ‘Yellow’

Logical operator can also be applied in code through a LINQ query.

AdventureWorksEntities context = new AdventureWorksEntities(uri);
DataServiceQuery<Product> products = context.CreateQuery<Product>(@"/Product");

var query = from p in products
  where p.Color == "Yellow"
  select p;

foreach (var item in query)
{
  Console.WriteLine(item.ProductID + "\t" + item.Color);
}

Not Equal To

Parameter: ne

Not equal is opposite of equal to and evaluates true when both operands do not have the same value. Here I am retrieving products where the value of Color is NOT “Yellow”.

http://localhost:6803/DataService.svc/Product?$filter=Color ne ‘Yellow’

Following code will also achieve the same result through a LINQ query.

AdventureWorksEntities context = new AdventureWorksEntities(uri);
DataServiceQuery<Product> products = context.CreateQuery<Product>(@"/Product");

var query = from p in products
  where p.Color != "Yellow"
  select p;

foreach (var item in query)
{
  Console.WriteLine(item.ProductID + "\t" + item.Color);
}

Less than

Parameter: lt

Less than evaluates true when first operand is less than second. In this example I get all products where ListPrice is less than 20.

http://localhost:6803/DataService.svc/Product?$filter=ListPrice lt 20

Here is the C# version of Less than operator.

AdventureWorksEntities context = new AdventureWorksEntities(uri);
DataServiceQuery<Product> products = context.CreateQuery<Product>(@"/Product");

var query = from p in products
  where p.ListPrice < 20
  select p;

foreach (var item in query)
{
  Console.WriteLine(item.ProductID + "\t" + item.ListPrice);
}

Less than or equal to

Parameter: le

Less than or equal to evaluates true when first operand is either less than or equal to second operand. In this example I am retrieving all products where ListPrice is less than or equal to 9.5

http://localhost:6803/DataService.svc/Product?$filter=ListPrice le 9.5

And C# equivalent will look like this.

AdventureWorksEntities context = new AdventureWorksEntities(uri);
DataServiceQuery<Product> products = context.CreateQuery<Product>(@"/Product");

var query = from p in products
  where p.ListPrice <= 9.5m
  select p;

foreach (var item in query)
{
  Console.WriteLine(item.ProductID + "\t" + item.ListPrice);
}

Greater than

Parameter: gt

Greater than evaluates true if the first operand is greater than the second. In this example I am retrieving all products where ListPrice is greater than 9.5

http://localhost:6803/DataService.svc/Product?$filter=ListPrice gt 9.5

Greater than is a no brainer with a LINQ query.

AdventureWorksEntities context = new AdventureWorksEntities(uri);
DataServiceQuery<Product> products = context.CreateQuery<Product>(@"/Product");

var query = from p in products
  here p.ListPrice > 9.5m
  select p;

foreach (var item in query)
{
  Console.WriteLine(item.ProductID + "\t" + item.ListPrice);
}

Greater than or equal to

Parameter: ge

Greater than or equal to evaluates true if the first operand is greater than or equal to second. Here is  an example. I am retrieving all products where ListPrice is greater than or equal to 9.5

http://localhost:6803/DataService.svc/Product?$filter=ListPrice ge 9.5

C# example

AdventureWorksEntities context = new AdventureWorksEntities(uri);
DataServiceQuery<Product> products = context.CreateQuery<Product>(@"/Product");

var query = from p in products
  where p.ListPrice >= 9.5m
  select p;

foreach (var item in query)
{
  Console.WriteLine(item.ProductID + "\t" + item.ListPrice);
}

And

Parameter: and

“And” evaluates true when both operands are true. In this example I am retrieving all products where the Color is Black and ListPrice is greater than 1000.

http://localhost:6803/DataService.svc/Product?$filter=(Color eq ‘Black’) and (ListPrice gt 1000)

Same results can be retrieved in C# with this code.

AdventureWorksEntities context = new AdventureWorksEntities(uri);
DataServiceQuery<Product> products = context.CreateQuery<Product>(@"/Product");

var query = from p in products
  where (p.Color == "Black")
    && (p.ListPrice > 1000)
  select p;

foreach (var item in query)
{
  Console.WriteLine(item.Color + "\t" + item.ListPrice);
}

Or

Parameter: or

“Or” evaluates true when either operand is true. In this example I am retrieving all products where either the Color is Black or ListPrice is greater than 1000.

http://localhost:6803/DataService.svc/Product?$filter=(Color eq ‘Black’) or (ListPrice gt 1000)

Now with C#.

AdventureWorksEntities context = new AdventureWorksEntities(uri);
DataServiceQuery<Product> products = context.CreateQuery<Product>(@"/Product");

var query = from p in products
  where (p.Color == "Black")
    || (p.ListPrice > 1000)
  select p;

foreach (var item in query)
{
  Console.WriteLine(item.Color + "\t" + item.ListPrice);
}

Not

Parameter: not

“Not” evaluates true if operand is false. An example can be retrieving all products where the Color is not Black.

http://localhost:6803/DataService.svc/Product?$filter=not (Color eq ‘Black’)

While this can be achieved using not equal to operator they are not the same. I’ll leave C# code for this one for you :)

Conclusion

This article demonstrated all logical operators available for ADO.NET Data Services. In a future post I will cover other types of operators for ADO.NET Data Services.