March22

Using EF 4.1 Code First to support multiple database vendors

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.

Download the sample Project

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 enties 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.

How to use the sample

Run the sample and look at the output, then change the config connection string (rename the existing one) and run the sample again. 

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

This is the order in which I installed the various things you need, but you may be able to install them differently.  Just make sure you have all the components before trying the sample.

Download the sample Project – Visual Studio 2010 solution with two sample databases

SQL Server Compact Edition 4.0

Visual Studio 2010 SP1

SQL Server Compact 4 Tools for Visual Studio 2010 SP1  (downloads using Microsoft Web Platform Installer)

Entity Framework 4.1 (don’t follow anything you read about getting the CTP, it is now an RC as I write this)

VistaDB 4.1

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.

Comments (3) -

22/03/2011 03:55 #

David McCallum

Jason

A complete beginner question, where do I tell the program which connection string to use?

David McCallum

23/03/2011 06:40 #

James


I think the title should be multiple database versions from the same vendors. I really don&amp;#39;t see this working to well when trying to mix in mysql / oracle / pgsql / mssql / access / etc....

James

26/03/2011 08:56 #

jshort

You set the connections strings in your app.config.  Which just to be very confusing is renamed for you by the compiler to be your application.exe.config in the runtime folder.

@James - It really has nothing to do with the same vendor.  This sample shows VistaDB and SQL CE - NOT the same company.  Now the types are comparable, but that is up to the vendor in how they map a type to the .Net type.  It could work with every database you mentioned if all of them implemented an EF provider, and all understand these types.  Since I only used simple types it might actualy work.  Access though won&amp;#39;t because there is no EF provider.

jshort

Pingbacks and trackbacks (2)+

Comments are closed