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.

Robert Harvey
  • 198,589
  • 55
  • 464
  • 673
FarFigNewton
  • 525
  • 1
  • 4
  • 11
  • Are you using Linq-to-SQL currently? – Morgan Herlocker Oct 19 '11 at 14:56
  • We are using parameterized SQL – FarFigNewton 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. – NoChance Oct 19 '11 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 Dec 26 '12 at 11:53
  • @rmac It was for a new database. – FarFigNewton Jan 02 '13 at 16:03
  • EF has nothing to do with MVC, it is merely a model provider. Its possible to have classes and a repository pattern component based on raw ADO and stored procedures that was used as a DAL for windows, ASP.NET Web forms and now MVC, with hardly any code changes. Mind you, I only ever use SQL Server as a database. –  Jan 03 '13 at 23:28

5 Answers5

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.

Rachel
  • 23,979
  • 16
  • 91
  • 159
  • 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. – SoylentGray 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
  • 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 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 02:51
  • @Rachel thanks for the cons, this something I haven't seen a lot of information about. – FarFigNewton Oct 24 '11 at 15:14
  • 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 Dec 19 '12 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. – Kirk Broadhurst Dec 26 '12 at 07:34
  • At this point it is possible to update entities that have only select columns retrieved, as long as the primary key is set. With deleting too you can create a blank entity object and set the primary key and cause a delete without pulling the entity in at all. – jnm2 Apr 01 '14 at 12:28
  • @EmmadKareem I disagree - dual-field composite keys ARE the correct solution for link tables and having to add another single-value key just to support EF is very messy and not good database design. It's one major reason I don't use EF. – NickG Mar 05 '15 at 14:14
  • @NickG, I did not say it is wrong to have dual-field composite keys (although they are slower since they require 2 indexes to be built instead of 1). However, I would not give up the EF just for this restriction alone. – NoChance Mar 08 '15 at 02:22
  • 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 Jul 17 '15 at 06:10
  • +1 for "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." That's something I've wanted to do for a while anyway. – Andy Nov 29 '16 at 14:40
  • Another big con is startup time. I have used EF in multiple projects and starting up your app will take a lot of time when your application grows around 100+ tables. – Rob Angelier Jan 27 '17 at 07:35
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.

P.Brian.Mackey
  • 11,123
  • 8
  • 48
  • 87
  • 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 Oct 20 '11 at 02: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. – Boris Yankov Oct 20 '11 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.

umlcat
  • 2,146
  • 11
  • 16
  • 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 Mar 05 '15 at 14:19
  • Altought there is an "Microsoft Entity Framework", the "Entity Framework" concept has been around for several years. – umlcat Mar 05 '15 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.

Tom Squires
  • 17,695
  • 11
  • 67
  • 88
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.
Daniel Little
  • 1,402
  • 12
  • 16