0

I use LINQ queries in my ASP.NET MVC application and want to use OutputCache in some of my Actions.

I hear this should be possible with CommandNotifications. But those seem to only go for self-created SQLCommands, or am I wrong?

Can I manually tell SQL server to send SQLDependency notifications if certain tables change? And if yes, how can I attach them to the OutputCache?

Another side question: Can you do this with strongly types views too?

Thank you in advance...

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

10 Comments

> 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...
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?
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.
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 msdn.microsoft.com/en-us/library/…. Just pretend it's somewhere other than a Page_Load.
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?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.