Facts or prejudice regarding LINQ and Entity Frameword?
My initial reaction to LINQ to SQL and Entity Framework was "I don't trust them!". Here's why (based on, I think, more prejudice than than facts): - With LINQ and EF, one will have to use either a proxy login for the system built with LINQ/EF, or give every login read/write permission on every table used by the application. None of the solutions are any good, for security reasons. Distributing a proxy login with password in a config file isn't secure (with or without config-file encryption) and it won't give logging on real-person-level. Windows login is more secure. Giving every login that will use the app full permissions on all tables is bad for obvious reasons. - LINQ and EF will produce inefficient code, will perform data operations in the application layer that are better suited to be solved in SQL Server. My question is: How fact based are my suspicions? If I want every user to connect to the database as their Windows-login, and I want role-based, schema partitioned security in SQL Server; would EF and/or LINQ be an option? Do you have daily wrestling and/or boxing matches between SQL Server-teams and .NET teams where you work? Who's winning?
I have a bunch of experience with nHibernate and a little bit of experience with Entity Framework. Here's my take. If you simply use them out of the box and follow some of the most popular and simple examples, you'll end up with object oriented databases that have horrific structures that will be extremely difficult to maintain and a performance nightmare. They frequently leave off anything that resembles referential integrity. Reporting and data migration will all have to be customized using .NET tools instead of common, well known, software. But, if you can work well together, DBA and Developer, there's no reason why you can't arrive at a good database design with appropriate structures and referential integrity. You'll also primarily be using generated code for the database access, especially the CUD part of CRUD queries. Some of the read queries are not going to be generated well by the ORM tool you use, so you'll need to write stored procedures for a small percentage of the access (5-10%). The tools absolutely support and thrive under this type of approach. They will all work with appropriately designed databases and they will all consume stored procedures. It's just a little extra work up front to set this up. Further, the really high-end users of the tools will readily acknowledge this and plan for it in their designs. All that said, of the two scenario's, which do you think is the most common? Of the projects I've worked with, the bad patterns were done in most of them. And, despite being able to point to articles and white papers by the people who designed these tools, despite showing stacks of performance metrics and precise suggestions for how things could be done better, the projects went ahead with their crappy designs. Not fun, not fun at all. My final take. These tools are excellent, if properly used. If not, you're placing a loaded gun, with rounds chambered and the safety off in the hands of a monkey.
Excellent question! I don't trust them either and I have seen terrible code produced by it and have been tuning the queries to prove them unefficient. But I have also seen terrible code created by hand from a .NET developer. If you let a developer touch your database, you've to be prepared for some heavy fights, and it may be an unfair fight because it's like 10-20 developer for each skilled DBA/database developer. And you've to use your super power Performance tuning to win the fight. Those who know me and my super powers have given up the fight. :) If you don't need to care about performance, you might allow Linq / entity framework.
"•LINQ and EF will produce inefficient code, will perform data operations in the application layer that are better suited to be solved in SQL Server" I can say this used to be true. About three years ago a co-worker and I went through several LINQ generated queries with the profiler and for all but the very simplest test cases our hand-crafted SQL came out as vastly more effecient. The key part of that statement though is that it was three years ago. I have not repeated the trials with some of the updates that have been released and I have read that it has at least improved, though not yet reached parity with good hand crafted SQL. Hand crafted SQL, done by someone knowledgeable both about SQL and about that particular database, will always have an advantage in that a DBA is likely to know how the database will be used in the future, and especially when the database is undergoing heavy changes that knowledge can be very significant. LINQ is also unlikely to ever take advantage of the powerful, but sometimes dangerous or fragile, advantages that can come from using query hints like [NOEXPAND] or NOLOCK. I suspect that hand crafted SQL will have an advantage over any ORM for a very long time when it comes to performance. But, LINQ seems to be getting better. Also, used properly (as Grant discusses eloquently), LINQ can offer a lot of advantages to a developer accustomed to an object-oriented mindset, especially if that developer has only limited support from a SQL expert. :