Just observing the obvious here, but about three and a half or so years ago when I heard about C-Omega, which later became LINQ, which then later became a part of C# 3.0, I got the impression that LINQ would perform SQL querying on the fly with integrated SQL-like syntax directly inline with C#, with strong typing and everything. In other words, I thought the language would inherently know ADO.NET/SQL semantics.

It does, but well, no, it doesn't. LINQ isn't an inline SQL translator at all. It is only syntactical sugar for a set of interfaces to a neutral provider to different sources of data. Once the provider is set, LINQ is still not translating SQL inline.

LINQ-to-SQL, LINQ-to-Entities, LINQ-to-NHibernate, LINQ-to-YoMamasDB, all of these use ORM templating and database boilerplating with class-specific generated code to match up the generated SQL.

I'm not against ORM, but it's still too much for smaller (read: tiny) quick-and-dirty hacks. In these cases LINQ (-to-anything) would be overkill galore in the database context. I do say this as a complaint: I still have to use plain old ADO.NET for quick-and-dirty SQL invocation additions, there's no way around it without making it not-so-quick-and-dirty.

Meanwhile, LINQ-to-Objects and LINQ-to-XML are legit. No boilerplating / generated code there. Very sweet.

This is really old news, but something I didn't realize until last night at a Silverlight users group meeting.

Silverlight 2.0 brought us LINQ-to-Objects and LINQ-to-XML on the client. Bravo, yay, etc., but obviously LINQ-to-SQL was out of the question because you can't make an Internet-based T-SQL connection (i.e. over port 1433), for obvious reasons (*cough* security).

But Service Pack 1 for Visual Studio 2008, which introduced ADO.NET Data Services, also brought in along with it transparent LINQ-to-SQL support over WCF. This to me is bizzare. I haven't tried it, I only heard it mentioned. Frankly I'm worried about security, still, as it sounds a lot like RDO (Remote Data Objects) from back in the ASP Classic / ActiveX days, and which turned out to be a huge security disaster. Nonetheless, I'm still curious about how this might work securely and how it might make the workflow of a modern-age developer much, MUCH more pleasant than manually wiring up WCF for data synchronization to begin with.

I'm casually skimming an ASP.NET book for review purposes and I came across mention of the connection factory classes in ADO.NET 2.0.

I forgot about these; I've always seen abstract, app-specific DAL base classes that get implemented with a SQL, Access, or other database-based implementation, but I've never seen anyone use DbProviderFactories.

The book claims that these factory classes provide database neutrality in instantiating a database connection, so that you can use SqlConnection but also OdbcConnection, et al, without changing or recompiling any of the codebase, "without affecting the application's performance!"

No performance hit? Is it not using reflection? I fired up Reflector to introspect these classes, namely System.Data.Common.DbProviderFactories, System.Data.Common.DbConnection, System.Data.Common.DbCommand, and System.Data.Common.DbDataReader. Reflection is used. It's fine, relflection is there for a reason, but when used in any loop it is also notoriously slow (at least 10x the invocation time of a strongly referenced invocation). I suppose if the application has a very lightweight load, it might not matter.

I wrote and ran a performance comparison test in a console app. First I just ran two near-identical methods seperately, each in a loop (1000x), one method using DbProviderFactories and one just using SqlConnection, and both using SELECT to return all rows in a single-row, 4-column table. Then I realized it would be good to measure the performance of the last run of each, because the first few runs and especially the very first run will be expectedly slower due to runtime caching and JITing.

Here's the end result:

Factory:        23739 ticks / 2ms (total @ 1000x: 2331ms)
SqlClient:      11233 ticks / 1ms (total @ 1000x: 1321ms)

Now the question becomes, does 1 millisecond difference per connection instance matter, considering how high that number's gonna go when it goes over the wire and both data load and business logic is going to increase things to anywhere from 10ms to 1000ms?

Perhaps not. There is a difference, but it is subtle. The debate is kind of like the debate about "" versus String.Empty.