31

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.

6
  • Are you using Linq-to-SQL currently?
    – user3792
    Commented Oct 19, 2011 at 14:56
  • We are using parameterized SQL Commented Oct 19, 2011 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.
    – NoChance
    Commented Oct 19, 2011 at 23:53
  • 2
    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
    Commented Dec 26, 2012 at 11:53
  • @rmac It was for a new database. Commented Jan 2, 2013 at 16:03

5 Answers 5

45

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.

13
  • 7
    +1 for detailed and organized answer. "All entities (tables, views, etc) need a primary key" sounds like a reasonable restriction rather than a con.
    – NoChance
    Commented Oct 19, 2011 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
    Commented Oct 20, 2011 at 2:51
  • 1
    Just try using EF in a disconnected N-Tier web app - updating entities in a session and M-M relationships, hmmmmm what fun!
    – Vidar
    Commented Dec 19, 2012 at 10:56
  • 5
    @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. Commented Dec 26, 2012 at 7:34
  • 1
    I'd say security is another issue. Many think all DB access should go through stored procedures with DB roles associated with procs to determine what logins can execute what stored procs. This rules out EF/LINQ for creating queries. I've used EF but have come across clients (cough Microsoft) who had these security requirements
    – Mick
    Commented Jul 17, 2015 at 6:10
12

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.

2
  • 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.
    – ocodo
    Commented Oct 20, 2011 at 2:50
  • 3
    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. Commented Oct 20, 2011 at 16:09
4

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.

2
  • I find this answer very confusing. There is only one Entity Framework (with capital letters) which is the the one Microsoft produce. Do you mean "use another object relational mapper"? Entity Framework is not a generic term - it is the name of Microsoft's ORM.
    – NickG
    Commented Mar 5, 2015 at 14:19
  • Altought there is an "Microsoft Entity Framework", the "Entity Framework" concept has been around for several years.
    – umlcat
    Commented Mar 5, 2015 at 17:04
3

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.

1
  • 3
    +1 for recommending not to re-write working code.
    – NoChance
    Commented Oct 19, 2011 at 23:44
2

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.

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