Caching in MVC Application With Entity Framework Using Query Notification

Jignesh Trivedi

5y70.6k11100Article

Entity Framework MVC ApplicationCaching in MVC applicationQuery Notification

11 Comments

Join the conversation! Your thoughts help the community grow.

Ajeet Mishra7y ago
Hey jignesh can tell me why this line giving exception SqlCommand command = new SqlCommand(((System.Data.Objects.ObjectQuery).... in this line
- Jignesh Trivedi7y ago
  Hi check your database setting. Is service broker is enabled?
- Jignesh Trivedi7y ago
  Also refer article, it help you understand what kind of query we can write for notification. https://www.c-sharpcorner.com/UploadFile/ff2f08/query-notification-in-sql-server/
tushar kshirsagar9y ago
This example do not work for ENABLE_BROKER for Azure; So useless but for full VM it may work not checked !
- Jignesh Trivedi9y ago
  Even I did not check this example with Azure. Let me check and get back to you.
sonubaba manani10y ago
Unable to cast object of type 'System.Data.Entity.DbSe to type 'System.Data.Entity.Core.Objects.ObjectQuery
- Jignesh Trivedi10y ago
  In which line you get error? hope you use latest Entity framework version.
- Kailash Polai8y ago
  SqlCommand command = new SqlCommand(((System.Data.Objects.ObjectQuery).... in this line
- Vinay Singh6y ago
  Hi Add reference to System.Data.Entity
sonubaba manani10y ago
i used your code but getting error
sonubaba manani10y ago
good article!!!!

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>
}

MVC Application

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.