Using Entity Framework With An Existing SQL Server Database (Code Second)

Entity Framework originally shipped with two modes – Model First which allowed for the entity model to be created using the designer UI and Database First which created the EF model from a database.

EF 4.1 introduced Code-First development, enabling developers to simply generate the EF model and database directly from code. This method rapidly became the most popular method for using EF, since it is relatively unobtrusive and developers can simply write POCOs (Plain Old CLR Classes) and have these seemlessly persisted to SQL Server. Code-First did not however reverse engineer POCOs from a database schema.

The workaround was to use Database First to generate the edmx files and then generate the POCOs from the edmx files. With Entity Framework Power Tools it is possible to reverse engineer a POCO model from an existing database (sometimes called ‘Code Second’). Simply install EF Power Tools (download here).

Prior to generating the POCOs you may wish to customize the code generation. This step is best skipped if this is the first time you are working with EF for reverse engineering code. To customize the code creation, open the project you wish to use with EF, right click on the project name and select Entity Framework > Customize Reverse Engineer Templates:

This option does not perform any mode generation, but adds the T4 templates which are used for the model generation to you project. The templates are added in the Code Templates > ReverseEngineerCodeFirst folder. There are three T4 template, one for customizing the mappings, one for the context and one for the entities themselves. The mostly likely candidate for customization is the Entity.tt file for generating the entities, the Context.tt will just generate the context and this will be easy to edit it after creation, if the mappings need editing it will probably be on a case-by-case basis and so post generation editing would be fine.

In the Entity.tt file you can, for example, edit the constructors for the entities – by default the generator adds code to initialize all ICollection objects to new Lists to avoid run-time errors when attempting to access a list with no added entities, but you may wish to modify this or add custom code in the constructor:

One thing I like to do is remove virtual from all properties in an entity. This will prevent lazy loading (see Entity Framework Gotchas for some of the issues with lazy loading) and set change tracking to Snapshot Change Tracking which is more straightforward to work with.

Next, to generate the POCOs. Right-click on the project and select Entity Framework > Reverse Engineer Code First:

Then simply select the server and database from which to generate the classes using the familiar connection / database dialog:

Click OK to start the model generator and the model classes should now appear in your project in the Models folder:

The POCOs will each be created in a separate file, in addition each entity will have an associated mapping information file in the Mappings folder describing how the properties in the entity will relate to eachother and to other entities in the database. When EF creates the database the mappings files will be used to create the schema, if no mappings files exist then EF can still create the database but only using its default object mappings. Note that the mapping files are not required to use EF code first (and you could avoid them being generated by deleting the Mapping.tt file prior to running the model generation but they are very useful to have as they will provide a great starting point for customizing the model later).

The context class itself may require some attention. The context class inherits from the EF DbContext class and all access to the database will be through a method exposed by this class or one of its DBSet properties (a DBSet corresponds to a table in the database).

By default the context class will be given the name of the database with ‘Context’ appended – ie [yourDBName]Context.cs . The first thing to note in the context class is the Initializer. By default the initializer is set to null meaning that EF will not automatically recreate or modify your database in the event of a change in the model:

        static yourDBNameContext()
        {
            Database.SetInitializer<yourDBNameContext>(null);
        }
        

This is a very normal setup since EF Migrations does an excellent job of handling the updates to the database. However, for rapid development at the start of a project it is very efficient to have EF automatically drop and recreate the database without generating un-necessary migration files, if so you can choose to set this to DropCreateDatabaseIfModelChanges as below. There are several other less useful initialization options such as DropCreateDatabaseAlways which drops and recreates the database everytime the app is loaded.

Whether you are using EF Migrations or DropCreateDatabaseIfModelChanges you may want to consider a Seed method. The seed method runs when the database is created or updated to add some data which may be required for development and testing.

        protected override void Seed(yourDBNameContext db)
        {
            //Hard code data to be saved to database on db creation
            db.Employees.AddOrUpdate(
              x => x.FullName,
              new Employee { FullName = "Jude OKelly" },
              new Employee { FullName = "Diego Maradona" }
            );
        }

Note above the use of the AddOrUpdate method which checks if the record exists previous to adding it.

In the Context class also note the call to the base constructor which passes in the connection string:

public yourDBNameContext()
: base("Name=yourDBNameContext")
{
}

There will be an entry in the <connectionStrings> node web.config for the actual string.

That’s all that is required. Overall it is a very simple process, and even with a bit of customization and testing it should take less than half hour to have a fully functioning EF model.




Related Articles :

One Response to “Using Entity Framework With An Existing SQL Server Database (Code Second)”

  1. I see your table names appear to have been kept. I can not get mine to do that. In your example, the AccountModels class would have become AccountModel (no s) With mine, SystemStatus table becomes SystemStatu. This happens in the class name, file name and where ever refrenced (DBContext, other models for FK’s) Do you know how to prevent this?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |