ADO.NET vs. Entity Framework

ado.net asp.net-mvc-3 entity-framework

Question

In ASP.NET MVC3 projects, is it faster, more secure, to use Entity Framework (with the repository pattern) or to use ADO.NET with SqlConnection and SqlCommand?

1
4
3/21/2012 7:36:03 PM

Accepted Answer

Very general question but some thoughts.

Performance:

Plain SqlCommand and DataReader will be significantly faster when it comes to performance as long as all the developers has a clue. With .net 4.5 and EF 5 it seems like EF will get a nice performance boost but plain sql will always be faster.

See here for some numbers: http://blogs.msdn.com/b/adonet/archive/2012/02/14/sneak-preview-entity-framework-5-0-performance-improvements.aspx

Plain ADO.NET also supports the async patter which might be very important in some scenarios. EF doesn't. Atleast not for EF 4.

Security

Plain SQL might be as safe as EF as long as you use paramaterized queries. EF will do this automatically for you to protect you from SQL injection. Because EF always gives you this I would consider it safer but with a slight margin.

Testability

I have found this to be a big win when it comes to EF. Instead of fooling around with mocking I run fast intergration tests against my controllers using SqlCe4. It's very easy to do this as long as you use EF.

Summary

I find EF very capable and the API is pleasant to work with. If you are doing performance intensive things you will have to drop into raw SqlDataReader and SqlBulkCopy from time to time but mixing them is not a problem. I like to use EF where I can live with the performance loss because I'm more productive. Where I feel the hit is to big I will use plain Sql.

6
3/21/2012 7:42:45 PM

Popular Answer

I use both depending on the needs of a project. Here is my take:

better (very ambiguous)

In this instance I would define better as a product/feature that provide me with a way to create a solution with less code to write, less run-time bugs to detect, and more features.

In this aspect, Entity Framework (EF) gives me Insert/Update/Delete statements, strongly-typed models, and a way to create dynamic strongly-typed sql statements.

faster

EF is slower and depending on your experience/knowledge, it can be hundreds of times slower. With the correct knowledge of how to use EF the speed difference in negligible.

security

Neither ADO nor EF provide any means of security (to my knowledge). Security typically is controlled at the presentation (IIS, Winforms, etc) and/or on SQL server.

The only major limitation I've found using EF (and maybe I haven't found the solution) is the inability for it strongly-type update records like:

UPDATE [sometable]
SET [column1] = 'new value'
WHERE [column2] = 'shared ID value'

Where, depending on the reuse of this method, I either use SqlCommand or write a Stored Procedure.

Update from 1st Comment

In regards to Stored Procedures, Entity Framework (EF) is very mature at this point, and not only can Store Procedures be called from EF, but each model's data methods (Select, Insert, Update, Delete) can be mapped to a Stored Procedure. I have not personally done this, but it's definitely part of the framework.

As for calling a Stored Procedure from a security point of view, the Stored Procedures security is stored on the SQL server. If it can't be called from EF then it wouldn't be able to be called from a SqlCommand either.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow