.NET'ers, Entity Framework DB first vs Code first?

[Durbs]

I've got a conundrum, my new pet project is to write an e-book for relative newbies documenting the process used to re-write my largest plan B web app from the ground up using Microsoft's latest and greatest technologies.

This app is very well suited to a book as the rewrite will use most of the things you'd come across in any .NET web project including MVC/EF/server-side Windows scheduling, communication with various mobile platforms etc etc.

I'm planning to write the book as a cross platform HTML5 app, a bit like the old Fighting Fantasy series where you can make choices based on your preference and the tutorial proceeds accordingly e.g. section on integrating the project with source control, gives pro's and cons of SVN vs TFS, if you decide TFS the rest of the chapter is setting up TFS Express 2012 and creating the project within it.

My first major branch though is not going to be do-able, this is the decision on Entity Framework Database First/Code First/Model First. I'm not going to produce separate code based on this decision!

I've read endless debates during EF's life on which is best for what scenario but basically, they way I see it, there's no particular reason not to use any of these for any given app, so the question "what kind of app is it" is pretty negligible IMO.

I'm generally a Database-first man, but that's just where I'm comfortable, I don't choose it because there's anything in Code-first that's stopping me going that route.

So all I can do is go for whatever is currently considered 'shinier' and en-vogue, what do the congregation reckon is the most universally accepted choice?

Ta

[Gentile]

I generally build the database schema first, whatever type of app I'm building. The schema evolves further as the design and build process reveals more about the problem being solved of course, but it all starts with a reasonably-complete schema that caters for 80-90% of the design requirements in the first instance.

I've only heard of one dev team attempting to use a code-first approach, and at the interview for that job I stopped listening at "I don't care what the database looks like, for me the database is just a bin for data..." Somehow I felt that developer had missed the point, and that sort of approach for a transactional database would only ever work in a development environment with limited amounts of data, if indeed it worked at all. If you want to do anything with the data applications collect, how you store and manipulate it needs to be considered as a primary design goal. You only know what information you need to put in, and how it needs to be stored and optimised, by considering what it is that you want to get out of the process at the end of the day.

I'm not sure what you're asking when you say "all I can do is go for whatever is currently considered 'shinier' and en-vogue, what do the congregation reckon is the most universally accepted choice?" Do you mean, what is newer and shinier than EF? In my opinion, nothing in the ORM field is at present. All I would say is that EF, or for that matter any ORM tool, is usually the correct choice for most designs, except in those situations where you're working with bulk manipulation of data (e.g., uploading many thousands of records at a time, selectively manipulating certain of those records, deleting others, etc). In those sort of scenarios, any ORM tool tends to grind to a halt, as they generate row-by-row SQL CRUD operations instead of performing the same updates in more practical and efficient ways.

It's always much more efficient in the sort of bulk update scenarios described above to use XML, either in conjunction with SQL Server stored procedures using its XML data type, or in isolation outside any RDBMS working with .Net's LINQ to XML functionality in conjunction with the XDocument .Net data type. e.g., I had a task this week where I had to compare two lists of ordered data, stored as flat text files, with SQL Server being unavailable. One of the files contained a quarter of a million rows, the other approx 10,000 rows that represented primary keys to be deleted from the first file. LINQ to XML was how I achieved a solution that performed that type of operation in less than 2 seconds. Using EF or any other ORM tool in that scenario would have been painfully slow and cumbersome, if it were even possible at all to populate entities from text files (it probably is possible, but not advisable, to populate EF objects from text files; it's not something I have or would attempt to do).

Some other topics you may want to consider in this chapter of your book include so-called "Big Data" or "NoSQL" approaches to data storage, using tools such as Membase and CouchDb. These hark back to the sort of approach suggested by the "to me it's just a data bin" developer mentioned above. These can be useful for the sort of tasks where you want to store and retrieve individual records from amongst a large amount of similar data (e.g., information about individual purchases on Amazon, the massive amounts of individually-simplistic data collected for weather forecasting, data about the millions of Google searches carried out each hour, etc), but where bulk analysis isn't a primary design consideration. You may also want to mention the new Dynamic Language Runtime features of .Net, that allow data types to be defined at Runtime in .Net in the same way that Ruby, Python and JavaScript have been able to do for years. This capability can be useful for modelling some types of highly-fluid data.

I hope the above helps. If not, by all means ask any specific questions you may have.

[Durbs]

I'm not sure what you're asking when you say "all I can do is go for whatever is currently considered 'shinier' and en-vogue, what do the congregation reckon is the most universally accepted choice?" Do you mean, what is newer and shinier than EF?

I mean, using EF, what method is the flavour of the month: code first, model first or database first, any of those will do the job but its not as black and white as C# vs VB, MVC vs Web Forms as to what the bulk of users see as the 'best' method at the moment.

From your post, its easy to see your preferred method, which is the same as mine

Googling around, it appears that code first is the flavour of the month now so I'm wondering whether I should use that approach to get the benefit of the tutorial using what the bulk of its target audience would choose? So basically asking whether everyone else agrees that code first is becoming the the 'C#' of the 3 available options?

The general consensus seems to be that displayed on the flow chart at: Model First, Database First or Code First–Update to Data Points Column : Don't Be Iffy
but I very much disagree with the whole existing database = database first, anything else = model/code-first. Database first is still valid in any greenfield build IMO, for reasons you outlined. I'd personally get rid of model-first given a choice, I either want to get my hands dirty in SQL or hands dirty in code, model-first has too much abstraction from either.

So basically, what option do you reckon would have less punters moaning "why didn't you use x-first"?

[eek]

The general consensus seems to be that displayed on the flow chart at: Model First, Database First or Code First–Update to Data Points Column : Don't Be Iffy
but I very much disagree with the whole existing database = database first, anything else = model/code-first. Database first is still valid in any greenfield build IMO, for reasons you outlined. I'd personally get rid of model-first given a choice, I either want to get my hands dirty in SQL or hands dirty in code, model-first has too much abstraction from either.

So basically, what option do you reckon would have less punters moaning "why didn't you use x-first"?

I would disagree with consensus as well. Building a model takes time, creating a few database tables takes at most minutes. Its probably worth saying that we've all spent x years doing things this way so we are probably biased (especially as I use tools to generate the data and basic business layers from those tables).

I will admit to not liking entity or to be frank all orm systems. I've never found one that allows you to quickly add a customisation or two without it becoming a world of pain.

[Gentile]

I mean, using EF, what method is the flavour of the month: code first, model first or database first, any of those will do the job but its not as black and white as C# vs VB, MVC vs Web Forms as to what the bulk of users see as the 'best' method at the moment.

From your post, its easy to see your preferred method, which is the same as mine

Googling around, it appears that code first is the flavour of the month now so I'm wondering whether I should use that approach to get the benefit of the tutorial using what the bulk of its target audience would choose? So basically asking whether everyone else agrees that code first is becoming the the 'C#' of the 3 available options?

The general consensus seems to be that displayed on the flow chart at: Model First, Database First or Code First–Update to Data Points Column : Don't Be Iffy
but I very much disagree with the whole existing database = database first, anything else = model/code-first. Database first is still valid in any greenfield build IMO, for reasons you outlined. I'd personally get rid of model-first given a choice, I either want to get my hands dirty in SQL or hands dirty in code, model-first has too much abstraction from either.

So basically, what option do you reckon would have less punters moaning "why didn't you use x-first"?

I see. I haven't seen anyone other than that one dev team from a few years back trying to make a Code-First approach work. And even with them, when I stated my own views at the interview they immediately started arguing amongst themselves. Most of them were seemingly in agreement that a Database-First approach was actually the right way to go. It turned out it was really just one very loud voice amongst that particular team that wanted to use a code-first approach, and the root of his bias seemed to be that he didn't appear to know much about database design.

IMHO, like TDD and Agile before it, if Code-First does become popular it'll merely be the latest incarnation of "The Emperor's New Code" phenomenon. i.e., something that people do purely because it's populist, rather than because they've considered the options and made an informed decision that it makes sense in a particular scenario. Moreover, again like TDD and Agile, I suspect that the "Code-First" label will be something that lots of teams approach in radically different ways. However, because they'll all be using the same buzzwords to label their vastly-differing approaches they'll mistakenly believe themselves to be doing the same thing as one another.

[woohoo]

It's pretty interesting reading some of the comments, I much prefer modeling the application in code and then creating the database. However, I've found that most teams I encounter take the database first approach. It's worth saying that I'm pretty ok with SQL Server so I don't entirely farm out the thinking to EF. I would like to hear more about peoples experiences of both approaches and comparisons.

Anyway back to the point, I've used code first and database first approaches with EF. I like the code first approach, design my model in code, let EF generate the DB and if I'm inclined use migrations to update the database with any changes. It also means that I don't have to worry about the EDMX, which I've found to be a pain, especially when merging changes. So in my opinion code first would be the way to go.

It's worth saying that a company that I've contracted at are moving away from EF and have started using PetaPoco with stored procs. The reasons for this are problems with the sql generated by EF and merging issues with the EDMX.

[garethevans1986]

Use NHibernate :-P