How do I cache an IQueryable object?

Question

I've got this method that returns a Linq-to-SQL query for all the rows in a 'UserStatus' table:

public IQueryable<BLL.Entity.UserStatus> GetAll()
{
    var query = from e in _SelectDataContext.UserStatus
                select new BLL.Entity.UserStatus
                {
                    UserStatusId = e.UserStatusId,
                    Enum = e.Enum,
                    Name = e.Name
                };

    return query;
}

It's just a look-up table that will hardly ever change so I'd like to cache the results. I can convert it to a List<> and cache that, but I'd prefer to return an IQueryable object as other methods in the class depend on that. Can anyone help? Thanks.

Answer 1

You can't really do this--the Querable is more a pointer than the data. If you are caching, you are caching data, so you need to have the data loaded. Yapiskan has the right idea--cache the resulting ToList() and bring it back to AsQueryable().

Do note that the cached list won't have a DataContext so your query options will be limited to data loaded in the list.

Answer 2

I would have to ask what you are actually wanting to cache? The query or the results?

If its the results then the suggestions above make sense, but if you are wanting to cache the query for later use then may I suggest using CompiledQuery and then storing that somewhere...

Here is an article about CompiledQuery and how to use it.

Again it all depends on whether you want the the query to have been executed or not...

Answer 3

could query.ToList().AsQueryable() be a solution for you? not the best one sure.

Answer 4

Take a look at the Enterprise Library caching application block. It is a very good general purpose caching architecture.