Dataset vs Entity Framework with stored procedures

Question

The whole question has been rewritten to be more clear..

New project design:

1. Sql Server 2012
2. Visual Studio 2012 .Net 4.5
3. Business logic will be implemented in stored procedures
4. ASP.Net Webforms
5. WCF SOAP XML Web Service to communicate with database using provided stored procedures by DBA
6. Entity Framework or Dataset

Here I can use Dataset - no problem, but I would like to know the advantage of Entity Framework over Dataset in more detailed explanation. I've been reading articles about entity framework, and I saw people had better experience using EF over dataset due to following reasons.

I would like to know if these are still advantages that I can obtain using EF in my case - database related actions are always done with stored procedures:

1. EF is a lot cleaner and much easier to maintain and program against. Queries against the EF ObjectContext are always executed against the database

2. Because the mapping between your objects and your database is specified declaratively instead of in code, if you need to change your database schema, you can minimize the impact on the code you have to modify in your applications--so the system provides a level of abstraction which helps isolate the app from the database. The EF can therefore replace a large chunk of code you would otherwise have to write and maintain yourself.(What if stored procedure design has been changed?)

3. The EF was specifically structured to separate the process of mapping queries/shaping results from building objects and tracking changes.

4. DataSets suck, especially in a WCF scenario (they add a lot of overhead for handling in-memory data manipulation) -> means EF with WCF is better in performance ?

Answer 1

1. EF is a lot cleaner and much easier to maintain and program against ->> can you elaborate?.. is this because of #2 below?

EF is an Object Relational Mapper (ORM) and will automatically generate objects related to your database schema as noted in #2. EF is an out-of-box abstraction for your data access layer and in the current version implements a repository pattern. This gives you benefits such as LINQ, object graph CRUD operations, and what the EF team deems as best practice for accessing data via .NET.

The out-of-box functionality and ease of integration with the database (specifically SQL Server) can provide easier to maintain and program against. However, there are situations where using an ORM may not be the best option and you should use prudent judgement. Here are some scenarios to think about not using an ORM (especially when your team lacks current knowledge of EF): limited data queries, non-complex application, comfortable writing or using your data access layer, your application deadline is aggressive, etc. See other options I noted below.

2. If you need to change your database schema, you can minimize the impact on the code you have to modify in your applications ->> what if parameters and returned fields of a stored procedure are changed? EF still minimize the impact?

Yes, EF generates based off of EDMX file which is simply an XML file of your database schema. This includes updating objects that map to your stored procedures (NOTE: this is not applicable if using code first until EF6 is released). You can alter a stored procedure and EF can take the updated schema and update your code. However, you will have to fix your code where you called EF stored procedures methods and the parameters have changed. You can also use something LINQ to SQL if you are uncomfortable with EF which will provide stored procedure calls as object methods.

3. DataSets suck, especially in a WCF scenario (they add a lot of overhead for handling in-memory data manipulation) ->> Can you explain more?

"DataSets suck" is obviously a generic statement. You use DataSets for what they are intended for which is dealing with data in memory using .NET. Since DataSets are in memory they are considered inefficient when doing simple CRUD operations. It is recommended to use stored procedures and data readers (be sure to close them when done reading data) for efficient data reads with SQL database.

There are other options besides EF out there:

1. Do it yourself - Write stored procedures, use data readers, and map to POCO objects

2. Use a Dynamic Library - Dapper, ServiceStack ORM Lite, Simple POCO, Simple Data, Massive

3. Use LINQ to SQL - Lighter weight data access layer (Only for SQL Server )

4. Other ORMs - NHibernate is a top choice.

Answer 2

This is for responding to Jonathan's answer about DataSet. Because a comment allows too short content, therefore I put it as an answer.

This is old but till now, with EF6 and EF dotnet core , I see that it's still valid to argue this point.

I'm finding opinions about this topic and eventually reached this post. Honestly, I quite disagree with Jonathan about DataSet.

To response to "Since DataSets are in memory they are considered inefficient when doing simple CRUD operations", I believe EF's DbContext is in memory too, and offline data processing is not only intended for DataSet, but also LinQ2SQL and EF. Simple CRUD operation can be done with DataTableAdapter and CommandBuilder, you don't need to write single SQL statements. DataSet is part of ADO.NET, we can't say ADO.NET recommends Stored Procedure or something else, it must support all the ways of accessing database, actually DataSet doesn't access database, DataTableAdapter, DataReader, DbCommand do.

If you use Typed DataSet of Visual Studio, you will see it far superior than EF in Stored Procedure mapping and batch operation. My project has 5000 stored procedures and mapping to EF is painful, EF throws many kind of errors if the declarations are not supported by EF. On the other hand, Typed DataSet allows controlling very small details of stored procedure mapping, bend it for your need. About batch operations, Table adapters allows you to control how many statements can be sent at once, How about EF? 1 by 1, Just imagine you need to import 10000 records, I did a comparison for this scenario, there's no way to make it quick in EF way. If stored procedure is changed, give me 30 sec to fix the DataSet, either regenerating it or fixing it manually. You will find it no faster in EF.

There's no way EF is faster than ADO.NET using DataSet. EF adds a lot overhead of parsing ExpressionTree, evaluating it, generating SQL statement before using ADO.NET to update the database, under EF's hood is ADO.NET.

The only thing I found inconvenient with DataSet is that DataRow is not POCO. It's difficult to serialize them, makes it suck in WCF when you have to map it to DataContract, you can use EF's POCO as data contract directly.

For projects those you need to deal with a lot of data, bulk update, stored procedures, stay away from EF. I gave EF too many trials to play with Enterprise projects, but eventually I had to use Typed DataSet for them all. I'm still looking for opinions because I still hope that EF will be able to answer my need. I do like the simplicity of EF's approach, but it's still too young comparing to Typed DataSet.