Using ADO.NET Entity Framework 4.1 with SQL Server Compact 4.0

WTF and Why?!!

ADO.NET Entity Framework, an object-relational mapper (ORM) built into the .NET framework, provides an easy way to map your classes into a database tables and vice versa.

I've been working with with Entity Framework CTP 4, and have now upgraded my project to use the final released version 4.1. I'll provide here a small proof-of concept which I've built in order to chart the important bits and pieces.

Article level: Not quite rocket science

SQL Server Compact (CE)

Yet another useful small scale tool. Of course you can map your entities into a full robust database. There are a number of pre-built providers, not counting Microsoft SQL and SQL Express (the default). I've chosen to use MS SQL Server Compact as a first option (later to be replaced or give the user/administrator the option to set a different provider) mainly because it's super-lightweight, builds the database in a file, and does not require heavy installation and licenses. It's free and is supported by mobile devices. For small applications it may be sufficient. For larger scale apps perhaps not so much.

Prerequisites checklist

• ADO.NET Entity Framework 4.1, available here.
• Microsoft SQL Server Compact 4.0, available here.
• MS SQL Server CE has no fancy management application (like MS SQL Server Management Studio for more robust SQL Server versions) and it is not natively browsable from Visual Studio.
  However, in order to query your tables and see what's going on, I recommend using SQL Server Compact Toolbox Visual Studio extension, available here.
  Mind you that in order for it to run properly, you will need to have both SQL Server CE 4.0 (from the link above) and the SQL Server CE 3.5 SP2 runtime from here installed on your machine.
  If you are running a 64 bit version of Windows, you will have to download, extract and install both the 32 bit and the 64 bit versions of the SQL Server CE 3.5 SP2.

Project references

Once all prerequisites are in place, the following assembly references are to be added to the project:

• EntityFramework
  Should reside in the folder where the Microsoft ADO.NET Entity Framework 4.1 has been installed. By default it would be something like:
  C:\Program Files (x86)\Microsoft ADO.NET Entity Framework 4.1\Binaries\
• System.ComponentModel.DataAnnotations
  This is required in order to use data attributes for annotating your persisted class properties, such as [Key] to denote a primary key property.

Code first: Data classes

The approach I'm implementing here assumes you have the data classes created first, and wish to map those into database tables. There are ways to implement in the opposite direction, meaning you have your database and wish to create corresponding classes ("Model first"). I'll use a simple structure of a Person class and Company class.

The simpler of the two is the company class, which consists of 2 properties: an ID and a name.

public class Company
{
    public Company()
    {
    
    }
    
    public Company(Guid companyId, string companyName)
    {
        this.CompanyId = companyId;
        this.CompanyName = companyName;
    }
    
    [Key, Column(Order =1)]
    public Guid CompanyId { get; set; }
    
    [Key, Column(Order = 2)]
    public string CompanyName { get; set; }
}    

A few points to note, though:

• Notice it has an empty default constructor with no arguments. Even if this constructor does nothing, it is required for reconstructing the object once it's fetched back from the database.
• For this example, I've defined both public properties, CompanyId and CompanyName as parts of a compound primary key. It is also necessary to order the key parts as demonstrated here.

For this demo, I've also included a simple enum. Enums were quite clumsy to use in previous CTP versions of the Entity Framework, but as of version 4.x they are supported more easily.

Hence the following enum is also defined:

public enum Prefix 
{ 
    Mr,
    Ms,
    Mrs,
    Dr
}

The Person class:

public class Person
{
    public Person()
    {
    
    }
    
    public Person(Guid personId, string name, int age, Prefix pref, Company workplace)
    {
        this.PersonId = personId;
        this.Name = name;
        this.Age = age;
        this.NamePrefix = pref;
        this.Workplace = workplace;
    }
    
    [Key]
    public Guid PersonId { get; set; }
    
    public string Name { get; set; }
    
    public int Age { get; set; }
    
    public Company Workplace { get; set; }
    
    [Required]
    public virtual Prefix NamePrefix { get; set; }
    
    public virtual int NamePrefixId
    {
        get { return (int)NamePrefix; }
        set { NamePrefix = (Prefix)value; }
    }
}

• A more complex class. It has a member of type Company.
• It has 2 member properties: NamePrefix and NamePrefixId, which relate to the enum of type Prefix. The NamePrefixId member is a helper for translating the enum to/from int, which is the actual underlying type which will be stored in the database.
• It also has an empty default non-argumentative constructor for the sake of reconstructing the retrieved object.

Database context

This class is required as a management context for our persisted objects against the framework. It inherits DbContext and its properties, which are of the generic DbSet type represent the queryable collections of the persisted objects.

public class PersonsContext : DbContext
{
    public PersonsContext()
        : base(PersonsContext.ConnectionString)
    {
    }
    
    public DbSet<Person> Persons { get; set; }
    public DbSet<Company> Companies { get; set; }
    
    public static string ConnectionString
    {
        get
        {
            return @"Data Source=" + 
                System.Reflection
                    .Assembly
                    .GetExecutingAssembly()
                    .Location
                    .Substring(0, 
                        System
                        .Reflection
                        .Assembly
                        .GetExecutingAssembly()
                        .Location
                        .LastIndexOf("\\") + 1) 
                + @"\\people.sdf";
        }
    }
}

Furthermore:

• Notice the constructor.
  It invokes the base class constructor, providing a simple connection string to our database.
  It could have also provided the name of a connection string from the App.Config file, or it could provide no connection string at all (in which case our database would be created on the local instance of SQL Express, with a name similar to our executing application.
• The connection string provided here corresponds with MS SQL Compact, and refers to a database file, people.sdf, which is expected to be created/found on the same folder of our application's assembly (If debug/run the solution from Visual Studio, the folder would be Bin\Debug by default).

Sowing the seeds of love

Many times when we run a newly built database-oriented application, we'd want to start with some predefined data already stored in the database.
If we generate the database manually with some SQL script, we could easily include some INSERT statements in order to generate and store the initial data.
In the case of Entity Framework, we can generate and store our initial data by creating the following class:

public class DatabaseInitializer : CreateDatabaseIfNotExists
{
    protected override void Seed(PersonsContext context)
    {
        Company telerik = new Company(Guid.NewGuid(), "Telerik");;
        Person alon = new Person(Guid.NewGuid(), "Alon", 38, Prefix.Mr, telerik);
    
        context.Companies.Add(telerik);
        context.Persons.Add(alon);
    
        context.SaveChanges();
    }
}

Notice:

• This class inherits the generic CreateDatabaseIfNotExists class. This is the default policy of the Entity Framework (to create the database if it does not already exist, duh!). Other options are listed on the MSDN library (duh!!).
• We override the Seed method, and populate the context argument with our predefined objects. Once the new database is created, this data should be automatically inserted into the underlying tables. In this case, one company (telerik) and one person (alon) are created and seeded.

I ❤ Helper classes

Now almost everything is in its right place. We will need to tie everything together, though.
I love helper classes. They are just so damn helpful. I've created this helper class in order to simplify and wrap the interaction with the data context nicely.

public class DataManager
{
    public static PersonsContext DataContext
    {
        get
        {
            if (DataManager.dataContext == null)
            {
                Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
                DataManager.dataContext = new PersonsContext();
                bool dbExists = DataManager.dataContext.Database.Exists();
                if (dbExists)
                {
                    //UpgradeScript();
                }
                if (!dbExists)
                {
                    Database.SetInitializer(new DatabaseInitializer());
                }
                else
                    Database.SetInitializer<PersonsContext>(null);
            }
            return DataManager.dataContext;
        }
    }
    private static PersonsContext dataContext = null;
}

About this class:

• The main purpose here is to statically cache (in memory) and manage our PersonsContext object.
• A few important settings are also applied here:
  Setting the default database factory to SQL Server Compact (SqlServerCe).
• If the database does not exist, an initializer of type DatabaseInitializer is assigned. That is, the seeding class defined above will kick in and create the persisted entities in the database.
• If the database already exists, no initializer is assigned, but I'm leaving the option to run upgrade scripts (i.e. check the version of our data and apply changes in the database schema according to changes in our persisted classes and/or seeded data to match the current product version) if they are needed.
  Those should manipulate tables, and need to be run directly against the database.

Manipulations are always such fun

To demonstrate the ease of use of this shameless production and manipulation through basic CRUD operations, I've constructed a small console application. Here is its main function:

class Program
{
    static void Main(string[] args)
    {
        //Check seeding:
        Console.WriteLine("{0} persons are in your database.\n", DataManager.DataContext.Persons.Count());
    
        //Basic CRUD operations:
    
        //C(reate)
        if (DataManager.DataContext.Companies.FirstOrDefault(
        c => c.CompanyName == "Microsoft") == null)
        {
            DataModel.Company microsoft = new DataModel.Company(Guid.NewGuid(), "Microsoft");
            DataManager.DataContext.Companies.Add(microsoft);
            DataManager.DataContext.Persons.Add(new DataModel.Person(
                Guid.NewGuid(), 
                "John doe", 
                20, 
                DataModel.Prefix.Dr, 
                microsoft));
            DataManager.DataContext.Persons.Add(new DataModel.Person(
                Guid.NewGuid(), 
                "Klark Kent", 
                30, 
                DataModel.Prefix.Dr, 
                microsoft));
            DataManager.DataContext.SaveChanges();
        }
    
        //R(ead)
        IQueryable<DataModel.Person> microsoftWorkers = DataManager.DataContext.Persons.Where(
            person => person.Workplace.CompanyName == "Microsoft");
        Console.WriteLine("{0} persons are working in Microsoft.\n", microsoftWorkers.Count());
    
        //U(pdate)
        DataModel.Person johnDoe = DataManager.DataContext.Persons.FirstOrDefault(
            p => p.Name == "John Doe");
        if (johnDoe != null)
        {
            johnDoe.Name = "Changed Name";
            DataManager.DataContext.SaveChanges();
        }
    
        //D(elete)
        DataModel.Person klark = DataManager.DataContext.Persons.FirstOrDefault(
            p => p.Name == "Klark Kent");
        if (klark != null)
        {
            DataManager.DataContext.Persons.Remove(klark);
            DataManager.DataContext.SaveChanges();
        }
            
        //Done.
        Console.ReadKey();
    }
}

Where is my data?

As written in the prerequisites section above, I'm using the very comfortable SQL Server Compact Toolbox Visual Studio extension.
Once installed, it can be accessed through the Tools menu:

Connecting to your database file is then extremely simple:

In the dialog, browse to select your created SDF database file, the connection string will be automatically created:

And you can now browse through your database and run queries like any civilized respectful person would do, if they were you:

A query execution window will open