SelecTViews
With Stephen Wynkoop
Microsoft BI Expert Jen Underwood talks with Stephen about the ever-changing roles of the DBA. You won’t want to miss this.
[Watch the Show]
LINQ – What is its Place – More Reader Feedback
This will be the last newsletter on this topic for a while. I have more responses from readers than I can possibly include…don’t worry; you comments won’t be lost.
The core question has matured into, “Does it make sense to make LINQ queries work natively in SQL Server, just as you can do with TSQL and CLR Code?” The value would be a syntax better known by developers, that may be used in other work they already do.
The goal is not to replace an ORM any more than TSQL replaces an ORM. There is a lot of other work that may go against your database than what an ORM generally manages. The goal here is to provide syntax capable of being encapsulated into a query, function, view, or stored procedure written in LINQ syntax, for those situations where an ORM is too heavy, and custom code is required.
Following are a few more comments from our readers.
Julie:
I want to help clarify some terms around LINQ in the latest ssqug.org newsletter. You may know all of this already and my goal is not to criticize, just be sure that in your expression of the terms, you don’t confuse readers.
LINQ is a query language to query in memory object from VB or C#. (I clarify this only because you say “LINQ” in the title )
LINQ to SQL is a twist that uses LINQ to query SQL Server and return strongly typed objects..but SQL Server only. (You say “LINQ to SQL simply puts a Dot Net wrapper around the data access layer, providing a common syntax, allowing the ADO infrastructure to manage the database specific variances.” So that raised a red flag for me.) Also, FWIW, LINQ to SQL is something of a “shelved” product from Microsoft. It’s completely supported but won’t get much investment going down the road. MS is pushing folks to use Entity Framework instead.
Entity Framework is an ORM that lets devs work with a variety of databases while using a common query syntax (Something like what you said about LINQ to SQL…”db specific variances”). They can either use the raw expression syntax, Entity SQL, or another twist on LINQ — LINQ to Entities.
Since you have a lot of people reading your newsletter, I wanted to be sure you were not confusing folks with a common misunderstanding.
I hope you don’t mind the uninvited help.
Editor: I always appreciate clarification…It helps everybody…
Erik:
LINQ, which is really a set of C# compiler extensions, lacks support for query hints, which is annoying. Also, the effectiveness of LINQ depends on which underlying provider you use. Microsoft has centered their efforts on Entity Framework, which is complex and not that fast unless you use compiled queries. But compiled queries add more complexity and do not support emitting anonymous types. Another option is to home-brew a query provider. It’s not easy, but you can find C# source code for a pretty good one on Matt Warren’s blog (http://blogs.msdn.com/b/mattwar/). All that said, LINQ provides compiler support for queries, which we have found very helpful.
Maurice:
Linq generates SQL, so you don’t have control on the queries we could otherwise optimize.
Uncareful use of linq ends up in break queries in row by row manipulation. Join disappears in favour of loop on row by row access.
In all cases Linq does single row updates where there is conditions that implies more than one table.
This the best of ORM technologies, but it fall short expectations for SQL optimization
Aaron:
I have a hard time believing any new technology will be able to better handle data retrieval and manipulation in as compact and powerful language as SQL.
You can do almost anything you want with 10 to 20 keywords. SQL is a domain specific language that works wonders, instead of trying to hide developers from it, we should train them how to write effective SQL.
Montgomery:
I mentioned in a previous post that LINQ to Objects (L2O) in modern SOA on the MS stack is a powerful facilitator of aggregation.
It allows aggregating queries of arbitrary complexity across disparate stores, federated DBs from a single vendor and between data from the DB and objects within the SOA cache.
As you can imagine, the last one is incredibly powerful. All this w/o using any of the LINQ to ‘DB’ technologies.
It can be shown that this single technology easily outperforms any alternate for each of the aspects listed above…
Ed:
Developers are already learning Linq instead of SQL. If you code using Visual Studio, then Linq is the natural query language to use because it provide the same FLWOR syntax regardless of the underlying object. If DBAs try to stand up against this tide they will simply be bypassed.
Some people are worried that developers using Linq will mean SQL gets a deluge of dynamic SQL. However, Linq can just as easily use SP calls, so if the site standard is to use SPs to enforce security or for any other valid business reason, then using Linq should not affect any of this.
My main concern about Linq is that it is not a First-Rate language within SQL Server. All Linq queries have to be recoded as T-SQL before they hit SQL Server, and this can and does lead to strange query syntax that is difficult to troubleshoot. If Microsoft really is committed to Linq, then it needs to be given the same status in SQL Server as T-SQL and XQUERY. In other words the optimiser should be able to take a Linq query from a program call or a SSMS query window and generate an execution plan from it.
Editor’s Note:
Thanks everyone…it’s always good to keep terms strait. One clarification of my thoughts is that I was not thinking of replacing an ORM by making LINQ native to SQL Server. You don’t gain anything from that.
LINQ to SQL is used to query the database for a lot of other reasons. Entity Framework, although an extensive library, cannot replace native SQL in certain situations. So, why not provide developers with syntax native to SQL Server that they use elsewhere if it performs as well as TSQL, just like CLR Stored Procedures?
Send your thoughts to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
The Procedural DBA (Part 5)
The following section anticipates some of the barriers to acceptance for a procedural DBA and a possible solution for overcoming the resistance.
Featured White Paper(s)
Query Tuning Strategies for Microsoft SQL Server
Written by Quest Software
When you’re looking for a reliable tool to diagnose … (read more)
Featured Script
2005 Blocking Locks RDL for Management Studio
Custom Report for SSMS. 1) Launch MS VSS or BIDS. Select File -> New -> File 2) Select XML File. 3) Paste the conte… (read more)