locked
LINQ, SqlCacheDependency + Stored Procedures RRS feed

  • Question

  •  

    I am using Visual Studio 2008, C# and SQL Server 2008.

     

    I have seen examples on the NET that associates LINQ with SqlCacheDependency.

     

    E.g. http://www.writebetterbits.com/2008/01/overview-of-sql-server-2005-database_11.html

     

    However I need this to work with an existing stored procedure (LINQ to SQL).

     

    I have tried the following, and although it doesn't complain, it also doesn't work. When the table changes the cache is not being invalidated.

     

    Code Snippet

    SqlCacheDependency lookupTable = null;

    LookupDBDataContext lookup = null;
    ISingleResult<stx_Lookup_SelectByTypeResult> sp = null;
    List<stx_Lookup_SelectByTypeResult> LookupList = null;

     

    try
    {

    lookup = new LookupDBDataContext();
    sp = lookup.stx_Lookup_SelectByType(strType);

     

    // Convert s/p to a array list
    LookupList = Enumerable.ToList<stx_Lookup_SelectByTypeResult>(sp);

     

    // The below converts the array list to queryable

    lookupTable = new SqlCacheDependency(lookup.GetCommand(LookupList.AsQueryable()) as SqlCommand);

     

         // Now add to the cache

         HttpRuntime.Cache.Insert(strType, LookupList, lookupTable);

    }
    catch (Exception Ex)
    {
       throw (Ex);

    }

     

     

    I hope the above code example is enough.

     

    The cache dependency does work when using standard ADO.NET with a SqlCommand object so I know it's just my LINQ code.

     

     

    Kind regards

     

    Mark Eaton

     

     

    Tuesday, March 11, 2008 2:16 AM

Answers

  • Hello. I use SqlCacheDependency with LINQ. But not stored procedures... maybe you can use my extensionmethod anyway?

     

    There is a chance that the example code contains some typeo´s. Wrote it directly in the post.

    The SqlCacheDependency will not work when the LINQquery is to complex. You can do a DataContext.Log to see if your LINQ-queries match the demands for SqlCacheDependency.

     

    My queries run ok, and i´m using MsSql2k5

     

    The Code:

    Code Snippet

    public static class MyExtensions

    {

    public static List<T> LinqCache<T>(this IQueryable<T> q, DataContext dc, string cacheId)

    {

    try

    {

    List<T> objCache = (List<T>)System.web.HttpRuntime.Cache.Get(cacheId);

    if(objCache == null)

    {

    string connStr = dc.Connection.ConnectionString;

    string sqlCmd = dc.GetCommand(q).CommandText;

    using(SqlConnection conn = new SqlConnection(connStr))

    {

    conn.Open();

    using(SqlCommand cmd = new SqlCommand(sqlCmd, conn))

    {

    string Notificationtable = q.ElementType.Name;

    SqlCacheDependencyAdmin.EnableNotifications(connStr);

    if(!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable))

    SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable);

    SqlCacheDependency sqldep = new SqlCacheDependency(cmd);

    dc.refresh(RefreshMode.OverwriteCurrentValues, q);

    cmd.ExecuteNonQuery();

    objCache = q.ToList();

    System.web.HttpRuntime.Cache.Insert(cacheId, objCache, sqldep);

    }

    }

    }

    return objCache;

    }

    catch(Exception ex)

    {

    throw ex;

    }

    }

    }

     

     

    You´ll get intellisense to the extensionmethod on every LinqToSqlQuery

    Example:

    Code Snippet

    var q = (from x in dc where and so on...);

    foreach(List<MyType> o in q.LinqCache((DataContext)myDC, "MyCacheId")

    {

    ...

    }

     

     

     

     

     

    Tuesday, June 10, 2008 10:02 AM

All replies

  • Hi There.

    Is this problem solved?

     

    Regards

    UTB

     

    Monday, June 9, 2008 1:28 PM
  • Hi Mark,

     

    I'm not familiar yet with SQL 2008 but I know for SQL 2005 you had to setup and configure some things on the SQL Server side to enable Cache Dependency to work on the code side.  Have you done this?

     

    You need to enable:

    1. the SQL Server to use Cache Dependency - using "aspnet_regsql.exe"
    2. any Tables for using Cache Dependency - again, using "aspnet_regsql.exe"
    3. I'm pretty sure you can do this for Views and StoredProcs but I've never tried it.
    4. If you have a web App, I'd enable and specify the SQL Cache settings in the web.config

    Code Snippet

    <system.web>

        <caching>

            <sqlCacheDependency enabled="true">

    <databases>

    <add name="Northwind" connectionStringName="AppConnectionString1"

    pollTime="500" />

    </databases>

    </sqlCacheDependency>

        </caching>

    </system.web>

     

     

     

     

    HTH

     

    Monday, June 9, 2008 9:15 PM
  • oops 

     

    I should have read your post more carefully...you mention that under normal ADO.NET it works - your only issue is using LINQ to SQL.  My apologies.
    Monday, June 9, 2008 9:24 PM
  • I am not particularly familiar with the SqlCacheDependency object but it would seem from the description that

     

    "Establishes a relationship between an item stored in an ASP.NET application's Cache object and either a specific SQL Server database table or the results of a SQL Server 2005 query. This class cannot be inherited."

     

    LINQ to SQL creates objects based on the results from queries but doesn't keep the underlying query around nor does it store the results in the ASP.NET application cache so I can't imagine LINQ to SQL would be updated by using SqlCacheDependency.

     

    The most comprehensive way to refresh query results is to use a new DataContext however you could also look at the dataContext.Refresh method.

     

    [)amien

    Monday, June 9, 2008 10:18 PM
  • Hello. I use SqlCacheDependency with LINQ. But not stored procedures... maybe you can use my extensionmethod anyway?

     

    There is a chance that the example code contains some typeo´s. Wrote it directly in the post.

    The SqlCacheDependency will not work when the LINQquery is to complex. You can do a DataContext.Log to see if your LINQ-queries match the demands for SqlCacheDependency.

     

    My queries run ok, and i´m using MsSql2k5

     

    The Code:

    Code Snippet

    public static class MyExtensions

    {

    public static List<T> LinqCache<T>(this IQueryable<T> q, DataContext dc, string cacheId)

    {

    try

    {

    List<T> objCache = (List<T>)System.web.HttpRuntime.Cache.Get(cacheId);

    if(objCache == null)

    {

    string connStr = dc.Connection.ConnectionString;

    string sqlCmd = dc.GetCommand(q).CommandText;

    using(SqlConnection conn = new SqlConnection(connStr))

    {

    conn.Open();

    using(SqlCommand cmd = new SqlCommand(sqlCmd, conn))

    {

    string Notificationtable = q.ElementType.Name;

    SqlCacheDependencyAdmin.EnableNotifications(connStr);

    if(!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable))

    SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable);

    SqlCacheDependency sqldep = new SqlCacheDependency(cmd);

    dc.refresh(RefreshMode.OverwriteCurrentValues, q);

    cmd.ExecuteNonQuery();

    objCache = q.ToList();

    System.web.HttpRuntime.Cache.Insert(cacheId, objCache, sqldep);

    }

    }

    }

    return objCache;

    }

    catch(Exception ex)

    {

    throw ex;

    }

    }

    }

     

     

    You´ll get intellisense to the extensionmethod on every LinqToSqlQuery

    Example:

    Code Snippet

    var q = (from x in dc where and so on...);

    foreach(List<MyType> o in q.LinqCache((DataContext)myDC, "MyCacheId")

    {

    ...

    }

     

     

     

     

     

    Tuesday, June 10, 2008 10:02 AM
  •  

    Hi, I use this code but when send query with where clause return this error

     

    System.Data.SqlClient.SqlException: Must declare the scalar variable @p
      at InfoMoney.FrameWork.Util.Cache.Cache.GetOrCreateIdea(IQueryable`1 Query, DataContext DataContext, String cacheKey, enumDatBase BaseConnection)

     

    Its possible send clause where in this method?

     

    my query:

    var query = from U in DbUsers.Users

    join Prof in DbUsers.UserProfiles on U.Id equals Prof.UserId

    where Prof.MemberOfId == 1

    select U;

     

    Tanks for help

     

    Wednesday, December 3, 2008 8:14 PM
  • Hi Fbelmonte...

     

    It was a while a go I wrote this solution. I to stumbled over the exception when using where parameters in my LINQ-query... Sooo I wrote this that probably will do the trick for you. link ->  http://code.msdn.microsoft.com/linqtosqlcache

     

    Please tell me if it helped you.

     

    Regards

    UTB

     

    Wednesday, December 3, 2008 8:21 PM
  • Hi Regards...

     

    You helped me a lot ...
    Very very cool your post!!  very good!


    Thanks for Help!

     

    Wednesday, December 3, 2008 8:48 PM
  • I have to cache dbml (linq to sql) stored proc result using SQL Dependency. I tried above MyExtensions class method. But objCache is always null.

    I have called above LinqCache method as below:

    TestContextDataContext

     

     

    dc = new TestContextDataContext();

    List

     

     

    <ShowResult> result = dc.GetData().AsQueryable().LinqCache(dc, "Data");

    where GetData() is dbml stored proc.

     

     

     

     

     

    please assist.

    Saturday, March 5, 2011 10:38 AM