Click here to Skip to main content
12,078,070 members (44,009 online)
Click here to Skip to main content
Add your own
alternative version

Stats

9.8K views
5 bookmarked
Posted

Using EF 4.1 Code First to support multiple database vendors

, 23 Mar 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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 th

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

JasonShort
Software Developer (Senior)
United States United States
I hold a PhD in computer science, and have been a practicing developer since the early 90's.

I used to be the owner for VistaDB, but sold the product to another company in August 2010.

I have recently moved to Redmond and now work for Microsoft. Any posts or articles are purely my own opinions, and not the opinions of my employer.

You may also be interested in...

Comments and Discussions

 
QuestionQuestion about VistaDB with EF Code-First Pin
Uldis Rāts27-Nov-13 10:57
memberUldis Rāts27-Nov-13 10:57 
QuestionRelease Date? Pin
AnnieMacD29-Mar-11 3:35
memberAnnieMacD29-Mar-11 3:35 
AnswerRe: Release Date? Pin
JasonShort4-Apr-11 13:13
memberJasonShort4-Apr-11 13:13 
GeneralRe: Release Date? Pin
AnnieMacD9-Apr-11 13:52
memberAnnieMacD9-Apr-11 13:52 
GeneralMy vote of 5 Pin
AnnieMacD29-Mar-11 3:26
memberAnnieMacD29-Mar-11 3:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.160212.1 | Last Updated 23 Mar 2011
Article Copyright 2011 by JasonShort
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid