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 ?

Marshmallow

Read this: blog.stackoverflow.com/2011/08/gorilla-vs-shark and go with EF – Domino

Going with EF is still a better choice over Dataset? – Marshmallow

apparently you didn't read the blog post linked – Domino

well... yes I agree this is a gorilla vs shark like question, but you are saying gorilla wins here by saying "go with EF". why EF? just because Dataset is an old technology? Microsoft will focus on EF not Dataset? ORM is better even if I work with stored procedures? I should persuade my team to go with EF with more detailed explanation. – Marshmallow

Seems like this is a topic which needs more debate and some more senior experienced members to comment on. – Frizzly

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.

Leeanneleeboard

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.

Perambulate

Khoa - Appreciate the feedback but to be clear, I was NOT stating Datasets are inferior to EF or saying to use EF, I actually do not prefer EF and that is not an even comparison. I was addressing the pros/cons of EF so the reader can decide what tool works given the job. The point regarding Datasets being inefficient is related to this being a web application (or WCF) and typically a Dataset is overkill for CRUD given a request/response service (e.g., I prefer a CQRS pattern using DTOs and Dapper). DataSet's primary use case are for apps requiring disconnected in-memory data manipulation. – Leeanneleeboard

To be short, my point is "If you have a simple CRUD application you can go with EF. If it is application with heavy data access (bulk update, stored procedures) then go with thing that supports these well". – Perambulate

I love it how a comment more than four years later results in an interaction with the original poster from more than four years before. That's real community! Thanks to you both for helping me decide the DS v EF question. – Edana

Good answer, you are an honest man. In the old days, I'd created a new Dataset structure by myself and let ms typed dataset diagram editor generate code for my Dataset, it makes DataRow support concurrent access. What's a nice day! Drop tables into Diagram Editor and get ER model and get typed dataset code, and works with GUI immediately. – Inexperience