LINQ Exchange

I recently read a blog from a developer complaining that you cannot cache the result of a LINQ to SQL DataContext. I want to set the record straight and demonstrate how you can cache the result of  LINQ to SQL DataContext.

Before we continue, I would like to point out something. In the original post, the author was trying to access the SQL tables directly from LINQ to SQL. While it is possible to do this (and to cache the results), I do not recommend it. I always recommend using stored procedures instead. You gain several layers of security by using stored procedures: added security from sql injection attacks, and the ability for the DBA to allow only execute to the application user (thus preventing a hacker from being able to run their own dynamic SQL if they gain access).

For our example, let's assume we will be using a stored procedure called "sproc_Users_Get", which will return a list of application users and their associated ID. The first thing we need to do is to add a new LINQ to SQL class. Right click your project, Add New Item,  "LINQ to SQL Classes". I generally give this class the same name as the database it will access. For our example, let's use "MyDB". You will notice that Visual Studio will create a file called "MyDBDataContext.dbml"; this is the LINQ to SQL designer. Double click it.

Now that we have the designer open, you'll notice it has two columns. The column on the left is for adding tables from the database. The right column is for adding stored procedures. Using the Server Explorer, you will drag your stored procedure onto the right column. For this example, we added "sproc_Users_Get".

When you add a stored procedure, the designer creates two classes. The first class has the name of the stored procedure (sproc_Users_Get). The second class is used to hold the results of the stored procedure. This second class will have the stored procedure's name with a suffix of "Results" (sproc_Users_GetResults).

To call a stored procedure, you would use the following syntax:

MyDBDataContext mydb = new MyDBDataContext();

var results = mydb.sproc_Users_Get();

When you compile the code, Visual Studio changes the "var" declaration to be IEnumerable<sproc_Users_GetResults>.

The technique for caching the results of the stored procedure is simple: convert the results to a List<>, which will force immediate execution. You'll recall that LINQ does everything through deferred execution, unless you call it's GetEnumerator() function (or convert the results, which forces a call to GetEnumerator). To convert the results to a List<>, we need to use the ToList extension.

Example of Caching the Result of a LINQ to SQL DataContext

// create the datacontext object

MyDBDataContext mydb = new MyDBDataContext();

 

// call the stored procedure and convert the result to a List<>

List<sproc_Users_GetResults> resultList = mydb.sproc_Users_Get().ToList();

 

// cache the list for later user

Cache["UsersList"] = resultList;

 

By calling ToList(), we force the data to be immediately executed, and the results to be returned in a cacheable List<>. You can use this same technique for caching the results of a direct table query, but again, I strongly recommend against direct table queries.

 

If you have any questions, comments, or suggestions, please feel to post them in the LINQ Exchange Forum

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.