In this post I am going to provide you with a hands-on example on how to use second level caching with Entity Framework 6.0 version. Second Level Caching is one of the features that is not present out-of-the-box in Entity Framework.

Second level cache is a query cache. The results of SQL commands are stored in the cache, so that the same SQL commands retrieve their data from the Cache rather than executing the query again against the underlying provider. This can have a performance boost for your application and results in less activity against your database, at the cost of increased memory.

There is currently a project in codeplex, EntityFramework.Cache that enables us caching of query results for EF 6.1 applications.

Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.

Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.

1) I will create an empty ASP.Net Application (Web Forms Application) and I give it the name EFSecondLevelCaching.  I am using Visual Studio 2013 Ultimate edition.

2) I add a new web forms page in the application. Leave the default name. Αdd  a Gridview control to the web form.

3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.

4) I will add a class library project to the solution, namely DataAccess. I will add an ADO.Net Entity data model using Database First to the class library project. Follow the wizzard steps, create the connection string and then import into the conceptual model the Person.Person table which will become an entity in the domain model. 

In the am going to install the “EntityFramework.Cache”  from the Nuget Library in this project.

5) I will write a sql statement to acquire rows from the Person.Person table. Connect to your SQL Server instance. Type the following query.

SELECT  *

FROM    [AdventureWorks2014].[Person].[Person]

WHERE   [FirstName] = 'Rob'

This query after execution will return 5 rows. 

6) In the webform1.aspx.cs file ( public partial class WebForm1) I will create a new method that returns the persons with FirstName = Rob

        private static List<Person> SelectAll()

        {

            AdventureWorks2014Entities ctx = new AdventureWorks2014Entities();

            var query = from p in ctx.People

                        where p.FirstName == "Rob"

                        orderby p.BusinessEntityID ascending

                        select p;

            return query.ToList();

        }

Ι just return all the persons with the first name Rob from the database. 

7) In our webform in the Page_Load event we write the following code.

  protected void Page_Load(object sender, EventArgs e)

        {

         GridView1.DataSource = SelectAll();

            GridView1.DataBind();

            Response.Write("</hr>");

        }

Make sure you build your application but do not run it just yet. Make sure that you copy the connection string from the App.config to the Web.config

  <connectionStrings>

    <add name="AdventureWorks2014Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=AdventureWorks2014;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

  </connectionStrings>

8) I will use the Sql Server Profiler to see what queries are hitting my database. I launch the SQL Server Profiler, I connect to the SQL Server Enterprise edition instance in my machine and then create a trace with the SQL:BatchCompleted, SQL:BatchStarting event and a filter in the DatabaseName column with the name AdventureWorks2014.

Build and run the application. You will see that the first time the page loads the application hits the database. If you refresh the web page the database is hit every time. 

Have a look at the picture below.

9) In the Person.cs class file add the code below

 public class Configuration : DbConfiguration

    {

        public Configuration()

        {

            var transactionHandler = new CacheTransactionHandler(new InMemoryCache());

            AddInterceptor(transactionHandler);

            var cachingPolicy = new CachingPolicy();

            Loaded +=

              (sender, args) => args.ReplaceService<DbProviderServices>(

                (s, _) => new CachingProviderServices(s, transactionHandler,

                  cachingPolicy));

        }

    }

As I said earlier I have already installed the EF Second level cache package ,“EntityFramework.Cache” , from Nuget Library.

Now we need to tell EF to use caching by configuring the caching provider and the transaction handler.

That is why we have created the DbConfiguration class. In the constructor of your DbConfiguration derived class you need to set the transaction interceptor and the Loaded event handler which will be responsible for replacing the provider. 

Have a look at the picture below

Build and run your application. The first time the page loads the database is it. When I reload the page the database is not hit again and the queries are served from the cache.

Have a look at the picture below

Hope it helps!!!