0

Recently there was a hot discussion on the Greek .NET Community over the subject of automatically generating a database from an Entity Framework model. It actually started when my good friend and SQL Server MVP and guru, Antonis Chatzipavlis (twitter, sqlschool.gr) came across such a database. It was based on a poorly configured EF model, so that actual database was not indexed, was full of nvarchar(MAX) fields and other kind of things that database experts see and want to... migrate to another universe.

Code First is an excellent pattern for designing our solution's data access layer. It helps us developers continue thinking in object oriented way.

So, I was wondering what is the way we can implement code first solutions that result in databases that follow good db design patterns and are acceptable by database experts?

papadi
  • 985
  • 2
  • 9
  • 22
  • If you're going to self answer a question, the question portion should actually be *phrased* as a question. There's no question here, and the answer seems like it could be opinion-based anyways. – LittleBobbyTables - Au Revoir Dec 08 '14 at 14:32
  • I modified the title to be phrased as a question. I don't understand what you mean by 'opinion-based'. Is it against the rules to express opinions in stackoverflow? – papadi Dec 08 '14 at 14:35
  • Yes, per the close reasons: "Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise." Additionally, not just the title should be updated, but the *entire question body*. As it stands, there is no question in the question body, and it almost reads as an advertisement/endorsement. – LittleBobbyTables - Au Revoir Dec 08 '14 at 14:37
  • I will change the body to be expressed as a question but other than that I really see useful content here. With references and good practices. Specific ones. I still don't understand the part about 'opinion-based' answers. – papadi Dec 08 '14 at 14:56

1 Answers1

0

Generating the database

There are a number of ways we can generate our database after working on our Code First EF model.

  1. There is nothing preventing us from writing our own DDL scripts! Actually I used to do that with versions of EF prior to 6, partly because database generation on previous versions wasn't as powerful and partly because I wasn't so familiar with it.
  2. EF provides a fully automated way to create the database. When our model is about to be used in runtime, EF will check if there is a database, if not will create it, using only the information provided by the code based model (classes correspond to tables, properties correspond to columns etc). In this case, as developers of the EF Model, we need to make good use of the data annotations, in order to avoid creating an inefficient database. If not then we will really get nvarchar(max) for all string properties etc. EF team made a great job to help use quickly generate databases without hassle, but this does not mean that these databases are ready for production systems. We need to make good use of annotations such as Index, Required, MaxLength etc in order to make a database that really makes sense.
  3. My favorite way, though, of creating my database from my EF Code First model is by enabling non-automated migrations in my data access project. By executing the Enable-Migrations command in your nuget console, you will get a file called NNNNNNN_InitialCreate.cs (where NNNNNN is a timestamp). Based on your model and the annotations on it, EF generates some code that will create your database. This code file is made available to you to modify and enhance in any way you want. For instance, I personally don't like the default foreign key names created by EF, so I tend to write them myself. This method provides me with the flexibility of creating my database the way I want. It also provides me with the proper level of encapsulation. My model is located very close to the code that generates my database, so it is easier to maintain. Also I can create modules that work independently from each other and each one of them has its own model and database schema (eg. logging tables, authorization etc).

Deploying the database

Even though Visual Studio provides the tools to upload our solution, database included, into Azure, the truth is that in many cases, as developers, we will not have access to our production database or its host server. Also the tools used by database administrators are quite different that ours. EF provides two excellent, database admin friendly, ways for deploying our code first databases.

  1. One way is to use the migrate.exe command line tool. It is located in the tools sub folder of packages folder of Visual Studio and we can distribute it with our application. This tool accepts as parameter the dll file that contains our code first model and database generation code. No sql files are used in this process.
  2. If your database admin, however, needs to see the actual SQL scripts that this tool will be executing (as a developer I really don't understand why, but...) you can always generate it.

Starting from version 6, Entity Framework provides an excellent set of tools for creating code first solutions that will not let your database people down!

Community
  • 1
  • 1
papadi
  • 985
  • 2
  • 9
  • 22