I presented tonight (10 July 2008) to the Richmond .NET User Group. We had a pretty good turnout, I'm guessing 40 to 45 developers. I gave this same presentation at my office today as a dry run and as a training opportunity within the company. It's so good to see the developer community eager to learn. I've attached my slides and the three demonstrations projects I used in this post. I'll be giving this same presentation to the Charlottesville .NET User Group next Thursday (17 July 2008). The abstract we put on both user group websites follows:

Silverlight is a client-side technology. So it’s not really a part of your SOA strategy, right? You may want to think twice about that. SOAP and WSDL support are coming to the web desktop via Silverlight. And Silverlight has good client support for REST+ JSON/POX and RSS/ATOM-based web services, too. During this discussion, we’ll dive into data serialization, security and cross-domain access policy capabilities inside Silverlight 2 Beta 2. We also talk about the nuances and pitfalls of provisioning your web services for an Internet audience. This presentation will be heavy on coding, demonstration and interactive discussion.

Powerpoint Presentation (289KB)

Twitter solution showing how to invoke a cross-domain RESTful service by way of an in-domain SOAP service bypassing the cross-domain access policy problem. (842KB)

REST solution showing how to create RESTful services in WCF and how to consume RESTful services in Silverlight (307KB)

Silverlight syndication solution showing how to consume cross-domain RSS and Atom feeds using the SyndicationFeed class. (11KB)

UPDATE: I've included a videocast with this blog post. Let me know what you think. 

A few days ago, my buddy Justin Etheredge wrote a blog post about Efficient Paging in SQL Server. I was thinking about how transparent Language Integrated Query (LINQ) makes paging and I thought I'd blog about it. Two of the more interesting extension methods offered by LINQ are Skip() and Take(). You can use these extension methods to skip rows at the beginning of the query result and take only those you want to return. Sounds like paging to me. I wonder if Skip() and Take() used in combination with LINQ to SQL behave as efficiently as Justin's example? Let's take a look. Consider the following LINQ query:

This small example uses the AdventureWorks SalesOrderHeaders as the input sequence and shapes the output sequence to include the associated Contact's name parts and the total value of each order. The total value is computed as the OrderQty times the UnitPrice for each associated item in the SalesOrderDetails table. There is a filter placed on the query to restrict the results to orders placed in the 'Northeast' territory. This simple query shows how easy it is to filter, perform arithmetic and use the relationships in a LINQ to SQL data context to traverse table relationships. What does this query look like when it's compiled for execution on SQL Server?

You can see the territory filter applied as a WHERE clause. Note that even when a string literal is used in the C# code, LINQ to SQL still passes filtering variables as parameters. In this case, the territory name 'Northeast' is passed as a variable named @p0. This is always a good practice because it helps to thwart the injection of potentially malicious T-SQL into your query. We can see another interesting feature of LINQ to SQL in the T-SQL that is created called projection. Because the C# code shown above shapes the output sequence to only a few required columns, the LINQ to SQL engine is smart enough to T-SQL shape the query to return only what's needed. Projection often improves query performance and always improves transportation speed on the wire.

Finally, notice that the third column projected into the output sequence, i.e. the sum of each order's value, is instatiated as a two-part, nested sub-SELECT operation in the T-SQL statement. The inner SELECT does the math on the order quantity and price. The containing SELECT aggregates the line item totals and them filters them to the rows selected by the outer query. Nicely done, LINQ! Now, we see that this is a long list, returning thousands of rows. If this query is meant for human consumption, we should break it into smaller chunks to make it easier to handle. How do we do that in LINQ? Add this to the C# code shown before.

This modification uses the Skip() and Take() extension methods to skip 40 rows and take the next 20 rows. In other words, at 20 results per page, this query now returns the 3^rd page. Though the magic of deferred execution, we can add the Skip() and Take() extentions at any time before we begin iterating over the result set. This comes in handy when you want to enable paging for human consumption but to disable it for B2B or ETL scenarios. Is the paged T-SQL query shown here efficient though? You tell me. Here the T-SQL that is produced:

If you read the query from the inside out, you'll see that on the inside it's essentially the same query that we saw before we added the paging feature. It has all the original SELECTs named t1 through t4 and is wrapped as a new result called t5. The SQL Server ROW_NUMBER() function is used to inject a row number into t5 ordered by all 3 projected columns. That looks a lot like the query Justin showed us in his blog post. Very efficient! The new result containing the row numbers is named t6.

Finally, the t6 result is filtered by a starting row number and ending row number using two new variables @p1 and @p2. For page 3 paged in 20 row chunks as shown above, these variables would have the values 40 and 59, respectively. LINQ to SQL injects these starting and ending row number parameters whenever you use Skip() and Take() together. Well, it almost always does that. If you happen to specify Skip(0), it reverts to the behavior that Take() uses without Skip() which is to use SQL Server's TOP() function instead. LINQ to SQL sure knows how to sweet talk SQL Server, don't you think?

Databases are just awful. I don't mean the products themselves but the concept of databases. Stop and think about how absurdly we behave when we write modern software. We generate scads of information in the course of our daily lives. (A scad for me is about 2Gb the last time I checked. May be more or less for you.) Much of this information approximates the lives we lead and the obligations we must honor. But rather than putting that information into a system that has the tools necessary to model the real world from which the data originally emanated, we usually choose to keep it in a place that does an efficient job of storage. When we need to put it back into the real life approximation engine, we shuttle the information in and out of our application servers as necessary. It's been estimated that as much as 50% of the time we spend in development is in bridging the gap between data storage and the business logic of our applications. That number may be an extreme, low or high. But even if this kind of work accounts for only 25% of our time, why would we choose to spend our development budget this way? Data is so simple. It should just be there, fully accessible to me all the time.

Some operating systems do a better job of closing the gap between code and data than others do. For example, the Pick System, originally developed by Dick Pick in the late 1960s uses a hash-based file system to create associative arrays that are super-efficient for many query operations. The only data type in the Pick System is the string. And most importantly, the Pick database engine is not relational. It is a multi-valued instead, meaning that any attribute that needs to have multiple values can just declare them. In the Pick mind, there's never a need to create related tables and join them for query or reporting. A platform that implements this type of database also typically ships with a Pick BASIC compiler which allows for direct manipulation of the query engine and the associative arrays it produces. The BASIC code runs right there in the database, not on a foreign system. Embedded Pick BASIC is not like the SQL CLR. The SQL CLR, for lack of a better term, is bolted onto the side of SQL Server. You can't do any real data manipulation in the SQL CLR. However, in Pick BASIC, you can freely manipulate schema and data directly. Forget for a moment that it's BASIC and you've got something great there. Compiled code running in the database that can manipulate database objects natively. Way cool and circa 1965.

IBM and InterSystems, among other vendors, still sell these databases like hotcakes today because they solve very real business problems for which relational databases are not ideally suited. First of all, they're fast. And I mean smokin' fast for many types of operations, especially high-volume transaction processing applications. This is partially due to the fact that because there are no join operations (in the classical sense), there's usually less work to do to obtain the data you're seeking. But even when there is a sub-select operation that is required to get what you're looking for, the efficiency of the underlying hash-based file system pays off handily. In database terms everything in the database is indexed, always.

My students and colleagues often hear me say that, "Databases are an unfortunate consequence of history." I say this (and believe it) because if you could travel back in time to 1948 and give the ENIAC developers at the University of Pittsburgh a handful of 4Gb DIMM chips and the necessary plans to connect them to their invention, relational databases like Oracle and Microsoft SQL Server would simply never have evolved. I think that the development path would have been more like what Dick Pick envisioned and built instead. Given enough memory early in computing history, associative arrays, set operations and in-memory manipulation of large data sets would have been the norm. However, as we know, memory was severely constrained in the early days of computing. In fact, it's only been in the last few years as new technology has allowed for memory prices to drop dramatically that it has been feasible to conceive of a solid-state database at all. Oracle's TimeTen and Microsoft's Project Code Name Velocity are leading-edge concepts in a new market-segment that will, one day, fully realize Dick Pick's vision, in my opinion. I predict that accessing data from distributed, in-memory databases will become the norm within my lifetime.

Many of the current Object/Relational Mapping (O/RM) debates are centered around my database evolution postulate because O/RM tools attempt the inverse of what the Pick OS does to achieve the same effect. O/RM tools essentially pull as many database semantics (sans execution) into the application tier as possible where the logic of the program is codified. Whether we run Pick BASIC in the database or use an O/RM to marshal data close to our C# code, the desired outcome is the same. But pulling data into an external execution engine as O/RM tools do is pretty close to nightmarish, to be frank. In fact, Ted Neward, whom I greatly respect, calls O/RM the Vietnam of Computer Science today, meaning a quagmire from which one cannot possibly be extricated and for which there is no good outcome. Ouch! What a stinging rebuke from a guy who's singularly qualified to make an assessment in this space. Even Ayende Rahien's blog post from earlier today reveals a sense of desparation about the state of O/RM technology. What a mess we've gotten ourselves into! No O/RM suite that I know of addresses the real problem at hand, i.e. making data access so transparent that you don't even know you're doing it.

We use both NHibernate and Language Integrated Query (LINQ) to SQL at SnagAJob.com for O/RM. They make life easier in some ways but so much more difficult in others. I cannot begin to count up the hours we've spent tuning the session management code in NHibernate to deal with authentication and transaction management issues. And you don't burn up welterweight programmer resources on that kind of work. Your heavy hitters need to be deeply involved because there are architectural design issues at every turn. Every minute that your senior developers and architects are distracted with this kind of stuff, they aren't focusing on what you thought you hired them for. LINQ is better than HNibernate in a couple of ways, chiefly because of the expressiveness afforeded by the IEnumerable<T> extension methods and the query comprehension syntax. But deploying LINQ to SQL or LINQ to Entities in a real-world environment is still not as simple as it should be. And the real goal of transparent data access is still far, far way using NHibernate or LINQ.

If you know of an O/RM suite that makes accessing SQL data more Pick-like as I've described, i.e. more transparent, I'd like to hear about it.

<Interesting Related Story> In 1993 while working for Datastorm Technologies, Inc., I attended Comdex in Las Vegas. At lunch one day, two fellows joined me at the table. The older fellow to my right introduced himself as Dick Pick. I asked him what he did for a living and he graciously and eagerly explained the Pick OS, it's simple power and beauty and a smallish version of his life story. I was impressed but didn't really get it at the time partly because the fellow seated across the table introduced himself as Phil Katz, the inventor of the PKZip file compression utility. For me, Phil Katz's fame overshadowed Dick Pick's because I didn't know any better. So, I didn't engage with Dick in conversation to the degree that I really should have. History, it seems, hasn't been all that respectful to Dick Pick either. Phil Katz has a detailed Wikipedia article about him yet Dick Pick doesn't, for example. Googling for Dick Pick yields scads (there's that word again) of Dick's Pick's Grateful Dead references and nearly nil related to the computer science genius of our time. In retrospect, even being seated with a legend like Dick Pick was a real honor. I wish I had known to take advantage of the opportunity that was given to me. Live and learn. </Interesting Related Story>

I've just been experimenting with the new DateTimeOffset type in .NET 3.5. It's about time. I've been evangelizing for the use of UTC for storage of times since it was called GMT. And it seems that SQL 2008 has a new DATETIMEOFFSET type, too. Now, instead of storing UTC offset as a separate attribute in every table that contains a DATETIME value, I can store the original date and it's original offset at the time of storage in one column. That will be nice. It remains to be seen how this will affect my T-SQL practices and the various ORM technologies I support. It seems that LINQ to SQL will be getting support for DateTimeOffset and related classes, too. I am somewhat disappointed that the new DATE and TIME types from SQL 2008 aren't available natively in .NET. There are good arguments for being able to store date and time references separately. Anyway, one step at a time, right?