Let me start off by saying that I'm a huge fan of Language Integrated Query (LINQ). If you've ever had to write code to loop through objects to locate properties and then filter the collection based upon specific values then you'll definitely have an appreciation for what LINQ has to offer. It's a phenomenal technology that will definitely make developing applications a more productive process. Scott Guthrie has some really nice tutorials that help jumpstart learning more about LINQ and LINQ to SQL that you can view here. For those that haven't seen many LINQ samples, here's a simple example of using LINQ to filter a collection of Customer types based upon a Country property:
List<Customer> custs = GetCustomers();
var filteredCusts = for c in custs
where c.Country == "USA"
select new {CustomerID = c.CustomerID, Name = c.ContactName,
Country = c.Country};
In addition to querying objects, LINQ can also be combined with other technologies such as SQL and ADO.NET (XML support is also included) to query databases and automatically fill collections without manually writting O/R mapping code. This is a great feature and a big time saver. However, it makes it really easy for developers to embed SQL logic directly into application classes without necessarily realizing it. This leads to the LINQ dilemma....
Inline LINQ/SQL Versus Stored Procedures
If you've been developing for very long then you've certainly heard the old recommendation of "use stored procedures when calling a database". There's a lot of wisdom to this recommendation since it promotes more fine-grained security, better re-use, and simpler maintenance as compared to embedding SQL directly in code. It also lends itself well to schema modifications since changes can quickly be made to tables, views, triggers and stored procedures directly within a database without even touching any application code potentially (depending upon the changes of course).
With the LINQ to SQL designer in Visual Studio 2008 you can use stored procedures (Scott demonstrates this in a few of the tutorials mentioned earlier), but many of the demos that you'll find out there covering LINQ to SQL perform LINQ queries that generate dynamic SQL directly within .NET classes. Is that bad? It depends on who you talk to. Experienced developers will weigh their options here, but those who are newer to the world of LINQ to SQL may not consider the ramifications of embedding LINQ code to query databases directly in their application classes.
I personally think that embedding LINQ/SQL logic directly into classes will lead to maintenance nightmares down the road especially in large applications since any changes to a database now require not only updating the database tables, views, triggers, etc., but also locating the related C# or VB.NET code that also needs to be changed (similar to what happens with embedded SQL code now). In enterprise situations there may be multiple applications that need to be modified.
Security is also a potential issue since instead of being able to lock-down specific stored procedures to defined users and roles, more generic queries against source tables are made (views can certainly be used as well though). In talking with several DBAs about this at the DevConnections conference in Las Vegas, they're certainly not supportive of moving query code out of the database for security and maintenance reasons. Some of them just didn't want to give up that type of control, but the majority just thought it was a bad idea from a security and maintenance standpoint.
I think that adding LINQ to SQL code directly into application classes is extremely productive. It's pretty amazing how quickly and easily you can access data. However, I'm still on the stored procedures bandwagon when using LINQ to SQL because I'm sold on the security, re-use and maintenance story that goes along with them. However, in talking with several other developers (some who are very experienced) they don't necessarily agree with this view and will be putting their LINQ/SQL queries directly within code since it's simply more productive. If you have an opinion here I'd be interested in hearing it since I think this "LINQ dilemma" will certainly stir up a lot of discussions as .NET 3.5 is released.
Translate: