Linq, SQL, CAML

So I went to a presentation about Linq yesterday. The presentation was given by Scott Allen. Before I get into my impressions of the content, let me say a few things about the packaging. You know how they say first impressions count? Well Scott Allen either didn't know that or he just plain ignored it. I wasn't impressed with the dude's appearance. But, before you crush me with accusations of superficiality, I have to say 5 minutes into the talk I was already impressed with his content. His tone was just right and the approach he took to explaining Linq (and even the use of "var" which I, hitherto, thoroughly despised) was excellent.

That said, I am sold on Linq-to-everything but sql. Some of the language enhancements that were made to support Linq are things I have always wanted to do. For example, you have a list with several items and you want to find items that match a criteria. How do you do it? You write a loop that has a lot of what Scott called "ceremony" i.e. fluff:

List matches = new List();

foreach (MyType x in myTypes)

{

if (MatchesCriteria(x))

{

matches.Add(x);

}

}

With C# 3.0, you just write

var matches = from t in myTypes

where t.StartsWith("svchost")

select t;

Imagine being able to query an xml document, a web service and a collection of objects with the same syntax.

So we are all in agreement that Linq is great for querying objects. However, where it breaks down for me is when you start using Linq to query modern day databases. I have a few problems with LinqToSQL

• Microsoft's implementation of LinqToSQL (and I suspect all other implementations) uses queries generated on the fly. Sure SQL Server will optimize and cache queries for you but how do test the generated queries? How do you know the queries being generated by Microsoft's LinqtoSQL provider generates sound queries when it has no prior knowledge of your database?
• When you use LinqToSQL, you give up all the power of SQL for the simplicity of Linq's syntax. For trivial applications that might be a good trade off (after all who wants to bother with stored procedures, views and functions in a trivial app). But for non-trivial apps, I think you need the flexibility and power of SQL with a C#
• Ignoring the efficiency issue, when you start mixing queries with C# code, isn't that just a recipe for maintenance nightmare? Imagine how much harder it'll be when renaming a table; adding new columns, dropping existing columns, modifying existings columns...if you've got C# code directly accessing those objects.
• What's so wrong with plain old SQL? Attempts to replace SQL with a 4th generation language-based query language will almost always fail. Why? For one 4th generation languages are geared towards you (the developer) coming up with the algorithm to retrieve data. Whereas, SQL is all about tell the RDBMS what to get and it figures out the best way to get it. When you start writing queries in C#, C++, Java, XML...well to me it looks like you are trying to do too much. And you just might end up with CAML, which is a truly awful query language native to SharePoint.

I get why Microsoft might have felt the need to introduce LinqToSQL for completeness sake. After all, you can't talk query with talking SQL. But I fail to see how one could use LinqToSQL in non-trivial applications.