Getting started with Linq to Querystring Part 2 – Filtering Data

In this second post in my introductory series, I’m going to take a look at how we can filter the results from our API using OData\Linq to Querystring. I’m going to be building on the paging sample from the last post, which you can find here if you want to follow along: https://github.com/Roysvork/LinqToQuerystringPagingSample.

This post is intended as a step by step guide, so if you’re just looking for a reference on what you can do with Linq to Querystring, feel free to skip the first two sections.

Making things more interesting

Because our previous sample only contained a single string value (not very interesting for filtering purposes!), I’ve extended things slightly as a starting point for this post. We’re still hardcoding the data, but there’s now a concrete class with multiple properties so we have something to play around with:

	public class Movie
	{
	public string Title { get; set; }
	public DateTime ReleaseDate { get; set; }
	public string Director { get; set; }
	public int MetaScore { get; set; }
	public bool Recommended { get; set; }
	}

view raw

movie.cs

hosted with ❤ by GitHub

This really seemed like a good idea for a demo class until it came to populating it with sample data. I’m not a movie buff… so after a good amount of googling here’s my test data:

	[LinqToQueryable]
	public IQueryable<Movie> Get()
	{
	return new List<Movie>
	{
	new Movie {
	Title = "Matrix (The)", ReleaseDate = new DateTime(1999, 3, 31),
	MetaScore = 73, Director = "Andy Wachowski\\Lana Wachowski",
	Recommended = true
	},
	new Movie {
	Title = "Avatar", ReleaseDate = new DateTime(2009, 12, 17),
	MetaScore = 83, Director = "James Cameron",
	Recommended = false
	},
	new Movie {
	Title = "Spaceballs", ReleaseDate = new DateTime(1987, 6, 24),
	Director = "Mel Brooks", MetaScore = 46,
	Recommended = true
	},
	new Movie {
	Title = "Return of the Jedi", ReleaseDate = new DateTime(1983, 6, 2),
	MetaScore = 52, Director = "Richard Marquand",
	Recommended = true
	},
	new Movie {
	Title = "Fellowship of the ring (The)", ReleaseDate = new DateTime(2001, 12, 10),
	MetaScore = 92, Director = "Peter Jackson",
	Recommended = true
	}
	}.AsQueryable();
	}

view raw

ValuesController.cs

hosted with ❤ by GitHub

I’ve made sure to include a mix of different property types so we can apply a range of filters, so if you’re using your own test data make sure to do this too.

Rendering results as a table

Last time we used knockout to bind the data coming back from our api into a table, and the markup looked like this:

	<tbody data-bind="foreach: records">
	<tr>
	<td data-bind="text: $data"></td>
	</tr>
	</tbody>

view raw

gistfile1.html

hosted with ❤ by GitHub

This was fine for retrieving a list of single values, but now we need it to display all the properties of each movie. We could just hard code the columns, but there’s a little trick we can use instead.

As objects in Javascript are just collections of properties, we can iterate over each one, read the property name for each and then add these values to an array from which we can bind our headers. Here’s the modified function that gets the data from our api:

	model.getData = function () {
	var skip = model.pageSize() * (model.currentPage() – 1);
	$.get("/api/values?$top=" + model.pageSize() + "&$skip=" + skip + "&$inlinecount=allpages", function (data) {
	model.count(data.Count);
	model.records(data.Results);
	// If we have a valid results set, iterate over each property in the first row to get headers
	if (data.Results && data.Results.length > 0) {
	for (prop in data.Results[0]) {
	if (data.Results[0].hasOwnProperty(prop)) {
	model.headings.push(prop);
	}
	}
	}
	});
	};

view raw

gistfile1.js

hosted with ❤ by GitHub

Note that we’ve also added a new observable array to our viewmodel called headers. And to take advantage of that in our UI, we now just need to tweak our table html also:

<table>

<thead>

<tr data-bind="foreach: headings">

<th data-bind="text: $data"></th>

</tr>

</thead>

<tbody data-bind="foreach: { data: records, as: 'record' }">

<tr data-bind="foreach: { data: $root.headings, as: 'field' }">

<td data-bind="text: record[field]"></td>

</tr>

</tbody>

</table>

view raw

gistfile1.html

hosted with ❤ by GitHub

Remember, in Javascript accessing an object’s properties directly by name is exactly equivilent to accessing them via indexer syntax, i.e:

record.Title === record["Title"]

We’re using this trick, and nested foreach bindings to render each column. To use the indexer syntax, we need to be able to refer to values from both foreach, which we can do using aliases. If these bindings seem a little confusing, take a look at http://knockoutjs.com/documentation/foreach-binding.html, and see Note 3: Using “as” to give an alias to “foreach” items

Et viola, a nice table. The labels aren’t perfect and the dates are ugly, but it was very simple to achieve.

The $filter operator

So, now that we’ve got some more meaningful results, we can get back to the task at hand. In OData, we use the $filter= query operator to tell our API that we want to apply a filter to our data. Linq to Querystring takes this query filter and then converts it to a Linq ‘Where’ expression.

A few examples (it’s worth noting that the whitespace here is important):

http://localhost/api/values?$filter=Title eq 'Avatar'
http://localhost/api/values?$filter=MetaScore ge 60
http://localhost/api/values?$filter=Recommended eq true
http://localhost/api/values?$filter=not Recommended

No prizes for guessing what the first one does of course, but some of the other operators such as Greater than or Equal aren’t so obvious. These examples give us an idea of the general format for a filter expression in OData; we can reference properties on our model, specify an equality operator, strings are enclosed in single quotes and we can even specify unary boolean expressions.

Here’s the full list of logical operators from the OData v2 specification:

Operator	Description	Example
Logical Operators
Eq	Equal	/Suppliers?$filter=City eq ‘Redmond’
Ne	Not equal	/Suppliers?$filter=City ne ‘London’
Gt	Greater than	/Products?$filter=Price gt 20
Ge	Greater than or equal	/Products?$filter=Price ge 10
Lt	Less than	/Products?$filter=Price lt 20
Le	Less than or equal	/Products?$filter=Price le 100
And	Logical and	/Products?$filter=Price le 200 and Price gt 3.5
Or	Logical or	/Products?$filter=Price le 3.5 or Price gt 200
Not	Logical negation	/Products?$filter=not StockAvailable
Grouping Operators
()	Precedence grouping	/Products?$filter=Price lt 30 or (City eq ‘London’ and Price lt 50)

Functions

The full OData specification provides a whole host of functions that allow us to manipulate values within our expressions. Linq to Querystring currently supports a very basic subset of these, which will grow as development continues.

Currently only three string functions are supported, the bare minimum which allow us to do useful string searches:

Function	Example
String Functions
bool substringof(string po, string p1)	/Customers?$filter=substringof(‘Alfreds’, CompanyName)
bool endswith(string p0, string p1)	/Customers?$filter=endswith(CompanyName, ‘Futterkiste’)
bool startswith(string p0, string p1)	/Customers?$filter=startswith(CompanyName, ‘Alfr’)

Escape characters

Like all string comparisons, we need to be able to filter using escape characters to indicate certain values. OData is no exception, and Linq to Querystring uses the following escape sequences:

Sequence	Meaning
\\	\ (backslash)
\t	Tab
\b	Non-destructive backspace
\n	Newline
\f	Line feed
\r	Carriage return
\’	‘ (single quote)
”	‘ (single quote – alternate)

Please note that while these work with Linq to Querystring, they may or may not be compatible with other OData providers.

Creating a basic search UI

Hopefully that all makes sense, so back to the sample project. We now want to add the ability to specify an OData filter when pulling down data from our API. We could do this manually like we did with the paging, but it’s a lot more complex.

For our search UI I’m going to use a jQuery plugin called OData filter UI, which will take care of generating the filter string for us. Currently in pre-release, this plugin will be the subject of it’s own post in this series at a later date. You can follow progress on the github page. For now, install the plugin using nuget:

Install-Package jQuery.ODataFilterUI -Pre

Make sure you’ve added the jquery.odatafilterui-0.1.js file it to your bundles or otherwise included it in the page. In order to use the plugin we add a textbox as a base and then apply the plugin which will then create the more complex bits of the UI. Here’s the markup and the js code that invokes the plugin and tell it what our fields are:

	<li class="two">
	<h5>Choose a page</h5>
	<select data-bind="options: pages, value: currentPage"></select>
	</li>
	<!– New textbox used as a base for the filter ui –>
	<li class="three">
	<h5>Filter the results</h5>
	<input type="text" id="filter" />
	</li>
	<li class="four">
	<h5>See the results</h5>
	…
	….

view raw

1-markup.html

hosted with ❤ by GitHub

	// Get data and bind viewmodel on page load
	$(document).ready(function () {
	model.getData();
	ko.applyBindings(model);
	model.FilterModel = $("#filter").oDataFilterUI({
	Fields: [
	{ text: "Title", value: "Title", type: "string" },
	{ text: "Release Date", value: "ReleaseDate", type: "datetime" },
	{ text: "Director", value: "Director", type: "string" },
	{ text: "MetaScore", value: "MetaScore", type: "int" },
	{ text: "Recommended", value: "Recommended", type: "bool" }]
	}).Model;
	});

view raw

2-code.js

hosted with ❤ by GitHub

Because the plugin needs to be flexible enough to fit into any UI, it comes with no default styling. I’ve neatened things up a bit using css you can see in this gist if you like: https://gist.github.com/Roysvork/a4d067e9550d32dc74b8. Anyways if you choose to or not, you should see something like the following:

Have a play around with the UI to familiarise yourself… it’s fairly straightforward to add or remove filters. You’ll see that for each data type the contents of the operator drop down change accordingly, and also the input type reflects this too. Currently each filter that you add will get ‘ANDed’ together.

All thats left to do now is wire up the filter to our api call. Here’s the final version of the getData function including paging & now filtering:

	model.getData = function() {
	var top = "$top=" + model.pageSize();
	var skip = "$skip=" + model.pageSize() * (model.currentPage() – 1);
	var inlinecount = "$inlinecount=allpages";
	var filter = model.FilterModel ? model.FilterModel.getODataFilter() : "";
	var oDataOptions = [top, skip, inlinecount];
	if (filter) { oDataOptions.splice(0, 0, filter); }
	$.get("/api/values?" + oDataOptions.join("&"), function(data) {
	model.count(data.Count);
	model.records(data.Results);
	model.headings([]);
	if (data.Results && data.Results.length > 0) {
	for (prop in data.Results[0]) {
	if (data.Results[0].hasOwnProperty(prop)) {
	model.headings.push(prop);
	}
	}
	}
	});
	}

view raw

gistfile1.js

hosted with ❤ by GitHub

As you can see, the OData Filter UI plugin has done the hard work of constructing the filter string for us via the getODataFilter() method.

We’ve also refactored the creation of the url to ensure we use ? and & to seperate querystring elements and the url accordingly.

Try out a few different filters, and use your favourite debugger to inspect the url that gets generated. Here are a few examples:

http://localhost:54972/api/values?$filter=ReleaseDate lt datetime'2000-01-01T00:00'&$top=5&$skip=0&$inlinecount=allpages

http://localhost:54972/api/values?$filter=Recommended eq true and MetaScore gt 55&$top=5&$skip=0&$inlinecount=allpages

http://localhost:54972/api/values?$filter=substringof('(The)',Title)&$top=5&$skip=0&$inlinecount=allpages

Summary

Starting with the code from Part 1, we’ve changed the test data to return a complex type with different properties, and updated the rendering of the results to reflect this.

We’ve looked at the OData syntax for filtering data, comparison operators, escape sequences and some of the string functions available. We’ve also seen how we can use the jQuery.ODataFilterUI plugin to provide a basic search UI.

Once again, you can check out the Linq to Querystring github page here: https://github.com/Roysvork/LinqToQuerystring and if you want to download the final source for the example in the post you can find that here: https://github.com/Roysvork/LinqToQuerystringFilteringSample

Stay tuned for the next few posts in the series, in which we’ll cover ordering of results, dealing with complex properties and collections, and how Linq to Querystring can work with Mongo DB to query loosely typed data.

Pete

References

https://roysvork.wordpress.com/2013/05/12/getting-started-with-linq-to-querystring-part-1-paging-data/
https://github.com/Roysvork/LinqToQuerystringPagingSample
http://www.odata.org/documentation/odata-v2-documentation/uri-conventions/#45_Filter_System_Query_Option_filter
http://knockoutjs.com/documentation/foreach-binding.html
http://github.com/roysvork/jquery.odatafilterui
https://gist.github.com/Roysvork/a4d067e9550d32dc74b8
https://github.com/Roysvork/LinqToQuerystring
https://github.com/Roysvork/LinqToQuerystringFilteringSample

Advertisement