It is often difficult to decide where to start when building a new application, but for TicketDesk 3 the obvious first step is to create the database and entity model.
Until now, the TicketDesk database has followed an unbroken evolution from the original TD1 database to TicketDesk 2.0.3. I built the original database by hand, then used it to generate a Linq to SQL model diagram. When I put TicketDesk 2 together, I just switched to EF 4 entity model diagrams; again, building the model from the existing database.
For TicketDesk 3, I’ll once again switch data access technologies, this time using EF 5’s DbContext with a code-first model (POCO). I could continue with *.edmx models, and generate the DbContext and POCO classes from the diagram, but Microsoft seems to be moving away from *.edmx, and I strongly prefer the code-first approach anyway.
Initially, I want to start TicketDesk 3 with a model as similar to the TicketDesk 2 database schema as possible. Due to the way EF generates T-SQL, there will be cosmetic differences, but I don’t want any significance structural variations –not yet.
To create the initial POCO classes, I was able to leverage the old entity model diagrams. The tooling in VS 2012 allowed me to open the old *.edmx files and add the “EF 5.x DbContext Generator”. This creates T4 templates, which in turn automatically produce generated DbContext and POCO classes based on the diagram. All I had to do was pluck out the generated class files and drop them into the TicketDesk 3 project.
Interestingly, the POCOs created by the generator include the model properties, but not the annotation attributes that identify key fields, required fields, and so on. Normally I’d have to annotate them all by hand, but I found a shortcut for this too. TicketDesk 2 originally used another T4 template, called AutoMetaData, which produces meta classes for all its entities; and those meta classes contain the data annotation attributes. The old EntityContext stuff doesn’t need those annotations, but generating them was useful to enable UI validations in the MVC front-end. So, all I had to do was copy the attributes from the TD2 meta classes, and drop them on the corresponding properties in the new TD3 model classes. Tedious, but easy.
TD 2 also included custom extensions (via partial classes) for a few entities. Mostly these just had a few non-persistent helper properties, and a custom method to two. I simply copied these into new POCOs too, and then made sure they were annotated correctly (adding the NotMapped attribute to most).
By leveraging the work I’d already done in TD 2, I was able to create a nearly complete EF code-first model for TD 3 without writing any code by hand. Sweet!
The last step was to test DB generation from the new model and DbContext.
My web project was created from the stock MVC SPA template that shipped with the “ASP.NET and Web Tools 2012.2 update“. I setup a new localdb connection string in web.config, configured EF to use the DropCreateDatabaseAlways initializer, and then called the Database.Initialize() method on my new DbContext during the application_startup event. Running the web application triggered the database creation without any problems.
I then used the schema compare tool in VS 2012 to compare the new DB against an unmodified copy of the TD2 database. There were a few differences, which I had expected, but structurally it was the same DB that TicketDesk 2 used. The only significant variation was the lack of default constraints in the new database. EF 5 doesn’t support default constraint definitions via attributes, nor the fluent model builder APIs. Fortunately, EF migrations can handle defaults, so I will solve that problem later on.
The stock TD2 database also included SQL membership, role, and profile tables, which are not a part of the new entity model. I’ll be addressing the security system pretty soon, and then I can decide how best to handle the security DB objects.
And that is pretty much it for the initial TD 3 code-first entity model. As I develop TD 3, I will likely make changes, but I can rely on EF to manage the database schema going forward. I’ll need to setup EF migrations, deal with seed data, and add upgrade support for legacy TD2 databases… but those are topics for future posts.