asked
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 been trying to read up on performance with EF SQL and/or LINQ, and found that in later versions, EF and LINQ perform much better than in their original releases. That's natural. And it's possible to use stored procedures instead of direct data access when needed. So from a pure SQL Server perspective, I'd rather go for hand-crafted SQL, but on the other hand, but I think it's quite possible to find an acceptable compromise between performance and flexibility. But my bigger concern is security. I want to use schema partitioning to allow/disallow access to certain parts of the database to different users. How will EF be able to handle that? Anyone with experience from such scenario?
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.
Thanks for valuable input. What about security considerations? If it would be up to me, wearing my SQL Server glasses, I would want users to connect to SQL Server as themselves, and I'd like to setup security in SQL Server so that each user and group of users have access only to the data they should have access to. Sometimes, this would mean providing read and/or write access directly to tables, sometimes it would mean creating stored procedures with the EXECUTE AS clause to provide row level security features. When using EF and LINQ - is that approach even possible?
Security? What's security? Seriously, you have to unzip. No choices. I've had to deal with data driven security for so long, I've probably forgotten most of what little I knew about database security.
So what you're saying is basically: "Let the app connect, and don't bother about being able to log who's REALLY connecting, that's an application task"? If that's it, it will take some time getting used to...
Yeah, it takes getting used to and it can make troubleshooting issues much more difficult. Who is doing this bad thing to the server? Frequently we never knew.
Though I still have my concerns about permissions and connecting identity, I guess I'll have to use a pragmatic approach, and profile tests to find the weakest performance spots to replace with stored procedures. Thanks for your input.
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.
There are other super-powers for a DBA/DB Developer, the ,most powerful being DENY CONNECT TO ... Thanks for your input!
"•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][1] 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. [1]:
http://www.sqlservercentral.com/articles/Indexed+Views/63963/
