631

What are the pros & cons of using Entity Framework 4.1 Code-first over Model/Database-first with EDMX diagram?

I'm trying to fully understand all the approaches to building data access layer using EF 4.1. I'm using Repository pattern and IoC.

I know I can use code-first approach: define my entities and context by hand and use ModelBuilder to fine-tune the schema.

I can also create an EDMX diagram and choose a code generation step that uses T4 templates to generate the same POCO classes.

In both cases I end up with POCO object which are ORM agnostic and context that derives from DbContext.

Database-first seems to be most appealing since I can design database in Enterprise Manager, quickly synch the model and fine-tune it using the designer.

So what is the difference between those two approaches? Is it just about the preference VS2010 vs Enterprise Manager?

Romias
  • 12,977
  • 7
  • 51
  • 79
Jakub Konecki
  • 44,070
  • 6
  • 84
  • 125
  • 12
    Entity Framework 7 is dropping EDMX: https://msdn.microsoft.com/en-us/magazine/dn890367.aspx – CAD bloke Apr 15 '15 at 10:02
  • 5
    @CADbloke Entity Framework 7 is now Entity Framework Core 1.0 – RBT Jun 04 '16 at 16:02
  • 6
    To any other browsers, unless you have a hardon for 7000 long XML files and solving merge conflicts in the aforementioned, *go code first* and save yourself a headache – Dan Aug 08 '16 at 14:26
  • 3
    There's a good Jan 2015 write-up on the three approaches at http://roland.kierkels.net/c-asp-net/ef-model-vs-database-vs-code-first-approach/ – danio Feb 03 '17 at 16:57
  • 5
    Just about every answer given is *"I Think"*...the absolute definition of "Primarily Opinion Based". – user692942 Aug 15 '18 at 16:07

10 Answers10

726

I think the differences are:

Code first

  • Very popular because hardcore programmers don't like any kind of designers and defining mapping in EDMX xml is too complex.
  • Full control over the code (no autogenerated code which is hard to modify).
  • General expectation is that you do not bother with DB. DB is just a storage with no logic. EF will handle creation and you don't want to know how it does the job.
  • Manual changes to database will be most probably lost because your code defines the database.

Database first

  • Very popular if you have DB designed by DBAs, developed separately or if you have existing DB.
  • You will let EF create entities for you and after modification of mapping you will generate POCO entities.
  • If you want additional features in POCO entities you must either T4 modify template or use partial classes.
  • Manual changes to the database are possible because the database defines your domain model. You can always update model from database (this feature works quite well).
  • I often use this together VS Database projects (only Premium and Ultimate version).

Model first

  • IMHO popular if you are designer fan (= you don't like writing code or SQL).
  • You will "draw" your model and let workflow generate your database script and T4 template generate your POCO entities. You will lose part of the control on both your entities and database but for small easy projects you will be very productive.
  • If you want additional features in POCO entities you must either T4 modify template or use partial classes.
  • Manual changes to database will be most probably lost because your model defines the database. This works better if you have Database generation power pack installed. It will allow you updating database schema (instead of recreating) or updating database projects in VS.

I expect that in case of EF 4.1 there are several other features related to Code First vs. Model/Database first. Fluent API used in Code first doesn't offer all features of EDMX. I expect that features like stored procedures mapping, query views, defining views etc. works when using Model/Database first and DbContext (I haven't tried it yet) but they don't in Code first.

David Klempfner
  • 6,679
  • 15
  • 47
  • 102
Ladislav Mrnka
  • 349,807
  • 56
  • 643
  • 654
  • 5
    @Ladislav - thank you for the comprehensive answer. Just to clarify: except for some limitations of fluent API there are no real *technical* differences between those approaches? It's more about development/deployment process/methodology? For example, I have separate enviroments for Dev/Test/Beta/Prod and I will upgrade database manually on Beta/Prod as changes to the schema might require some complex data modifications. With Dev/Test I'm happy for EF to drop&create databases as I will seed them with test data myself in the initializers. – Jakub Konecki Mar 27 '11 at 09:00
  • 3
    @Jakub: There are no differences. Those are only differen approaches to work with the same EF core. It is like configuring IoC in config, by attributes or with fluent API - still the same result but different approaches and some limitations in each used approach. Check this question: http://stackoverflow.com/questions/5433318/how-do-i-allow-the-ef4-codefirst-database-initializer-to-run-under-development-b There some good ideas how to solve the issue with different environments. – Ladislav Mrnka Mar 27 '11 at 11:53
  • @Ladislav - Thanks! I've already solved the problem with different environments by supplying different initializers based on config which I transform in MSBuild. I'm implementing UnitOfWork pattern right now and hopefully all should be working fine. PS. Although code-first is more 'cool' I've opted for database-first, as I want more control over DB and model generation saves me from writing some code. PPS. I also started thinking about creating entity-framework-ladislav tag ;-) – Jakub Konecki Mar 27 '11 at 13:57
  • I am investigating the codefirst/model first approach and it doesnt appeal to me.Like many of us I want to control database schema objects. And on the top that Code first "kind a" not support StordProc is a big turn off. However I still like EF. I have heard that for large number of tables EF 4.0 slows things down. Any idea how to over come this and still be able to use std. db objects such as storeproc. May be POCO entity generator? – daehaai Jun 07 '11 at 07:16
  • If you want control over DB you should use Database first. Code first supports stored procedures but you cannot map them to insert, update and delete operations for entities. Visual studio 2010 SP1 has improved designer so it can works with many tables (not sure what many actually means) if you have really a lot of tables then you probably want more models. – Ladislav Mrnka Jun 07 '11 at 07:28
  • 156
    I've been designing databases for so long I can't seem to imagine ever doing anything but database first. In fact, I still write a lot of stored procedures for the more high volume select statements and such, and then I'll do a function import into the EF model all in the name of performance. – Steve Wortham Jun 14 '11 at 14:36
  • 9
    What do you mean by high volume select statements? Stored procedures are not faster then SELECTs send from application. – Piotr Perak Jan 12 '12 at 20:17
  • 3
    @Peri, while stored procs are not faster than select statements, you have control over the DML vs w/ an ORM you are the mercy of what is generated. In many cases the sql generated by ORM is nowhere nearly as performant as compared to what dba could create by hand... – B Z Jan 24 '12 at 18:56
  • 2
    Ok I agree about ORM, but you can have SQL in your application instead of in SP's. – Piotr Perak Jan 24 '12 at 21:19
  • 20
    You *can* have SQL in your application. That SQL will more than likely be embedded in compiled code, and any changes will require a recompile and redeployment while a Stored Procedure change will just require editing of the Stored Procedure. Customers/Clients/Users will be less impacted by changes in this case. – CodeWarrior Mar 07 '12 at 17:14
  • 5
    @JakubKonecki, whatever you don't find in the `DbContext` that exist in `ObjectContext` simply use `((IObjectContextAdapter)dbcontext).ObjectContext`. – Shimmy Weitzhandler Mar 12 '12 at 05:13
  • I recently did a hybrid version of this. I created a Code-First database from a model class library then for my actual Apps I use the DB First EDMX approach. This way my model library is decoupled from the applications (can be used for multiple applications). I also have the benefit of pretty abstracts in my models and the database generated is very uniform in convention. – d4rklit3 Aug 21 '12 at 19:04
  • I hate using a designer PERIOD. That's why I hate ORMs..one reason. I like to have control of what my code is doing and is capable of in terms of extending. Even with code first you're forced to use Entity framework conventions or attributes and I don't want that forced on me. – PositiveGuy Nov 27 '12 at 19:13
  • and who cares if you have to recompile if using parameterized sql BTW or LINQ. Big freakin deal! – PositiveGuy Nov 27 '12 at 19:17
  • 2
    Based on my experience, you can use code first if you're using an old version of SQL Server. Like in my case, I'm currently using SQL Server 2003 that is why I use the code first approach. Reason: Database First and Model First approach supports SQL Server 2005 and up. Although, you can use it with the SQL 2003 but it takes a lot of time to configure it. Just my 2 cents. – Musikero31 Dec 14 '12 at 08:52
  • 1
    A **concrete non-objective** con for model/DB: Modifications to EDMX to accommodate mappings that aren't supported by the designer are easily blown away. So you modify EDMX manually, but then you can never use model first UI or DB first update without losing those tweaks. Where as with code first you can use fluent mapping to prop up anything not supported by code first conventions, and both can be preserved/modified in parallel with no risk of being blown away. – AaronLS Jul 10 '14 at 22:40
  • If i am following Code First approach and later i need to add some properties in my models then what will happend ? CF will automatically create those properties to associated tables or i need to do some extra stuff ? please answer with appropriate links. – Ashish-BeJovial Apr 07 '15 at 04:32
  • 1
    @Ladislav - that you view using Model First and a designer as less than human, and that real programmers do not like designers, is very narrow minded and does not account for the visually oriented nor for dealing with large complex entity relationship models nor for communicating that design to others. EF should have been fixed so that all 3 approaches could be used interchangeably on the same database as there are advantages and disadvantages for all 3 approaches. – Dave May 04 '15 at 19:39
  • @LadislavMrnka, I have one more option that you might consider: using a database _model_ to generate code-first entities. My **[SqlSharpener](https://github.com/aeslinger0/sqlsharpener)** project can use T4 to generate entities from an SSDT project without having to deploy it first. Your DBA's still have control and your devs can regenerate whenever they want. Plus no huge edmx file to try and merge. – adam0101 May 26 '15 at 19:30
  • 1
    **Manual changes to database** is very easy and reliable with **Migrations**. Thusi +1 point to the code first. – ozgur Dec 13 '15 at 18:57
  • "General expectation is that you do not bother with DB. DB is just a storage with no logic. EF will handle creation and you don't want to know how it does the job." Are you kidding me? That's exactly the reason NOT TO :) – PHPer Feb 15 '17 at 19:59
  • funny note on "hardcore programmers". by the same logic, those who have professional pride and cant waste employer time for writing changes by hand on terabyte dbs with several hundred tables, these are idiots.. while those who are so scared that they don't know something about what they do, so they cant trust themselves, which manifests in petty control freakiness - those are heroes.. yeah Ladislav.sure – Boppity Bop Mar 19 '17 at 13:18
  • @SteveWortham I thought I was the only one that thought that way. – johnny Jul 12 '17 at 15:26
136

I think this simple "decision tree" by Julie Lerman the author of "Programming Entity Framework" should help making the decision with more confidence:

a decision tree to help choosing different approaches with EF

More info Here.

Do Nhu Vy
  • 33,131
  • 37
  • 143
  • 202
Bahador Izadpanah
  • 1,786
  • 1
  • 13
  • 13
  • 117
    This isn't complete. What if you prefer NOT to use a visual designer but you DO have an existing database? – Dave New Jan 20 '13 at 13:30
  • 19
    Even worse... real life decisions are not done by diagramms rather by technical limitations you face when using code-first, e.g. you can not create a unique index on a field or you can not delete hierarchical data in a tree table for this you need a CTE using the context.Table.SqlQuery("select..."). Model/Database first do not have these drawbacks. – Elisabeth Jan 31 '13 at 13:51
  • 33
    @davenewza that's the first path isn't it? – Chris S Feb 02 '13 at 21:58
  • 3
    @davenewza existing database => existing classes ? Code First : Database first :) – riadh gomri Apr 22 '13 at 15:43
  • 4
    @davenewza Use Entity framework Powertools to create your POCO classes from DB. [Code First to an Existing Database](http://msdn.microsoft.com/en-us/data/jj200620) – Iman Mahmoudinasab Jan 06 '14 at 13:23
  • 1
    Please update the link if you can as it seems to be broken at the moment – Izzy Apr 28 '15 at 16:16
  • 2
    This diagram is completely wrong. What if you don't like Visual Designer but already have a database? – Rexxo Jun 10 '15 at 14:27
  • 1
    That means, that even when you have database ready.. if you don't like designer you SHOULD code first. "First" cause it's kinda reverse engineering – Marshall Jan 25 '18 at 19:18
  • People saying to Code First for an existing Database as the logical one path... They are the few so lucky who never had to work on a legacy or messed database. – Andre Figueiredo Jan 26 '18 at 18:09
  • Even if you have existing database, still you can use code first – amal50 Jun 01 '18 at 14:40
  • This is misleading, you don't choose CodeFirst/DBFirst Just by thinking about it being visualizable or not, there are a lot issues you must address choosing either one, the answer got attention wrongly. – yekanchi May 18 '19 at 06:27
52

Database first and model first has no real differences. Generated code are the same and you can combine this approaches. For example, you can create database using designer, than you can alter database using sql script and update your model.

When you using code first you can't alter model without recreation database and losing all data. IMHO, this limitation is very strict and does not allow to use code first in production. For now it is not truly usable.

Second minor disadvantage of code first is that model builder require privileges on master database. This doesn't affect you if you using SQL Server Compact database or if you control database server.

Advantage of code first is very clean and simple code. You have full control of this code and can easily modify and use it as your view model.

I can recommend to use code first approach when you creating simple standalone application without versioning and using model\database first in projects that requires modification in production.

TobiMcNamobi
  • 4,305
  • 2
  • 29
  • 51
Stepan Smagin
  • 553
  • 4
  • 4
  • 7
    If you're going to be manually updating the production environment with SQL scripts you can still do the same with Code First. You simply generate the change scripts as necessary. Several tools can automate these deltas, and you can keep on using Code First. You'll just simply need to change the Code First initializer to something like CreateDatabaseIfNotExists so as not to delete the current database. – Esteban Brenes Jul 04 '12 at 22:54
  • Some differences are importing views then regenerating the database where the views become tables. Makes it hard to gen a new DB and compare to the prod DB to see if in sync. – Dave Aug 25 '13 at 15:53
  • Model First doesn't support user-defined SQL functions (at least in EF4, don't know if this has changed). With Database First, you can import UDFs and use them in your LINQ queries. – Tsahi Asher Apr 16 '14 at 20:30
  • No differences? Try importing views and SimpleMembership tables and then generate database from model and see what you get. Not even close! These should round trip but then MSFT has basically abandoned MF and DF in lieu of CF which is also incomplete in terms of using views and stored procs. – Dave Jun 15 '14 at 15:55
  • You can disable code first migrations based database recreation process and do it manually in model and database. you can do so by specifying disableDatabaseInitialization="true" in your web/app.config at ..... You can delete the migrations folder. – Hasteq Jun 17 '16 at 19:53
  • "When you using code first you can't alter model without recreation database and losing all data." - I created a very simple EF code first project, created a migration to add a column to an existing table, and applied the migration using `update-database`. All of the existing data still exists in my table, so it seems like this statement is not true? – Nathan Friend May 16 '18 at 17:35
38

Quoting the relevant parts from http://www.itworld.com/development/405005/3-reasons-use-code-first-design-entity-framework

3 reasons to use code first design with Entity Framework

1) Less cruft, less bloat

Using an existing database to generate a .edmx model file and the associated code models results in a giant pile of auto generated code. You’re implored never to touch these generated files lest you break something, or your changes get overwritten on the next generation. The context and initializer are jammed together in this mess as well. When you need to add functionality to your generated models, like a calculated read only property, you need to extend the model class. This ends up being a requirement for almost every model and you end up with an extension for everything.

With code first your hand coded models become your database. The exact files that you’re building are what generate the database design. There are no additional files and there is no need to create a class extension when you want to add properties or whatever else that the database doesn't need to know about. You can just add them into the same class as long as you follow the proper syntax. Heck, you can even generate a Model.edmx file to visualize your code if you want.

2) Greater Control

When you go DB first, you’re at the mercy of what gets generated for your models for use in your application. Occasionally the naming convention is undesirable. Sometimes the relationships and associations aren't quite what you want. Other times non transient relationships with lazy loading wreak havoc on your API responses.

While there is almost always a solution for model generation problems you might run into, going code first gives you complete and fine grained control from the get go. You can control every aspect of both your code models and your database design from the comfort of your business object. You can precisely specify relationships, constraints, and associations. You can simultaneously set property character limits and database column sizes. You can specify which related collections are to be eager loaded, or not be serialized at all. In short, you are responsible for more stuff but you’re in full control of your app design.

3)Database Version Control

This is a big one. Versioning databases is hard, but with code first and code first migrations, it’s much more effective. Because your database schema is fully based on your code models, by version controlling your source code you're helping to version your database. You’re responsible for controlling your context initialization which can help you do things like seed fixed business data. You’re also responsible for creating code first migrations.

When you first enable migrations, a configuration class and an initial migration are generated. The initial migration is your current schema or your baseline v1.0. From that point on you will add migrations which are timestamped and labeled with a descriptor to help with ordering of versions. When you call add-migration from the package manager, a new migration file will be generated containing everything that has changed in your code model automatically in both an UP() and DOWN() function. The UP function applies the changes to the database, the DOWN function removes those same changes in the event you want to rollback. What’s more, you can edit these migration files to add additional changes such as new views, indexes, stored procedures, and whatever else. They will become a true versioning system for your database schema.

Bhargav Rao
  • 41,091
  • 27
  • 112
  • 129
Said Roohullah Allem
  • 6,775
  • 8
  • 51
  • 97
31

Code-first appears to be the rising star. I had a quick look at Ruby on Rails, and their standard is code-first, with database migrations.

If you are building an MVC3 application, I believe Code first has the following advantages:

  • Easy attribute decoration - You can decorate fields with validation, require, etc.. attributes, it's quite awkward with EF modelling
  • No weird modelling errors - EF modelling often has weird errors, such as when you try to rename an association property, it needs to match the underlying meta-data - very inflexible.
  • Not awkward to merge - When using code version control tools such as mercurial, merging .edmx files is a pain. You're a programmer used to C#, and there you are merging a .edmx. Not so with code-first.
  • Contrast back to Code first and you have complete control without all the hidden complexities and unknowns to deal with.
  • I recommend you use the Package Manager command line tool, don't even use the graphical tools to add a new controller to scaffold views.
  • DB-Migrations - Then you can also Enable-Migrations. This is so powerful. You make changes to your model in code, and then the framework can keep track of schema changes, so you can seamlessly deploy upgrades, with schema versions automatically upgraded (and downgraded if required). (Not sure, but this probably does work with model-first too)

Update

The question also asks for a comparison of code-first to EDMX model/db-first. Code-first can be used for both of these approaches too:

Todd
  • 14,946
  • 6
  • 42
  • 56
  • 3
    Model-first isn't coding the POCO first, this is Code First, Model-First is a Visual Designer to automatic generate POCOs and after that generate databases from model. – Diego Mendes Feb 03 '16 at 01:51
  • These days in both the visual and code routes, you can do either "Model" first or "Database" first. The first is manual design (either through code or visual editor), the second is building a database and creating the model (either POCOs or EDMX). – Todd Feb 03 '16 at 06:05
12

I use EF database first in order to provide more flexibility and control over the database configuration.

EF code first and model first seemed cool at first, and provides database independence, however in doing this it does not allow you to specify what I consider very basic and common database configuration information. For example table indexes, security metadata, or have a primary key containing more than one column. I find I want to use these and other common database features and therefore have to do some database configuration directly anyway.

I find the default POCO classes generated during DB first are very clean, however lack the very useful data annotation attributes, or mappings to stored procedures. I used the T4 templates to overcome some of these limitations. T4 templates are awesome, especially when combined with your own metadata and partial classes.

Model first seems to have lots of potential, but is giving me lots of bugs during complex database schema refactoring. Not sure why.

user1618371
  • 131
  • 1
  • 4
  • 4
    You *can* define composite keys using code first - http://stackoverflow.com/questions/5466374/composite-key-with-ef-4-1-code-first – Jakub Konecki Sep 27 '12 at 06:09
  • 3
    For future readers, this is no longer the case, you can add Indexes, multi-column primary keys, and this kind of things in EF Code First. – tobiak777 Feb 23 '15 at 20:03
  • 1
    EF should have been fixed so that all 3 approaches could be used interchangeably on the same database as there are advantages and disadvantages for all 3 approaches – Dave May 04 '15 at 19:43
  • Additionally the truth of not ideal code first solution I'm using database first because of migration to other IDE/Language in the future and I want to have solid and integrate database structure, another fact I prefer database first is flexibility on changing any part of data storage. – QMaster Aug 13 '16 at 14:50
7

Working with large models were very slow before the SP1, (have not tried it after the SP1, but it is said that is a snap now).

I still Design my tables first, then an in-house built tool generates the POCOs for me, so it takes the burden of doing repetitive tasks for each poco object.

when you are using source control systems, you can easily follow the history of your POCOs, it is not that easy with designer generated code.

I have a base for my POCO, which makes a lot of things quite easy.

I have views for all of my tables, each base view brings basic info for my foreign keys and my view POCOs derive from my POCO classes, which is quite usefull again.

And finally I dont like designers.

hazimdikenli
  • 4,768
  • 7
  • 32
  • 64
  • 8
    'when you are using source control systems, you can easily follow the history of your POCOs, it is not that easy with designer generated code.' - I keep designer generated code in Source Control, so I can always view history. – Jakub Konecki Apr 01 '11 at 08:37
  • 1
    @JakubKonecki Did you ever try to merde EDMX files in a team of 3+ people? It is just a pain... Instead people try to avoid merging and just take the other revision and repeat their own changes, because merging is prone to fail in an auto generated file with thousands of lines of XML. – bytecode77 Dec 10 '17 at 17:36
6

Database first approach example:

Without writing any code: ASP.NET MVC / MVC3 Database First Approach / Database first

And I think it is better than other approaches because data loss is less with this approach.

AukI
  • 115
  • 1
  • 1
  • Could you elaborate on there being 'less data loss' with DB first approach? How would you perform data transformation if you were to split existing table into two? – Jakub Konecki Mar 10 '12 at 09:45
  • you would probably end up writing an sql script that takes care about the transformation. Generally, MS announced to improve the Code First data migration with their new version, so this might not be an argument in the future. – ckonig Jun 21 '12 at 06:49
  • The problem with database first is the database design generally has faulty abstractions that leak into your model ... junction tables, etc. The job of the database is simply to persist your model. – Nerdfest Aug 13 '14 at 13:34
  • 1
    This "answer" is an opinion based with no meat to your argument, one sentence doesn't make a stance. – TravisO Feb 27 '17 at 13:41
  • Could you elaborate on there being 'less data loss' with DB first approach? – amal50 Jun 01 '18 at 14:37
5

IMHO I think that all the models have a great place but the problem I have with the model first approach is in many large businesses with DBA's controlling the databases you do not get the flexibility of building applications without using database first approaches. I have worked on many projects and when it came to deployment they wanted full control.

So as much as I agree with all the possible variations Code First, Model First, Database first, you must consider the actual production environment. So if your system is going to be a large user base application with many users and DBA's running the show then you might consider the Database first option just my opinion.

  • 1
    You are right. MS gave programmers differents approaches because the re are different scenarios. You should know all and decide based on your scenario what is best for the project, and then what you like the most. – Sterling Diaz Mar 05 '18 at 22:32
3

I think one of the Advantages of code first is that you can back up all the changes you've made to a version control system like Git. Because all your tables and relationships are stored in what are essentially just classes, you can go back in time and see what the structure of your database was before.

  • 1
    That's a good point – Egli Becerra Aug 09 '20 at 09:24
  • 1
    Visual Studio Allows creation of a Database Project. With such a project you have full schema version control, can compare db schema and data and generate change scripts, apply schema or data changes to a db. See https://visualstudio.microsoft.com/vs/features/ssdt/ – Cogitator Jan 13 '21 at 19:36