When I've needed to invoke a cache mechanism for data on a web site, I've happily used the ASP.Net Cache object over the past few years. It's a nice model for storing a chunk of data under a key until a specific time or for a given duration, providing the application domain doesn't recycle.

I've even hooked up some systems that resemble a Rube Goldberg apparatus to get the same features as a job scheduler, but I don't like to talk about them. ASP.Net will fire an event that you can use when something expires from the cache and therefore give you the "Cache, Wait, Expire, Do Something, Repeat" model for solving a job scheduling problem.

.Net 2.0 and up supported the SqlCacheDependency class. One of its talents is the ability to expire a cached object when the underlying database changes. Before this feature was available, you might wire up specific cache expiration logic in relevant insert, update or delete functions of your app. However, that falls short when the database is updated outside of the trip wires you set in your own code. Another application might update that database, or someone might edit the database through Query Analyzer.

Way back in the day, my team set out to bag the big beast by writing a trigger in SQL Server that would reach out an edit a text file whenever a specific table was modified. This event would be observed by the cache dependency object that was good at watching physical files for changes and ska-doosh! We've got our SQL Server cache dependency working.

Nowadays, those long, verbose methods aren't required. We've got a set of fancy new tools, namely, the SqlCacheDependency object. Or so I thought.

Its great that support goes all the way back to SQL Server 7, albeit via a costly polling mechanism. SQL Server 2005 gave us a direct feedback loop that avoided the polling system. I've know about this feature, as a concept, since it came out but only in the past few weeks have I really looked at the code to use it and I'm honestly a little shocked at the site.

I was really expecting something akin to the familiar practice of checking the cache for the existence of the object, and if necessary, repopulation of the cache. It turns out there's a few more steps, and a little craziness if you want to use LINQ to access your database.

I'll always leave the door open and hope that some day I'll learn the super easy way of doing this, but today, so much of this is in the non-trivial bucket that I'm really tempted to leave it alone and continue my current caching practices.

A year or more ago, I would reach for the data access block inside the Enterprise Library as a default choice. It's a great data access layer and sure beats writing all that goo on my own. Today, I'll reach for LINQ as my default choice. As I understand it, Microsoft is touting the Entity Framework as the future of data access, but today, I'm still sitting contently in the LINQ boat. Who knows what the future holds for me.

To be fair, the example code in MSDN that shows how to implement the SqlCacheDependency actually looks pretty good when you're creating your own Command objects. Not much to talk about there. But I was disappointed when I looked at the LINQ story. Perhaps LINQ just got out ahead too far from the pack and better things are coming.

One of the cleaner approaches to caching LINQ data I've seen was on the MSDN Code Gallery web site. Here, the developer created a sweet extension method that creates the Command object on the fly, executes the query and caches it all inside a generic method. It bears a little more review on my part, but its neat enough that I thought I'd share it. Its less than 50 lines of actual code, but they comment the crap out of it.

The whole idea of having SQL Server notify ASP.Net involves more considerations than the simplistic approach that served me well for years. For example, I can have SQL Server watch a specific table, a set of tables, or a specific query (this last options requires SQL Server 2005 or better). In addition, you'll need to start and stop the entire process when the application domain comes up and gets recycled respectively. You probably with a few lines of code in Global.asax. Finally, you're going to need a few more elements inside your config file that describe a few more details of the cache behavior. All in all, I like the direction, but I'll be happier when/if the LINQ story improves.

I just had a Pwop moment (the sound of a forehead slap) with LINQ:

I needed to retrieve a distinct list of years contained in a collection of objects. The data was in the form of an generic List<T>, already loaded into memory. So without thinking too much about it, I sat down and wrote a quick LINQ expression that included the Distinct() operator and since the years were actually properties on the objects, I wrote my own implementation of IEqualityComparer.

It had some problems because the resulting data was not really all that distinct, and then it happened: PWOP!

I don't need to implement an interface. I can simply use the default equality comparer for a List<int> like so.

Gah!

LINQ is an acronym for Language-Integrated Query and a new feature in v3.5 of the .Net Framework from Microsoft. This new version of Microsoft .Net reached RTM status a couple of weeks ago — this framework is chock full of brilliant things I can use to improve my efficiency and effectiveness on daily tasks here at Pop Art.

As one of my favorite features, LINQ helps me write data-driven application even faster than what .Net 2.0 brought to the table. A common example starts with launching Visual Studio 2008. After dragging a collection of tables from a SQL Server database onto a surface in my solution, I can see a visualization of the columns in the tables as well as the relationships between them. Click the following thumbnail to see a larger image.

Next, I start writing data access code directly in my C# program as opposed to switching languages and writing in the T-SQL language. Visual Studio gives me Intellisense here too; as I type the name of a table and click the period key, all of the columns in the table appear where the cursor is located. Big time saver. Huge! I'm certain that I didn't misspell a column name and that my code will compile.

The syntax for LINQ in a C# program is very similar to the T-SQL language, which is a "set based" language. LINQ statements are compiled, just like the rest of my C# code. The first thing someone well versed in T-SQL will notice is that the columns normally specified in a SELECT query are at the end of the LINQ statement instead of the start as in T-SQL. The idea is that you're articulating your constraints at the start of the LINQ statement and then pulling out the fields you need at the very end.

Behind the scenes, LINQ is using the relationships expressed in the database to generate T-SQL scripts on the fly. This is a clear line of demarcation for LINQ. If you're using stored procedures exclusively for database access, then LINQ isn't going to buy you much. You'll still get Intellisense inside Visual Studio and you can specify an existing stored procedure instead of using the auto-generated SQL, but you're giving up a lot of acceleration tools. Perhaps more than you're getting in return.

LINQ really shines in multiple table joins and aggregation. The following two blocks of code show a query is performed in LINQ and its equivalent T-SQL script. The query retrieves product information from three tables where the list price is below a given amount and a sub-category name exists. If you go the T-SQL route, you will still need to write some C# code to call your database query.

Which of the following versions would you rather author and support?

LINQ Version

T-SQL Version

MSDN is a great online resource for developers and they really hit a home-run here. They have a page with 101 LINQ samples. This is my preferred way to learn when I already know the surrounding technologies and I want to fill in a specific gap. The page categorizes several ways of retrieving and iterating over information.