ansaurus

Question

ASP.NET MVC <OutputCache> SqlDependency (CommandNotification?) with LINQ queries

Answer 1

A:

But those seem to only go for self-created SQLCommands, or am I wrong?

I would say they're only useful for "self-created" commands, because those are the only ones you can know about ahead of time. If you wanted to create a notification for a particular LINQ query, well, then you'd just call the method in code along with the query.

Can I manually tell SQL server to send SQLDependency notifications if certain tables change?

Sure. Do a notification on:

SELECT * FROM TABLENAME;

And if yes, how can I attach them to the OutputCache?

Make a command, create a cache dependency, and reference it when you add to cache. Initialize dependency notifications in global.asax.cs as usual.

Can you do this with strongly types views too?

Wouldn't make sense. In MVC, you shouldn't be doing data access in the view.

Craig Stuntz 2010-05-04 14:53:44

> You'd just call the method in code along with the query... Which method? `SqlDependency.Start(connectionString)` maybe?> Make a command New SqlCommand with SELECT * from TABLENAME? > create a cache dependency, and reference it when you add to cache... I didn't understand this.. sorry...> Initialize dependency notifications in global.asax.cs as usual.I read this somewhere... I got a code example from there... With a custom OnChange event. What does this do though? And how does it help with the OutputCache attribute? I am completely confused with this topic...

sinni800 2010-05-04 16:04:56

My first point: You don't need notifications for custom queries. You can just invoke whatever you want to do right there instead of waiting for a notification. Notifications are for when you want to know when *someone else* changed the data. Yes, `Start`. As for how to add a cache dependency, which part of the MSDN docs on this aren't clear to you?

Craig Stuntz 2010-05-04 16:24:04

I feel like there are so many ways to do this, and I don't know which way is the right one for me. I don't need notifications for custom queries? What then? I have a website with content which may be frequently changed by everyone. Something like a forum. So I think "someone else" applies here. Also I don't know where to search in the MSDN docs.

sinni800 2010-05-04 16:33:52

If the only app which will ever change the database is *your* app, then you don't need query notifications. You need (1) CQRS as an overall application pattern and (2) the Repository pattern for notifications when you *really* need them. **Cache invalidation is hard; there is no one class which will solve these problems for you.** As far as actually *using* a `SqlCacheDependency` goes, there's an example in, well, the overview page for `SqlCacheDependency` http://msdn.microsoft.com/en-us/library/system.web.caching.sqlcachedependency.aspx. Just pretend it's somewhere other than a `Page_Load`.

Craig Stuntz 2010-05-04 16:54:47

Thanks to you I am getting closer to actually understanding what is going on. So I need a command for the SQLDependency... I could replace those critical LINQ commands with real SQL commands and make SQLDependencies for those. Am I right with that? Or I could use LinqDataContext.GetCommand to get the TSQL command which corresponds to the LINQ I executed... When i create those SQLDependency Objects with the commands and use SQLDependency.Start, will it trigger the CommandNotification as in <OutputCache SqlDependency:="CommandNotification")> (my Action Attribute, omitted parameters) then?

sinni800 2010-05-04 18:04:10

No, it would not be correct to replace LINQ with SQL. Please take this the right way: You really need to spend some time learning about how query notifications work *inside SQL Server* before you try to implement them in your app. The devil is in the details here. Nobody can explain this in one Stack Overflow answer. It's a complicated subject and is not a magic bullet for fixing performance problems.

Craig Stuntz 2010-05-04 19:42:53

So far I know there are Service Brokers which call back to the ASP.NET application. The ASP.NET application seems to put those callbacks in a cookie which you can retreive by remoting. I thought this was implementable as easy as it is without using LINQ.

sinni800 2010-05-04 21:25:57

If you believe that SQL cache dependencies are *ever* "easy", then you have not fully understood the problem. *Correct* cache policy is always hard. SQL dependencies add a layer of complexity on top of that. Don't let the fact that it's easy to slap a directive onto an ASPX page fool you into thinking that caching is easy!

Craig Stuntz 2010-05-04 21:35:48

Oof. My application wouldn't even be that big though... But I'll try just reading into it... I thought the Framework automatically figures out where you do SQL and, depending on that, sets up mechanisms with SQL Server. In my case there would have been only ONE query (Select * from Table) which would have been needed to cache, so I thought it would be easy to implement. Just invalidate the HTML cache when SQL Server backfires that the table has changed. Nothing else.

sinni800 2010-05-07 22:55:51

What you're asking for is not as simple as you think. Beware of premature optimization. Build your app, get it to work, and then think about caching when you've profiled it and found the slowest parts.

Craig Stuntz 2010-05-10 14:32:21

Answer 2

+1 A:

You can give a shot to LinqToCache project. It does exactly what you ask for, hook a SqlDependency on any LINQ query, as long as the query sent to the SQL Server conforms to the Query Notification restrictions. For Linq-to-SQL this mainly consist of specifying the full two part name for tables in the model designer (ie. dbo.Table not only Table). For Linq-to-EF unfortunately the way EF chooses to format the query is incompatible with QN restrictions.

Remus Rusanu 2010-08-02 03:35:59

This is kinda cool, sorry to see it SO LATE!

sinni800 2010-09-22 11:20:34

ansaurus

tags:

views:

answers:

ASP.NET MVC <OutputCache> SqlDependency (CommandNotification?) with LINQ queries

related questions