Quite a while ago, someone (I forget who) was soliciting opinions on whether or not LINQ and ADO.NET vNext EDM should be able to run "in process", in other words should be useable in SQLCLR procedures. Here's one perspective, and a question.

LINQ and Entity SQL are QUERY languages. Although they might be able to encapsulate resultset output from stored procedures, much of their value is in the query language itself. NOT having them be useable in SQLCLR would encourage folks that want to use these languages to move AWAY from stored procedures and put their data access query code in client or middle-tier programs, rather than in the database. I don't think DBAs and database developers really want to encourage that.

On the other hand, some of the value of stored procedures is being able to restrict table permissions and find errors at compile time. SQLCLR procedures don't have either of those attributes built-in, although you can restrict table permissions if you use EXECUTE AS OWNER. And coding with LINQ/Entity SQL can allow compile-time type metadata checking. Other benefits of stored procedures, such as allowing the code to be shared among database apps, and consolidation of SQL code in database objects, do apply as well to SQLCLR.

And its a given that, with either of these languages (and with SQLCLR in procedures in general) you'll be giving away procedure execution speed for (*arguably* better, meaning you'all can argue about it) code maintainability. Unless you only use LINQ/Entity SQL to process T-SQL stored procedure resultsets.

What do YOU think?

One thought on “LINQ/EDM in SQLCLR?

  1. All that stuff almost made sense to me. 🙂 I really gotta brush up on my DBA techie jargons.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.