23 March 2007 in .Net | Comments enabled

One thing we’ve been playing with at Mindscape with the creation of Background Motion is LINQ for SQL (previously known as DLINQ). Over my time in software development I’ve found myself having to write paging code a few times. In itself it’s not hard but it is easy to do it in a dumb way if you don’t think beyond just restricting the dataset.

Every now and then I see code that pulls an entire dataset back from the database and then slices and dices it into a single page. This of course isn’t a very good idea but it does solve a basic issue when doing paging: knowing the total number of results which you need to know when displaying a paging control.

So you will need two queries, one to get the count, one to get the page. Still more efficient than transporting 10, 000 or more rows of data to your application.

How does LINQ for SQL make this easier? Check out this code snippet:

private IList<Contribution> GetWithinDays(int days,  int skip){

  return ModeratedContributions

    .Where(c => c.AddedOn >= DateTime.Now.AddDays(days))

    .OrderByDescending(c => c.AddedOn)

    .Skip(skip)

    .Take(PageSize)

    .ToList();

}

This is LINQ in action, this will return a collection of contributions which were added in the last 5 days, in descending order based on when the contribution was added. The key here is the Skip and Take methods. These allow to to chop of the start of the result set and Take will select the next number of rows from that point. Effectively this is doing your paging. Keep in mind that LINQ for SQL is converting this C# code into SQL so this paging functionality is being applied at the database, not in our application.

All you need to do is calculate the offset for the skip parameter and you’re done. In the code above, PageSize is defined within the scope of the class so is not passed in but this can be any integer for the number of results you want per page.

You really can’t get much easier than that. Now, just to get the count of the results, that’s easy enough with LINQ as well:

private int GetNumberWithinDays(int days)

{

  return ModeratedContributions

    .Where(c => c.AddedOn >= DateTime.Now.AddDays(days))

    .Count();

}

Now with those two pieces of data from our database, the results and total number of potential results we can go forward and implement some easy paging. Of course we control all the other information in this example such as the number of items on a page etc but overall you can see that LINQ enables these types of solutions easily.

For more information about LINQ for SQL check out this MSDN article.

- JD

Average Rating: 4.6 out of 5 based on 281 user reviews.

[ | | | | ]


2 comments. Add your own comment.

Jayson Go says 1 May 2007 @ 10:47

I’ve been building a site based on .NET3.5, LINQ and LINQ-TO-SQL, and paging, as with most community sites, is a common feature.
I’ve been getting my hands very dirty with these new technologies, and maybe its just a matter of preference, but couldn’t the same functionality be returned with:

private IList GetWithinDays(int days, int skip)
{
DateTime dateTime = DateTime.Now.AddDays(days);

return (from mc in ModeratedContributions
where mc.AddedOn >= dateTime
orderby mc.AddedOn descending
select mc).Skip(skip).Take(PageSize).ToList();
}

private int GetNumberWithinDays(int days)
{
DateTime dateTime = DateTime.Now.AddDays(days);

return (from mc in ModeratedContributions
where mc.AddedOn >= dateTime
select mc).Count();
}

In my opinion, the above looks cleaner because it shows it more like a query.

I added the DateTime dateTime line for performance and accuracy since because *I THINK* the lambda expressions, which are really then anonymous methods will execute against that value for each item.

Dave.Dolan says 13 August 2008 @ 06:15

I do not think that it would evaluate the lambda against each value. It rather uses the lambda to evaluate an expression tree that will generate a SQL statement. The provider is pretty darned clever. For example if you do this:

string [] strs = new string [] { “ABC”, “DEF”, “GHI” };

MyDataContext ctx = new MyDataContext();

var foozles = ctx.Foozles.Where(a => strs.Contains(a.Name));

will translate into the following SQL query:

select * from Foozle where Name in (“ABC”,”DEF”,”GHI”);

which is REALLY f’n cool. So don’t worry, expression trees generate SQL, they don’t run in realtime in LINQ to SQL. In LINQ to Object’s, yes you’re right it would because it’s calling a method in there somehweres.

Leave a Comment

Name (required)

E-mail (required - not published)

Website

Your comment: