Entity Framework vs ADO.NET

Question

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

Answer 1

Very general question, but some thoughts.

Performance:

Plain SqlCommand and DataReader will be significantly faster when it comes to performance, if all developers have a key. With .net 4.5 and EF 5, it seems that EF will get a good 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 an asynchronous process, which can be very important in some scenarios. EF does not. At least not for EF 4.

Security

Regular SQL can be as safe as EF if you use parameterized queries. EF will do this automatically to protect you from SQL injection. Since EF always gives you this, I find it safer, but by a small margin.

Testability

I found this to be a big win when it comes to EF. Instead of cheating with a mockery, I quickly run integration tests with my controllers using SqlCe4. This is very easy to do if you use EF.

Summary

I find EF very capable, and the API is fun to work with. If you do heavy work, you may have to switch to 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 a loss of performance because I am more productive. Where I feel the punch is big, I will use simple Sql.

Answer 2

I use both options depending on the needs of the project. Here is my trick:

better (very ambiguous)

In this case, I would better define a product / function that would provide me with a way to create a solution with less code to write, fewer runtime errors, and more features.

In this aspect, the Entity Framework (EF) gives me Insert / Update / Delete instructions, 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 right knowledge of how to use EF, the speed difference is negligible.

safety

Neither ADO nor EF provide any security features (as far as I know). Security is usually controlled by presentation (IIS, Winforms, etc.) and / or on the SQL server.

The only serious limitation that I found using EF (and maybe I did not find a solution) is the impossibility for it of syntax 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

With respect to stored procedures, the Entity Framework (EF) infrastructure is currently very mature, and not only Store procedures are called from EF, but each model data method (Select, Insert, Update, Delete) can be associated with a Saved procedure. I personally have not done this, but it is definitely part of the framework.

Regarding the call to a stored procedure from a security point of view, the security of stored procedures is stored on the SQL server. If it cannot be called from EF, then it cannot also be called from SqlCommand.

Answer 3

I think that if you go to the Entity framework this will save you time on dev since it will generate most of the code for you. But in the case of ADO.NET with SQlConnection and SQLCommand, you need to write your own methods for creating / updating / deleting at your data access level.

I seriously think that using the ADO.NET stored procedure provides some performance benefit because the execution plan is stored on the server.

When an SQL statement is executed, the database must generate an execution plan for it. If this SQL statement is executed multiple times, each time that will be executed, the database may need to restore the query execution plan. In cases where SQL runs frequently, moving SQL to a stored procedure can provide more performance (not to mention more security). The first time the stored procedure is executed, the database forms a query execution plan, saves the plan in the procedure cache, and then executes the stored procedure. On subsequent calls to the stored procedure, only the database engine should grab the query plan from the procedure cache and restart the stored procedure. Thus, he skips the step of developing a query plan for subsequent calls

http://msdn.microsoft.com/en-us/magazine/cc163799.aspx