Entity Framework vs Traditional ADO.Net

Question

Hello everyone,

 

I work for a small software company and we are planing to build a web based application using asp.net mvc and sql server. All my past experience is with traditional style ado.net/ sql queries/ sps etc for data access and manipulation.

 

My question is, should we look into EF as it is gaining it's popularity. Is there any advantage / disadvantage of using EF over traditional ADO.Net. Are there any proven large scale projects built using EF? What are you people using in your company for dataaccess?

 

I would be really very thankful for your help.

 

Regards,

TK Lee

Answer 1

Hi TK

i have no arguments that would speak against using EF. I am building an enterprise MVC3 application thats running on windows azure and hopefully for tousands of users. I have choosen the code first approach and i dont regret anythng. Its a complete new way in designing and developing testable applications. The advantage of using EF is surely the strong typed entities with the power of linq and the easy transaction management (SaveChanges does the work for you). Together with a nice design you can build really nice architectures. I suggest for MVC projects

1. use a dependency resolver like UNITY for all your services
2. Implement the WorkOfUnit pattern together with a contextbuilder class
3. Use a generic repository for your entities
4. Inject the dbContext, unitofwork, repositores via Constructor injection in your services
5. Inject the services in your MVC controllers

I made this so far, that i created a testenvironment that allows to use my complete infrastructure. I use for unit testing a SQL CE database. With code first you can let create your database automatically. I used this for creating / initializing the testdatabase for each test run. SQL CE i use because I can copy the database file for each test method.. Also it runs on TFS.

There are many other advantages like scaffolding (if you use it) and soon migration support is implemented as well in ef.

If found a codeplex projekt that looks similiar to my architecture: http://efmvc.codeplex.com/

 

Hope that helps a bit :)

Holger

Answer 2

On 10/7/2011 12:13 PM, tkl33 wrote:

> Hello everyone,

>

> I work for a small software company and we are planing to build a web

> based application using asp.net mvc and sql server. All my past

> experience is with traditional style ado.net/ sql queries/ sps etc for

> data access and manipulation.

>

> My question is, should we look into EF as it is gaining it's popularity.

> Is there any advantage / disadvantage of using EF over traditional

> ADO.Net. Are there any proven large scale projects built using EF? What

> are you people using in your company for dataaccess?

>

> I would be really very thankful for your help.

>

 

I worked in two shops that were using EF. One shop used EF exclusively

no sprocs period were used after EF started to be used in solutions. The

second shop allowed EF and Linq-2-Entries to be used to query, but for

add, updates and deletes only sprocs could be called by EF.

 

Each shop was using EF in n-tier with MVP or MVC and WCF Web service

with high volume traffic applications.

 

Here are some links you may want to look at.

 

http://msdn.microsoft.com/en-us/library/cc853327.aspx

 

Compiled views stay in static memory on ASP.NET Web application on Web

server until the ASP.NET Worker Process is recycled.

 

Use a profiler to view the T-SQL generated to make sure you are getting

optimal performance -- by just changing the Linq query to generate a

different set of T-SQL can give you better performance.

 

Be aware of using the T-SQL Nolock when querying....

 

http://stackoverflow.com/questions/926656/entity-framework-with-nolock/1467052

 

http://msdn.microsoft.com/en-us/library/bb387145.aspx

 

http://blogs.msdn.com/b/alexj/archive/2009/03/26/index-of-tips.aspx

 

I have seen MVC used in applications, but I have never implemented it. I

think MVC is not flexible.

 

MVP is flexible, it's a derivative of MVC and there are several ways to

implement it.

 

One way is to use events.

 

http://polymorphicpodcast.com/shows/mv-patterns/

 

The second way is to use dependency injection to inject the view and the

service layer into the presenter.

 

<http://blog.vuscode.com/malovicn/archive/2007/11/04/model-view-presenter-mvp-design-pattern-close-look-part-2-passive-view.aspx>

 The first link will show you how to use the service layer. But you can

take the instance of the service layer and inject it into the presenter

along with the view. You do it in the Page's class constructor and not

on the Page_Load() in the example.

 

Public void ClassHelp

{

   public ClassHelp

   {

      _presenter = new UserDetailsPresenter(this, servicelayer instance);

   }

}

 

Your Page_Load() would call _presnter.PageLoad();

 

On the IView, you can create a control.

 

public instance IView

{

  public Textbox tbName {get; set;)

}

 

ClassHelp would implement IView.

 public Textbox tbName

{

    get

    {

      return TBName;  //TBName would be the name of the textbox HTML

control

    }

     set

    {

      TBName = value;

    }

}

 In the presenter you can do this.

 

var name = mView.tbName.Text;

 

or

 

mView.tbName.Text = "Larry";

 You can do any control that way with results being no code in the code

behind dealing with controls.

 

You have a control wires up and event on the codebehind, then pass the

object and event args to the _presenter.EventMethod(object sender, event

args)

 

Answer 3

Hello Holger K,

 

Thanks for your reply. Actually I'm very new to EF and our team is about to start a Web-based enterprise application aswell. I'm bit confused whether should we go with EF or write our own DAL. Have u found any difficulties implementing EF in your project? any limitations compared to traditional ADO.net data access? Any major advantage of using EF over traditional data access?

 

Regards,

TK Lee

Answer 4

Hi TK,

no i didnt find any disadvantages. The only "disadvantage" was the missing migration support for code first. But thats in work..

People say "performance" is a kind of disadvantage. But like darnold suggested aswell, i use sql profiler to see what sql is generated by EF and i optimize the sql execution tree's by creating indexes for the most important queries. Thats why I inherit a entity repository from the generic repository to implement each dbset query in one single repository. This is a nice design, because you see which queries you may have to optimize and you dont need to search for them somewhere in a DAL.

feel free to ask here about any concerns or questions..

greetings

Holger

Answer 5

On 10/7/2011 10:59 PM, tkl33 wrote:

> Hello Holger K,

>

> Thanks for your reply. Actually I'm very new to EF and our team is about

> to start a Web-based enterprise application aswell. I'm bit confused

> whether should we go with EF or write our own DAL. Have u found any

> difficulties implementing EF in your project? any limitations compared

> to traditional ADO.net data access? Any major advantage of using EF over

> traditional data access?

 

Your other option is to use nHibernate a mature and robust ORM solution,

as opposed to EF which is still in its infancy stag.

 

Your DAL would use either nHibernate or EF with methods in the DAL

working with the ORM solution for CRUD operations on the model. Also one

doesn't expose the entities from nHibernate or EF at the UI of a Web

application.

 

An ORM solution doesn't replace the DAL. The DAL works with the ORM

solution just as if you were using ADO.NET and SQL command objects with

T-SQL and sprocs for CRUD operations in the DAL.

 

Each one of the Dofactory for E-commerce  solutions show how to use the

DAL with Link-2-SQL or EF. As a matter of fact, each one of the

E-commerce solution are using the same back-end of BLL and DAL which is

interchangeable between the E-commerce solutions.

 

http://www.dofactory.com/Framework/Framework.aspx

 

EF is using this to execute T-SQL with all the advantages that using a

sproc has like caching, compiled sprocs and the whole nine yards.

 

http://msdn.microsoft.com/en-us/library/ms188001.aspx

 

The major advantage of using an ORM solution like EF over the straight

ADO.NET with SQL Command objects is this.

 

http://en.wikipedia.org/wiki/Object-oriented_programming

http://en.wikipedia.org/wiki/Language_Integrated_Query

http://en.wikipedia.org/wiki/Object-relational_mapping

http://en.wikipedia.org/wiki/Design_Patterns

http://en.wikipedia.org/wiki/Domain-driven_design

http://en.wikipedia.org/wiki/Test-driven_development

 

The above is what an ORM like EF brings to the table when developing

n-tier solutions.

 

The only disadvantage of using any ORM solution is the developer's

inability to use the ORM effectively.

 

Answer 6

On 10/7/2011 10:59 PM, tkl33 wrote:

> Hello Holger K,

>

> Thanks for your reply. Actually I'm very new to EF and our team is about

> to start a Web-based enterprise application aswell. I'm bit confused

> whether should we go with EF or write our own DAL. Have u found any

> difficulties implementing EF in your project? any limitations compared

> to traditional ADO.net data access? Any major advantage of using EF over

> traditional data access?

 

Your other option is to use nHibernate a mature and robust ORM solution,

as opposed to EF which is still in its infancy stag.

 

Your DAL would use either nHibernate or EF with methods in the DAL

working with the ORM solution for CRUD operations on the model. Also one

doesn't expose the entities from nHibernate or EF at the UI of a Web

application.

 

An ORM solution doesn't replace the DAL. The DAL works with the ORM

solution just as if you were using ADO.NET and SQL command objects with

T-SQL and sprocs for CRUD operations in the DAL.

 

Each one of the Dofactory for E-commerce  solutions show how to use the

DAL with Link-2-SQL or EF. As a matter of fact, each one of the

E-commerce solution are using the same back-end of BLL and DAL which is

interchangeable between the E-commerce solutions.

 

http://www.dofactory.com/Framework/Framework.aspx

 

EF is using this to execute T-SQL with all the advantages that using a

sproc has like caching, compiled sprocs and the whole nine yards.

 

http://msdn.microsoft.com/en-us/library/ms188001.aspx

 

The major advantage of using an ORM solution like EF over the straight

ADO.NET with SQL Command objects is this.

 

http://en.wikipedia.org/wiki/Object-oriented_programming

http://en.wikipedia.org/wiki/Language_Integrated_Query

http://en.wikipedia.org/wiki/Object-relational_mapping

http://en.wikipedia.org/wiki/Design_Patterns

http://en.wikipedia.org/wiki/Domain-driven_design

http://en.wikipedia.org/wiki/Test-driven_development

 

The above is what an ORM like EF brings to the table when developing

n-tier solutions.

 

The only disadvantage of using any ORM solution is the developer's

inability to use the ORM effectively.

 

Thanks very much guys, it really helped. I'm still trying to convince my team lead to go with EF instead of manually coding own DAL from scratch. He has a concern about versioning and migration of database. Lets say if we manually create a trigger on database or if we write a LINQ query and let EF generate SQL query. Now if we optimize that SQL query for performance. When we would deploy our product for some other client, we are going to loose our changes no? because EF would regenerate it's own query. In that case we would have to maintain our own sql scripts for triggers, hand written optimized sql queries etc.. no? or is there any automated way in EF to sync those things?

Answer 7

Hi,

Code First Migration is here: http://blogs.msdn.com/b/adonet/archive/2011/09/21/code-first-migrations-alpha-3-released.aspx

Have nice day.

---

Alan Chen[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.