Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We currently have the following stack :

  • VS 2005
  • Web forms
  • SQL Server 2005
  • IIS 6

We are planning on transitioning to this :

  • VS 2010
  • MVC and Web Forms
  • SQL Server 2008
  • IIS 7

My question is, when we move to MVC with VS 2010, should we use Entity Framework( or another ORM), a micro ORM (like Massive), or just plain SQL?

All the tutorials I've read about VS 2010 are all geared towards using Entity Framework for data transactions, but is that going to be around for the foreseeable future (5+ years)?

If it matters, our client's applications can have anywhere from 10 - 1,000 active users.

share|improve this question
    
Are you using Linq-to-SQL currently? –  Morgan Herlocker Oct 19 '11 at 14:56
    
We are using parameterized SQL –  guanome Oct 19 '11 at 15:20
4  
Avoid using SQL directly in your future development. ORM or EF are almost a must. Devote sometime to have a strategy for your data access layer. It is a critical decision and it is not a trivial task. Make sure you have enough time for you and the team to learn it. Introduction of new core technology to the team must be managed. Choose the tool, choose the material, have some education, ..., then evaluate and decide. –  Emmad Kareem Oct 19 '11 at 23:53
1  
New or existing databases? There's potentially a huge difference between building a new DB with the conventions of EF in mind, and trying to retrofit EF on top of an existing DB that wasn't built for ORMs. –  rmac Dec 26 '12 at 11:53
    
@rmac It was for a new database. –  guanome Jan 2 '13 at 16:03
show 1 more comment

5 Answers

up vote 29 down vote accepted

I recently switched from using in-line SQL queries to using EF and here's what I've found:

Pros

  • Much faster to build the DAL (love not writing the SQL queries!)
  • Much easier to maintain
  • No longer need to remember to parse my input before building an in-line sql statement, which means less chance of a SQL injection attack (of course, it's still possible depending on your queries, but much less likely)

Cons

  • Cannot span multiple databases... at least not easily
  • All entities (tables, views, etc) need a primary key
  • If you want to update a single column in a 100+ required columns table (not my table design), you have to pull down all 100 columns to make the update. Or use a Stored Procedure.
  • I've had issues with some default values defined on SQL server not getting pulled into the entity model after a new record gets added. Usually this is with computed values, or values that get added in an INSERT Trigger
  • On occasion, the SQL queries get badly written and are slow to execute. If you have a slow-running query, run a SQL trace to see what EF is doing. It's possible you can re-work that query as a SP or View. This doesn't happen that often though.
  • I've had a few issues with trying to create an association between tables that do not have a Foreign Key defined in SQL Server. Usually it's because I'm trying to create a 1:0-1 relationship where EF wants to use a 1:0-*

I'm no EF expert though, so I probably missed some things. These are just the items I know I've encounted in the past when switching from inline SQL to Entity Framework. I am glad I made the switch, but there have been times when I've really hated EF due to its quirks.

share|improve this answer
    
Your points are all true. There are ways to deal with most them (though I do not know of any for the triggers) but they require some effort. –  Chad Oct 19 '11 at 19:28
    
@Chad Yes, I've found ways around most of these issues, but I wish I'd known this information when I first started using EF. For example, not being able to span multiple databases with a single EF model was a deal breaker for one project I was working on, although in the end I did find a way around it using Synonyms –  Rachel Oct 19 '11 at 19:40
5  
+1 for detailed and organized answer. "All entities (tables, views, etc) need a primary key" sounds like a reasonable restriction rather than a con. –  Emmad Kareem Oct 19 '11 at 23:43
2  
@EmmadKareem Its an ok restriction if you have control over the database, but if you're working with a 3rd party database, or with views, it can be a bit annoying –  Rachel Oct 20 '11 at 2:51
4  
@EmmadKareem entities really need a single valued primary key - using composite keys is a nightmare in EF. This is a con rather than a reasonable restriction. –  Kirk Broadhurst Dec 26 '12 at 7:34
show 3 more comments

Entity Framework is a productivity tool. Unless you have a good reason not to (E.G. you are on SQL 2000 or have no time to ramp up on the technology), then use the best tools at your disposal.

That being said, I find the concept of Entities to translate very well to the MVC pattern's Model. While having a 1:1 relationship with Models and tables is a bad practice, thinking in terms of Entities tends to produce clean designs, easy to read code (especially with LINQ).

Entity Framework is actively supported by Microsoft. No one has a magic crystal ball to say "support will last X years". I see no reason to believe Entity will die in the next 5 years.

share|improve this answer
3  
LinqToSql died pretty fast, so there's really no reason to believe one way or the other whether Entity Framework will survive. It is well worth taking into consideration MS's new push toward Metro, in as much as they may be considering overhaul of many of their offerings. –  Slomojo Oct 20 '11 at 2:50
2  
Slomojo, may be you have a different definition from the rest of the world of the word 'Dead'. Because LinqToSql is just not being actively developed anymore. You will still be able to use it 10-20 years from now. –  Boris Yankov Oct 20 '11 at 16:09
add comment

You have to make an architectural decision based on the problem and existing solution. As with any technology there are advantages and disadvantages.

I personaly would normally use the entity framework for new development but not rewrite working existing code. You then get the speed for future delelopment but dont have to invest lots of time converting code. The downside of that approach is it reduces consistancy.

share|improve this answer
3  
+1 for recommending not to re-write working code. –  Emmad Kareem Oct 19 '11 at 23:44
add comment

Another potential solution is to use an alternative Entity Framework Library that is not the one supplied with V.S. There are a few out there on the web.

The Entity / 3 layers framework concept, has been out there for a while, and have work with several custom libraries, like many other developers, before Microsoft released its own "official" framework.

Pros

Have the benefits of the Entity (D.A.L.) framework, without been stuck with Microsoft constant libraries / framework changes.

Adding features to a library that are maybe not available to the existing official library, like using several dtabase brands.

Cons

Have to support the library or tools. Its very common to have a Entity generator code tool to generate the enitites.

share|improve this answer
add comment

In your situation I would definatly use Entity Framework, I've found it works well with MVC.
Here are some real reason and pointers.

  • Linq is a pleasure to use, and the delayed execution is also extremely useful.
  • You can generate your models (however when using with mvc I'd recomment you use view models in conjunction with the data models, this makes validation and model binding alot easier, if you do take that approach use automapper to map the changes back onto your data model).

There are a number of things you'll need to learn about using an ORM however.

  • What the context does for you (entity tracking)
  • That a context should be used as a unit of work
  • Remember to thing about concurrency, EF can tell you when your object is out of date but it can be tricky if you want to handle concurrency properly across requests (as you need to keep a timestamp or something).

Things to consider

  • Triggers and ORM's don't work together, use the ORM events instead.
  • Make sure all your tables have promary keys.

I would also highly recommend the code first approach, even if you have an existing database.

  • The conventions mean you don't need to regen mappings or classes when you change the database.
  • It's eaier to place validation and other logic in the models.
  • You can use the code generator to help make them if you have a huge existing database.
share|improve this answer
add comment

protected by Robert Harvey Jan 3 '13 at 23:31

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.