Using EF 4.1 Code First to Support Multiple Database Vendors

The download for this article is no longer available (sorry).  This is kept for historical purposes.  You could recreate the databases pretty easily though, schema is simple.

• With the release of the Entity Framework 4.1, I was interested to see how well the code first aspect works with multiple database vendors. Could you use the code first modeling to change the database through a connection string? I thought it might be worth a quick sample app to find out.

About the Sample Databases

I have an identical database in both SQL CE 4 and VistaDB 4.1. Since they both have ADO.NET providers for the Entity Framework, I decided to use them for this test. The databases must have identical schema for this to work, otherwise your code wouldn’t match the database. The data in the databases is slightly different, this is from an RSS Reader app.

The schema is fairly simple, only two tables. RssFeeds (list of feeds the user wants to get entries from), and RssEntries (the actual RSS entries from the feed). The schema is simple enough to be used for demo, but has real world data that always makes a demo much nicer.

• Rss.sdf – The SQL CE 4 version of the database
• Rss.vdb4 – The VistaDB 4 version of the database

Create a Connection String that Matches Your Context Class

There are a number of naming conventions you have to follow if you want the EF Code First to work without change against your POCO classes. Some of them are pretty straightforward, but some of them are just things I don’t normally do. Your connection string class needs to have the same name as your database context class (not something I normally do).

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="RssDatabaseCE" connectionString="data source=|DataDirectory|\RSS.sdf" 
             providerName="System.Data.SqlServerCe.4.0" />
    <add name="RssDatabase" connectionString="data source=|DataDirectory|\RSS.vdb4" 
             providerName="System.Data.VistaDB" />
  </connectionStrings>
</configuration>

I have two connection strings, one for the VistaDB database, and one for SqlCE. You can run the EXE multiple times and just change the connection you want to use to the RssDatabase connection and the code will work! That is pretty amazing. You literally can’t do this with an EF model, but you can with Code First EF!

Build the POCO Code Classes

The database is pretty simple, so here are the POCO classes to represent the tables. You do have to add a using statement for the System.ComponentModel.DataAnnotations namespace in order to add the [Key] annotation on the class. You would not have to do this if the key was named RssEntryID.

public class RssEntry
{
    [Key]
    public long RssEntryKey { get; set; }
    public long RssFeedKey { get; set; }
    public string EntryID { get; set; }
    public DateTime CreatedOn { get; set; }
    public bool IsNew { get; set; }
    public string Title { get; set; }
    public string Url { get; set; }
    public DateTime ModifiedOn  { get; set; }
    public DateTime PublishedOn { get; set; }
    public string Author { get; set; }
    public string Email { get; set; }
    public string RawSummary { get; set; }
    public string TextSummary { get; set; }

    // Each entry knows about it's parent feed and can return that object
    public virtual RssFeed Feed { get; set; }
}

public class RssFeed
{
    [Key]
    public long RssFeedKey { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedOn  { get; set; }
    public string Url { get; set; }
    public int RefreshMilliseconds { get; set; }
    public bool IsStartup { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public bool RequiresAuth { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public string AltUrl { get; set; }
    public DateTime? ModifiedOn { get; set; }
    public string Language  { get; set; }
    public string Copyright { get; set; }

    // Each feed knows about all of the entries under it, and can return them as a collection
    public virtual ICollection<RssEntry> Entries { get; set; }
}

Notice the public virtual methods to extend how the class works as an entity. The RssFeed exposes a collection of RssEntry objects (one feed can have many entries). But the RssEntry only exposes a single RssFeed object (one entry can only have one parent feed). Both of these are very common database concepts, but are subtle to get a code only solution to work. I think the EF team has come up with a very elegant way to solve the problem by using the virtual methods.

Build the Database Context

The naming of the class has to match what you expect Code First to find as a connection string. There is a good blog post about the EF naming conventions. Again, you may or may not want to follow these, but there is a way to override the behavior if you don’t like the default.

public class RssDatabase : DbContext
{
    public DbSet<RssFeed> Feeds { get; set; }

    public DbSet<RssEntry> Entries { get; set; }
}

Notice that the collections exposed off the RssDatabase are DbSet<T>. The naming does not have to match the names of the tables, at this stage, we are just exposing a collection from an object.

Consume the EF Code First Classes

Now, to the easy part – consume the classes. The code here will look very much like a traditional EF Model consumer. This code doesn’t know that it was loaded from a code first project. There are some differences, but for most simple cases, it won’t matter.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CodeFirstSample
{
    class Program
    {
        static void Main(string[] args)
        {
            using (RssDatabase db = new RssDatabase())
            {
                var activeFeeds = from f in db.Feeds
                            where f.IsActive == true
                            select f;

                foreach (var feed in activeFeeds)
                {
                    Console.WriteLine("Feed: {0}", feed.Title);

                    foreach (RssEntry entry in feed.Entries)
                    {
                        Console.WriteLine("[{0}]:{1}", entry.EntryID, entry.Title);
                    }
                }
            }
        }
    }
}

Now the same code will work against SQL CE 4, and VistaDB 4.1. That is way easier than maintaining two models, or editing one model for the other provider, etc.

Ok, What Are the Drawbacks?

Yes, there are always pros and cons with any solution. The one that I see with EF Code First is when the database schema changes, there is no way to have your app work with a newer database schema. There is a good blog post on the concepts and current thoughts around this problem.

This technique will not work unless your .NET types match up correctly across database vendors. So if you have a model with SqlGeometry present, it will not work with any other database! The provider factory needs to be able to map those .NET types to the underlying provider type. If it can’t perform the mapping, you will get an error.

Summary

This is pretty impressive to me as a database vendor developer. The EF has always had the promise of allowing a data model to conceptualize the database independent of the underlying provider. The EF Code First is the first implementation I have seen that actually allows you to have zero knowledge in your app about the database. The database provider factory is totally loaded from ADO.NET (as it should be). You don’t need to add any references in your project at all!

Good Job, EF Team!

More Information about Code First EF

• EF Code First Walkthrough – Good overview of the entire topic
• EF Fluent API Samples – More advanced options and abilities are explained
• EF Code First Database Conventions – This is the best source of information I have found on the naming conventions for how EF Code First works
• EF Code First Database Migration

Download Links to Things You Need

Most of the downloads in this article are no longer available.

Getting Entity Framework 4.1

Get NuGet from here (package manager to add packages to your projects directly within Visual Studio 2010)

View the console using View –> Other Windows –> Package Manager Console

Make sure you select your project for the code first model generation before running the following command.

Type install-package EntityFramework, you should end up with a message saying you have the following package:

EntityFramework 4.1.10311.0

(Or higher should also work)

This will add a reference inside your project as well.