Skip to content

Getting started with Linq to Querystring Part 1 – Paging data

May 12, 2013

It’s been a little while now since I released Linq to Querystring into the wild… we’ve since solved a few issues and it’s been put to use in some real-world applications. Thanks to everyone who’s provided feedback so far!

So lets have a look now at some of the practical applications for Linq to Querystring (and for OData in general) from a beginners perspective. In this post I’ll take you through creating a sample table with paged data using Web API\Linq to Querystring from start to finish.

Getting set up

Fire up Visual Studio and start a new ASP.Net MVC 4 project:

New project

Choose a suitable name and click OK. Then on the next screen, select the Web API template:

Web api template

Leave the rest of the settings as default, and click OK again to create the project.

Once everything loads up, we just need to install LinqToQuerystring before we can get started. To do that, open the package manager console (View->Other Windows->Package Manager Console if it’s not open already), and type the following:

install-package LinqToQuerystring

If all goes well, you should see something like this:

Install package

Also make sure to add the WebAPI extension to make things even easier to use:

PM> install-package LinqToQuerystring.WebApi

Now we’re ready to get started.

Setting up the API

First we need to write some code in our API so that we can retrieve some values. Linq to Querystring can work with any type of data source or format, so long as your API method can return an IQueryable<>. Open up the ValuesController.cs file that was created for us when we started the project.

It will have the standard methods for the main HTTP verbs as usual… for this sample we’re only interested in retrieving multple records, so we can hose everything apart from the Get method.

Change this method to return an IQueryable instead of an IEnumerable; you’ll also need to use the AsQueryable() extension method on the return statement. Finally, add some more sample strings to the array and give them more imaginative values than just ‘value1′, ‘value2′ otherwise it’s very dull.

If you like you can hook this up to a source of complex objects, from Entity Framework or your favourite document database solution. I’ve just hard coded some values for simplicity as the example works just as well.

If everything has gone to plan, you should be able to fire up your solution and browse to http://localhost:<port>/api/values and get some data back:

Xml bleurgh

Ugh! XML. This isn’t the 90’s. Lets remove the XML formatter from the Web API config so we don’t have to look at it anymore.

Open up the WebApiConfig.cs file in the App_Start folder, and add the first two lines to the Register method so it looks like below:

Fire it up again, and viola… some nice friendly JSON:

Nice friendly json

Now we’ve got some test data, we can look at sorting out our UI.

On the client side

So what we now want to do is render our data into a table, and provide the user with some controls for paging the data. We’ll need to tell them how many records there are in total, allow them to choose how many records they want on each page, and provide a button to click that will retrieve the data.

We’ll go ahead and modify the template Index.cshtml that came with our project to include those elements we need. I’ve made mine look something like this (photoshopped for size):

gui sample

I’ve omitted it from this post for succinctness, but you can get the cshtml source here (or build it yourself if you’re not lazy!): https://gist.github.com/Roysvork/5564031

To make things easier, I’m going to use Knockout.JS to map the values and button click from our form controls onto a viewmodel, which will encapsulate all our functionality. If you’re not familiar with knockout, you can find out more here: http://knockoutjs.com/documentation/introduction.html.

To use knockout, you’ll need to reference it in Layout.cshtml… you can do this directly or use the bundle functionality in MVC 4. Anyways once you’ve done that… here’s the viewmodel and the javascript that fetches the data and wires it all up:

It’s quite straightforward, we have a getData function that makes the ajax call to our API which is also called when the page first loads. We have a bunch of observable properties, and then a pages computed observable that will provide a correct list of pages whenever the page size or record count changes. This provides the list of options for the page size drop down.

Fire up the app again and take a look at your handiwork. You should be able to see that the list of available pages changes as you select a different page size, and the data is displayed along with the correct total. Give yourself a cookie.

So what about the paging?

So now comes the hard part… well it would be if it wasn’t for Linq to Querystring. I’ve deliberately left this till last so you can see just how easy this is. First of all, we need to modify our API method to provide OData query support like so:

// GET api/values
[LinqToQueryable]
public IQueryable<string> Get()

Now on the client side, we can inform our API that we want to page the data via the OData query operators $top and $skip. As you might expect, $top specifies that we want a restricted number of results, and $skip tells our api to jump over a specified number of records beforehand.

All we need to do is modify our url to use the values from the model:

var skip = model.pageSize() * (model.currentPage() - 1);
$.get("/api/values?$top=" + model.pageSize() + "&$skip=" + skip, [...]

Very simple indeed. If you’re really paying attention though, you’ll notice there’s one last thing we need to do. Our count is now wrong as it doesn’t bring back the total number of records, only the number in the current page.

We can solve that by adding the OData $inlinecount=allpages query operator. Remember the JSON we got back earlier? After adding the inlinecount it now looks like this:

Inline count json

So now we can use the Count and Results properties to provide data for our model. With these tweaks in place, our final getData() implementation now looks like this.

Fire up the sample app for one last time, and we now have a working data paging implementation!

Paging example 1

Paging example 2

This is just a taste of what OData\Linq to Querystring has to offer. Check out Part 2 where I extend this sample to see how we can also perform filtering on our data.

Also feel free to take a look at the github page for the current project progress and features. You can also find the full source for this sample here: https://github.com/Roysvork/LinqToQuerystringPagingSample

Pete

References:

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: