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.

score 2 · Accepted Answer · answered Mar 13 '09 at 09:35

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.)

+1 Sounds like a good strategy. I do have code generators written in T-SQL that let me take any table or stored proc and create matching functions (for procs) and classes (for tables). An automagic class generator is part of Linq but it doesn't cover complex queries, just tables. — Mark Brittingham, Mar 13 '09 at 14:35

score 1 · Answer 2 · answered Mar 12 '09 at 19:09

1

I think it is overkill, both LinqToSQL and LinqToEntities. I took a minimalistic approach. I developed a DAL that uses a DTO and reflects on its properties to generate SQL on the fly. It works for 95% of the cases where I don't need and dont' want to think about SQL. For all the other cases my DAL allows me to write any SQL I want and returns either a DataSet or if given a DTO it will populate it with the results. It also allows to call stored procedures with the same concept, returning DataSet or DTO's. It works pretty well for me and I don't need to see any SQL if I don't want to.

answered Mar 12 '09 at 19:09

Otávio Décio

73,752
17
161
228

If I could award two answers as "the" answer - this would be marked as such as well. – Mark Brittingham Mar 16 '09 at 14:51
Not a problem, Mark. I just wanted to point out that sometimes simple and well targeted solutions are sometimes preferable to more ambitious ones. – Otávio Décio Mar 16 '09 at 15:52

score 1 · Answer 3 · answered Mar 12 '09 at 19:17

1

linq2sql has built in paging support and is strongly typed.

You can avoid using the entity tracking if you won't use it.

You can call the sql functions, by wrapping it into a method.

If you use entity tracking, you can easily batch several data manipulation calls (does so automatically, thus the SubmitChanges call). So you don't hit the database 15 times to do 5 inserts, and 10 updates.

It also comes handy when you want to get structured information, like:

var someInfo = from s in Context.Somethings
               select new SomeInfo
               {
                   AField = s.SomeColumn,
                   RelatedStuff = s.RelatedInfo.Where(someconditionhere)
               };

I would say it is more than worth it. Also note if you are really needing to get the most performance, you can compile expressions, and it will match datareader speed.

answered Mar 12 '09 at 19:17

eglasius

35,831
5
65
110

Freddy - thanks for responding. Note that the library I outline above is strongly typed as well - providing strongly typed parameters and output is the main benefit. Paging support is pretty easy although I do this in ObjectDataSource instances not in the DAL. -Continued- – Mark Brittingham Mar 12 '09 at 19:31
Doesn't wrapping the SQL functions kind of set off a red flag? If you are having to work around normal LinqToSql to do normal things, doesn't that give you pause? Also, note that you can batch calls to the database with normal SQL commands (and thus this DAL) and that this too uses a datareader. – Mark Brittingham Mar 12 '09 at 19:39
With the strongly typed bit, notice your has a not strongly type list for the select, also for the comparisson i.e. "a.ClassID" can be misspelled"a.ClasssID" and it won't catch it. I wouldn't call paging/sorting easy, I am sure you have seen other's peoplecode doing itin awfull, sometimes witherrors – eglasius Mar 12 '09 at 19:49
On the wrapping - actually no red flag, as the whole point is for All the query to be strongly typed, I would rather have a class that already exposes these existing functions :) – eglasius Mar 12 '09 at 19:52
If my memory serves me correctly, you lose the strong typing when you have to wrap normal SQL. Since LinqToSql doesn't have a method to cover it, then you fall back into having to know the type to match the appropriate argument. – Mark Brittingham Mar 12 '09 at 20:05
RE: the Select list- I see what you mean: I pass typed arguments in but won't get an error at compile time if my arg doesn't match the type expected by the input parameter. Of course, to get this in LinqToSql you get other errors: non-compile time errors when your generated classes are out of date. – Mark Brittingham Mar 12 '09 at 20:09
@Mark agreed, if your model isn't up to date with the sql, you will still get the errors. On the wrapping the function, y, it serves pretty much the same function as the model/generated stuff, but you get to do it once, from the linq you call it like Context.DateDiff(date1,date2) ... all typed – eglasius Mar 12 '09 at 20:45

score 1 · Answer 4 · answered Mar 13 '09 at 00:47

1

What you are proposing with your query class is that you still embed SQL directly in the application code. What you get with the LINQ + ORM solution is that the application code never contains SQL. You still have a mismatch and a distraction from working on the code. You mention it in your post:

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.

What the LINQ syntax is trying to give us is queries as first class citizens in the code. Yep some stuff is clumsy, some stuff might not be quite right yet, but the stated goal of LINQ is to integrate query and application code to the point where there isn't a disjoint. It sounds like that isn't what you are after, so maybe embedding SQL as string literals that are opaque to the compiler and runtime will work better for you. We know it can be made to work, we've done it for years.
The other benefit is that the LINQ statement will not be subject to vendor specific variations (that's the LINQ provider's problem). You could end up with the SQL string that you pass to qry.Command() being tied to a particular database.

answered Mar 13 '09 at 00:47

Hamish Smith

8,153
1
34
48

+1 - Interesting comment. I agree in a way and, in my full-blown library, I have replacements for a variety of SQL keywords (Where, And, Or, etc.) to make it more "native." However, I'm sure you're aware that the overall goal you state has a drawback: since SQL *is* the native language -continued- – Mark Brittingham Mar 13 '09 at 01:16
Absolutely. For the current developers that have a deep understanding of SQL there are limitations and yeah SQL is the native language and is really well suited to set based processing. It could be possible for some future developers to not need to know SQL though if integrated options are improved? – Hamish Smith Mar 13 '09 at 01:20
of the database, then any attempt to replace it with native programming constructs runs the risk of embedding the mismatch in ways that are more frustrating than the occasional argument type problem. – Mark Brittingham Mar 13 '09 at 01:43
Hamish - great comment followup. I'm modifying the request to reflect my take on what you've said. – Mark Brittingham Mar 13 '09 at 01:46

score 1 · Answer 5 · answered Mar 26 '09 at 11:30

1

var q = DataContext.ExecuteQuery<Etc>("SELECT * FROM etc") usually does a pretty good job.

answered Mar 26 '09 at 11:30

Good observation. I've already walked down the path of a pretty complete DAL that uses an interface rooted in the above but, really, this is the kind of observation I was looking for. – Mark Brittingham Mar 26 '09 at 11:52

score 0 · Answer 6 · edited May 23 '17 at 12:13

At the moment, I'm not a fan of Entity Framework, but 4.0 looks much improved. I'm not sure is it as big a change as Vista vs Windows 7, but definitely better.

For now, I find LINQ-to-SQL covers most things I regularly need. Heck, even if I just use it to connect stored procedures without having to write the parameter code, that is to my advantage. I find the CRUD stuff is great for simple orthogonal data manipulation. For more complex scenarios, I like UDFs etc - which LINQ-to-SQL supports fine.

As long as you hide it in a repository, you aren't even painting yourself into a corner.

Re datediff - have you looked at SqlMethods?

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

6 Answers6

Linked