Using Entity Framework With Legacy Databases

I find more people are interested in getting started with Entity Framework within their organizations. However, the various documentation and examples that exist don't necessarily give the best overall implementation solutions. I have worked with many people that have thought "we have a legacy database setup, we can't use entity framework" or "we cannot use migrations, so we can't use code first." Both are very common misconceptions, but something that isn't the case. It just takes a bit of work. In this post, we will dive into the specifics of implementing Entity Framework to a legacy database, where we cannot rename tables and other objects without substantial effort. A link to full code will be provided at the bottom.

The Database

Our sample database as you can see follows many of the "legacy" patterns, including the use of Hungarian notation within table names. In our Entity Framework code, we want to make sure that we remove this notation to make our code easy to understand.

The Setup

In this situation, we have a legacy database. We don't want to use Entity Framework to manage initialization or migrations, but we want to be able to work with this database like any other. In my sample code, provided below, we use two separate projects, one is a standard DataTier setup and the second is a consumer of the data tier. Initial setup from the creation of a new project just consisted of calling "install-package entityframework" from the package manager console. From there, we will walk through all of the code.

The Models

The first step of the process is to create our models. In our example, we want to standardize on more common names. Given that we are not working with migrations or a database initializer we simply have to be sure that the names/columns that we specify exist in the database. Any discrepancy will result in a runtime error.

Customer Model

[Table("tblCustomer")]
public class Customer
{
    [Key]
    [Column("pkCustId")]
    public int CustomerId { get; set; }
    [Column("strCustName")]
    public string CustomerName { get; set; }
    [Column("strCustEmail")]
    public string CustomerEmail { get; set; }
    [Column("dteBirthdate")]
    public DateTime Birthdate { get; set; }

    public virtual ICollection<customeraddress> Addresses { get; set; }
}

This object is pretty similar to that of the examples you might see. However, a few distinct differences are apparent. The first is the use of the Table attribute. With this attribute, we can override the Entity Framework default table name with our own. We then repeat this process with our columns and the Column attribute.

Customer Address Model

[Table("tblCustomerAddresses")]
public class CustomerAddress
{
    [Key]
    [Column("pkCAddId")]
    public int CustomerAddressId { get; set; }
    [Column("fkCustId")]
    public int CustomerId { get; set; }
    [Column("strCAddStreet")]
    public string Street { get; set; }
    [Column("strCAddCity")]
    public string City { get; set; }
    [Column("strCAddState")]
    public string State { get; set; }
    [Column("strCAddPostalCode")]
    public string PostalCode { get; set; }

    public virtual Customer Customer { get; set; }
}

This object was created very similar to that of the Customer model. The key point to note is that we did not do anything special for our foreign key relationship.

The DBContext

Our DBContext code for this example is also quite small. The key provision that we must meet here is that our DBContext should not perform initialization. The code sample below shows the detail of our context.

public class LegacyDemoDbContext : DbContext
{
    public LegacyDemoDbContext() : base("DemoConnection")
    {
        //Disable database initialization
        Database.SetInitializer(new NullDatabaseInitializer());
    }

    public DbSet<customer> Customers { get; set; }
    public DbSet<customeraddress> Addresses { get; set; }
}

The key difference here is the usage of the NullDatabaseInitializer. This removes all EntityFramework expectations around the data model. Allowing us to create an EF model that represents a subset of the database without issues.

Querying

Just to prove that we can now query against the database two simple queries are included in the sample. Specifics of entity framework queries are beyond the scope of this post, but I can work on some details if desired.

Conclusion

Just because you are stuck with an older data model, doesn't mean that you can't use newer technology. With a little bit of work, you can get there quite quickly and work to introduce modern development techniques to your daily process. You can see the full sample project, including test database, on GitHub.