I think EF's support for Stored Procedures is great.
It's abstracted to the point of just calling a method on your DbContext
using (var db = new PersonContext())
var results = db.CallStoredProcedure(stored, procedure, params);
If devs don't take advantage of this, you can't really blame EF.
Basically, the problem comes from developers tying EF directly to the table structure of the database and not using views, stored procs and so forth to take advantage of the power of the database. Then, they use LINQ to handle all the sorting and filtering of the data. When you walk into a legacy system where it was developed this way it can be very difficult to change.
But doesn’t LINQ to EF delay execution so stuff like order by is implemented at the database level, and not in memory, effectively leveraging the power of the database?
Stored procedures are faster because they have a predictable execution plan that takes full advantage of the database engine, primarily using a cached execution plan. While LINQ may delay execution, the execution plan in the database is reconstructed each time a query is ran and can often result in performance issues.
Complex queries that involve joins, groupings and such between tables or pulling data from tables in other databases are usually very inefficient in LINQ as compared to SQL. This is something that's more common when dealing with existing legacy databases in a corporate IT environment vs a "green field" new development situation. A lot of this data isn't laid out in a way that's particularly friendly to LINQ or EF in general.
LINQ via EF is also considerably slower when it comes to bulk insert, update and deletes.
The advantage of LINQ primarily lies in the ability to shield .NET developers from having to learn how to develop and maintain good SQL. It also makes debugging easier for them since they don't have to learn how to debug queries or how to craft execution plans.
Okay, but like you're bouncing all over the place now. And not replying to my statements direct.
I understand that stored procedures can be more efficient, but again, EF has support for just calling stored procedures or views, so it's not EF's fault it devs don't use it.
If I "sort" in LINQ using LINQ to EF, it just adds an ORDER BY to the query it sends to the DB when you finally call .ToList(), so it is doing that on the database.
I don't understand what you mean by LINQ vs SQL... LINQ isn't an ORM, it's just a way for devs to write SQL using connectors like LINQ to SQL. The advtange of LINQ lies in it's ability to save .NET Developers time from wiring up a query and an object mapping when they need to do is db.Books.Where(book => book.Author == "George R.R. Martin");
Using EF doesn't prevent developers from doing any of powerful database things that you suggest doing. Maybe some developers don't know how to take advantage of these things, and that's what you've had to experience, but that's not EF's fault.
EF gives you the ability to decide where you leverage Stored Procedures and views, where you leverage ADO.NET and even where you can write your own SQL. The only thing EF does is track your entities in memory to know when they've changed. If you know when and where to leverage the database, EF will permit you to do so.
As Sam said you can switch from Lazy loading to Eager loading within EF in a few clicks with Lazy loading being the most used while Eager loading is good in some specific scenarios where you know that connected objects will be used in the future.
Also, use IQueryable vs IEnumerable to get the sorting done on the server.
However, it is evident that EF is slower than traditional SQL principles(connected and disconnected scenario) with the connected one being the fastest in pure speed. It's all about performance vs speed of development, and it really depends on the project you are working on.
I would go for EF with LINQ if performance is not a top priority, just because it saves so much time
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.