CODEWITHSUNDEEP
sql-serverlinqpaginationsqlcachedependency
sagescrub
sagescrub

Reputation: 549

Caching paged LINQ results without using Skip() or Take()

I am using LINQ for paging data in my website. I am currently using Skip() and Take() to perform the paging. Now I want to cache the data using cache dependencies so that the the cache will invalidate if the data changes. However SQL Server's query notifications don't support the TOP expression. Are there any alternative ways to query a paged set of data with LINQ that doesn't generate TOP? Or another way to cache this data so that it invalidates?

Upvotes: 0

Views: 514

Answers (2)

Amy B
Amy B

Reputation: 110171

Pull your data in two passes:

// step1: get the IDs of the items in the current page.
List<int> customerIds = db.Customers
  .Where(filter)
  .OrderBy(c => c.FirstName)
  .ThenBy(c => c.CustomerID)
  .Select(c => c.CustomerID)
  .Skip(200)
  .Take(20)
  .ToList();

// step2: get the items for that page
List<Customer> customers = db.Customers
  .Where(c => customerIds.Contains(c.CustomerID))
  .ToList();

Upvotes: 1

James Curran
James Curran

Reputation: 103535

Cache the entire result set, and set the SqlDependancy to that.

Read the entire set from the cache, and then use Skip/Take on that.

Upvotes: 1

Related Questions