Caching in MVC Application With Entity Framework Using Query Notification

Introduction

Caching is very important for every web application. It allows us to store frequently used data in server memory that is available for all users. Query Notification allows us to have SQL cache dependency. Query Notification is very efficient and it allows applications to be notified when data has changed. The SqlDependency represents a Query Notification between application and SQL Server instance. Applications can create an object of SqlDependency and register it to receive a notification.

Procedure to Create Caching mechanism in MVC with Entity Framework

Step 1: Enable Service Broker and the Trustworthy property set in the database as in the following:

ALTER DATABASE [Testdb] SET ENABLE_BROKER;
ALTER DATABASE [Testdb] SET TRUSTWORTHY ON;

Step 2: Create a table and insert some dummy data as in the following:

 

USE [Testdb]

CREATE TABLE [dbo].[CategoryMaster](
                [Id] [int] IDENTITY(1,1) NOT NULL,
                [Name] [varchar](50) NOT NULL,
                [Description] [varchar](50) NOT NULL,
CONSTRAINT [PK_CategoryMaster] PRIMARY KEY CLUSTERED
(
                [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

USE [Testdb]
GO
SET IDENTITY_INSERT [dbo].[CategoryMaster] ON
INSERT [dbo].[CategoryMaster] ([Id], [Name], [Description]) VALUES (1, N'Test 1', N'Test 1')
INSERT [dbo].[CategoryMaster] ([Id], [Name], [Description]) VALUES (2, N'Test 2', N'Test 2')
INSERT [dbo].[CategoryMaster] ([Id], [Name], [Description]) VALUES (3, N'Test 3', N'Test 3')
INSERT [dbo].[CategoryMaster] ([Id], [Name], [Description]) VALUES (4, N'Test 4', N'Test 4')
INSERT [dbo].[CategoryMaster] ([Id], [Name], [Description]) VALUES (5, N'Test 5', N'Test 5')
INSERT [dbo].[CategoryMaster] ([Id], [Name], [Description]) VALUES (6, N'Test 6', N'Test 6')
INSERT [dbo].[CategoryMaster] ([Id], [Name], [Description]) VALUES (7, N'Test 7', N'Test 7')
SET IDENTITY_INSERT [dbo].[CategoryMaster] OFF

Step 3: Register the SQL Server instance to get notification.

We can use an Application_Start event of Global.asax to register the SQL Server instance to get notification the same as we can use an Application_End event of Global.asax to deregister the SQL Dependency.

 

private static EntityConnectionStringBuilder entityConnectionString = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["TestdbEntities"].ConnectionString);

protected void Application_Start()
{
    string connectionString = entityConnectionString.ProviderConnectionString;
    System.Data.SqlClient.SqlDependency.Start(connectionString);
}
protected void Application_End(object sender, EventArgs e)
{
    string connectionString = entityConnectionString.ProviderConnectionString;
    System.Data.SqlClient.SqlDependency.Stop(connectionString);
}

Step 4:  Creating Caching Class

MVC does not have an object of the System.Web.Caching.Cache class. This class object can be goten from HttpContext.Current. In this class data is loaded either from a cache or from a database. It is dependent data within a cache, in other words if data is present in the cache then this class returns data from the cache else it retrieves data from the database and sets the cache dependency.

 

public class Caching
{
    private static EntityConnectionStringBuilder entityConnectionString = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["TestdbEntities"].ConnectionString);
    public static IEnumerable<CategoryMaster> GetCategoryData()
    {
        IEnumerable<CategoryMaster> categoryData = HttpContext.Current.Cache.Get("Category") as IEnumerable<CategoryMaster>;
        if (categoryData == null)
        {
            using (var context = new TestdbEntities())
            {
                IQueryable<CategoryMaster> categoryDataCache = context.CategoryMasters;
                using (SqlConnection connection = new SqlConnection(entityConnectionString.ProviderConnectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(((System.Data.Objects.ObjectQuery)categoryDataCache).ToTraceString(), connection);
                    SqlCacheDependency dependency = new SqlCacheDependency(command);
                    categoryData = categoryDataCache.ToList();
                    HttpContext.Current.Cache.Insert("Category", categoryData, dependency);
                    command.ExecuteNonQuery();
                }
            }
        }
        return categoryData;
    }
}

Example Controller Code

 

public ActionResult Index()
{
    ViewBag.Message = "Welcome to ASP.NET MVC!";
    IEnumerable<CategoryMaster> categoryData = Caching.GetCategoryData();
    return View(categoryData);
}

Example View Page Code 

 

@{
    ViewBag.Title = "Home Page";
}
@model IEnumerable<MvcApplication2.CategoryMaster>

<h2>@ViewBag.Message</h2>
@if (Model != null)
{
    <table>
        <tr>
            <td style="width:50px">Id</td>
            <td style="width:150px">Name</td>
            <td style="width:150px">Description</td>
        </tr>
        @foreach (var data in Model)
        {
            <tr>
                <td>@data.Id</td>
                <td>@data.Name</td>
                <td>@data.Description</td>
            </tr>
        }
    </table>
}
else
{
    <p>No Data Found.</p>
}

Summary

Query Notification and a SQLDependency object allows an application to be notified when data has changed. It does not matter how the data has changed. Sometimes Entity Framework generates a complex query that is not supported by Query Notification.