c# - Is LinqToSQL powerful enough? Isn't a more powerful but equally fluent interface easy to construct?

Question

In a previous question, I asked whether ORM libraries were suboptimal solutions and received a lot of great feedback. As I've thought about the issue further, I've concluded that the primary benefit of LinqToSQL (now) and the promise of LinqToEntities (down the road) lies in their ability to reduce the "mismatch" between procedural code and SQL. Given the limitations of LinqToSQL as a complete data querying language, I'm not inclined to agree that its advantages go much beyond this but I'd like to know others opinion.

To start, what do I mean by a "mismatch" between procedural code and SQL? If you have ever developed a database application then you have likely become familiar with (and weary of) the steps needed to interact with a database: set up all the parameters, enter the SQL command, and then hand convert the expected results back to variables or instances used in code.

LinqToSql makes this much easier by the use of "fluent" interfaces, lambda expressions, extension methods, etc. The end result is that it is easy to push native C# scalar types into the data retrieval call and easy to automatically generate native C# class instances. Take this example from the MS web site:

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
foreach (var cust in q)
   Console.WriteLine("id = {0}, City = {1}",cust.CustomerID, cust.City);

Very cool!

So what is the problem?

Well, as I pointed out in the article linked to at the top, there are a number of problems. The biggest showstopper for me was that anyone who is minimally competent in SQL will immediately hit roadblocks. It isn't just that many of the constructs created as alternatives to SQL are unfamiliar or even clumsy (Group By? We're talking Ugly in LinqToSql). The bigger problem is that there are a number of common constructs that just aren't natively supported (e.g. DateDiff). At best, you can sort of "escape out" of Linq and submit SQL code into the Linq call stream.

So, isn't it better to simply embed SQL in C# (or VB) in ways that permit you to freely state your SQL but that also give you easy parameter embedding and automatic conversion to native classes? For example, if we implement just six functions in a single class, we can write something like this (where qry is an instance of our query class):

var ListOfEnrollees = 
           qry.Command("Select b.FirstName, b.LastName, b.ID From ClassEnroll a inner join Folder b on (a.ClientID = b.ClientID) ")
             .Where ("a.ClassID", classID)
             .AND()
             .Append("(DateDiff(d, a.ClassDate, @ClassDate) = 0) Order By LastName;")
             .ParamVal(classDate)
             .ReturnList<EnrollListMemberData>();

The Command simply starts us off collecting the SQL statement, the Where starts our SQL Where clause and enters the first parameter, Append tacks on more SQL, ParamVal just enters a parameter value to match the SQL parameter found on the previous line, and ReturnList does the magic needed to convert to a class. Notice that this is just SQL: joins are trivial, DateDiff (or any other SQL construct) is supported, etc. We can test it out in a SQL window and then just cut and paste into our code, breaking it up as appropriate to enter our parameters. It couldn't be easier.

Now, the basic construct shown above seems outrageously simple to me and it can handle ANY SQL construct using the knowledge of SQL I already have. I did have to use some reflection and some of the other cool new C# 3 constructs to make the ReturnList (and similar) calls work but, overall, it was pretty straightforward. I also added a number of bells and whistles to make the interface more fluent. So, here is my question and where I'd love to hear comments from the community:

Why would I ever need to master the intricacies of LinqToEntities or even incur the overhead of LinqToSql? Doesn't this pretty much give me everything that LinqToSql gives me and more? Doesn't it cover even LinqToEntities with the exception of exotic Relational to Object mappings?

Update: Hamish Smith argues below that LINQ may someday be a fully capable data manipulation language so powerful that SQL isn't even needed. This is an important tipping-point argument that I didn't discuss but with which I agree. The essence of my position is that, while there are some practical advantages to LinqToSql, it still falls far short of Hamish's goal. This is a very real and significant drawback.

Hamish also argues, correctly, that I'm still working with embedded SQL - I haven't "solved" the problem. To me, however, this is a feature and not a bug. SQL is still so much better at selecting and manipulating relational data (which is, after all, what we're working with) that I want to be able to use it to craft my solution. I'm arguing that embedded SQL isn't the problem so much as the impedance mismatch between it and C#. However, by using the new, Linq-inspired features of C# 3, I can largely eliminate this "impedance mismatch" and get the best of both worlds.

Answer 1

So, isn't it better to simply embed SQL in C# (or VB) in ways that permit you to freely state your SQL but that also give you easy parameter embedding and automatic conversion to native classes?

What I usually do comes very close to this. I write the SQL in a stored procedure, so I get the freedom of real SQL. In C# I use Linq's DBML to access the stored procedures in a natural way. This feels like the best of both worlds.

The real solution looks even simpler to me: embed SQL in C# code and have the compiler generate the strong classes. If Visual Studio supported that, I doubt anyone would be using anything else.

(There' always people trying to create Entity Frameworks and "architecture" away the need for SQL. As far as I know, this has never really worked, if only because the result is dramatically harder to maintain and dead slow.)