Code-first vs Model/Database-first

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?

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.

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

More info Here.

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.

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.

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:

• Model-First: Coding the POCOs first (the conceptual model) then generating the database (migrations); OR
• Database-First: Given an existing database, manually coding the POCOs to match. (The difference here being that the POCOs are not automatically generated give then existing database). You can get close to automatic however using https://stackoverflow.com/questions/8315596/generate-poco-classes-and-the-mapping-for-an-existing-database-using-entity-fram or https://stackoverflow.com/questions/13327235/entity-framework-5-how-to-generate-poco-classes-from-existing-database.

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.

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.

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.

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.

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.