Code-First Development (EF) w/ SQL CE 4.0

I’ve been playing with the new Microsoft WebMatrix IDE. It’s a very neat little piece of software that takes the effort out of many things. It’s great for small Web projects.

The problem with out-of-the-box WebMatrix development is that it encourages you to use direct SQL programming with embedded SQL queries. The world has moved away from embedded SQL queries in favor of object-relational mapping and (domain-un-specific) query syntax such as LINQ.

I wanted to test out using LINQ2SQL with WebMatrix. The tool to use when doing Database-First LINQ2SQL development is called SQLmetal. It queries the database schema and generates mapping classes (one class per table) that allows one to use LINQ syntax in C# or VB.NET directly against the source data.

The problem, though, is that SQLmetal does not appear to work with SQL CE 4.0. So, what to do?

Microsoft’s next-generation O-R mapper – Entity Framework (EF) – is one possibility. Entity Framework uses the Entity Data Model (EDM) as its conceptual mapping framework.

Microsoft has three basic strategies for using the Entity Framework (very roughly):

  • Database-First: Start with a database and create an Entity Data Model (EDM) that maps to that database
  • Model-First: Start with an Entity Data Model and generate classes and database from that model
  • Code-First: Start with classes and generate a database from those classes; use data annotation attributes to guide the mapping

It is the Model-First and the Code-First approaches that I find most interesting. In this case I chose the Code-First approach because it appears easier to work with without any tooling support.

So what do we need to work with Code-First and SQL CE 4.0?

We need to

  • download and install SQL CE 4.0
  • download and install Entity Framework Code-First (currently at CTP 5 status)

Then we need to setup a basic project. Let’s just make a C# console application.

Then we need to reference some assemblies. We need these ones:

  • System.ComponentModel.DataAnnotations.dll
  • EntityFramework.dll (Code-First CTP 5)
  • System.Data.SqlServerCe.dll (SQL CE 4.0)

Then we need to import some namespaces beyond the default ones:

  • System.Data
  • System.Data.Entity
  • System.Data.Entity.Database
  • System.Data.SqlServerCe
  • System.ComponentModel.DataAnnotations

That settles our external dependencies. Now onto the data modeling.

Most Web sites will want a user type, for logins and such. Let’s create a basic user domain model using Code-First code:

public class User
{
    public string Name { get; set; }

    public string Password { get; set; }

    public string PrimaryEmailAddress { get; set; }

    public string SecondaryEmailAddress { get; set; }

    public DateTime Creation { get; set; }

    public bool Active { get; set; }
}

Now, for the database mapping to make a little more sense we’ll add some constraints via attributes:

public class User
{
    [Key, StringLength(50)]
    public string Name { get; set; }

    [Required, StringLength(100)]
    public string Password { get; set; }

    [Required, StringLength(320)]
    public string PrimaryEmailAddress { get; set; }

    [StringLength(320)]
    public string SecondaryEmailAddress { get; set; }

    [Required]
    public DateTime Creation { get; set; }

    public bool Active { get; set; }
}

The attributes are pretty self-explanatory.

We need one more thing in order to be able to use this, a DbContext-derived class that references our User domain model class.

public class ProtoCatalog : DbContext
{
    public DbSet<User> Users { get; set; }
}

Now we are ready to actually use the model. The following code will insert a new user into the database. Assuming there isn’t already a database file present, one will be automatically generated for us:

static void Main(string[] args)
{
    DbDatabase.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");

    using (var db = new ProtoCatalog())
    {
        var user = new User
        {
            Name = "bob",
            Password = "123",
            Creation = DateTime.Now,
            PrimaryEmailAddress = "bob@example.com"
        };

        db.Users.Add(user);

        db.SaveChanges();
    }
}

That’s it. We now have a new Entity Framework-generated SQL CE 4.0 database with data. It was generated from a simple class modeling our simple domain.

I don’t think the Entity Framework Code-First CTP API is quite as nice as it could be though:  setting a static property on the DbDatabase class to use a specific database driver is not that clean. It’s global state and we don’t like global state.

Let’s try and open the new database in WebMatrix and see what happens.

Let’s see the data as well:

So, we’ve designed a Code-First data model. We’ve made some code to insert data into a database fitting this model. The EF code automatically generated a SQL CE 4.0 database when no previous database file existed. And finally we’ve seen the database schema and data inside of WebMatrix.

Presto.

About these ads

About xosfaere

Software Developer
This entry was posted in Datamodel, Declarative, Imperative, Paradigm, Software, Technical and tagged , , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

3 Responses to Code-First Development (EF) w/ SQL CE 4.0

  1. Pingback: Code-First Development (EF) w/ SQL CE 4.0 | exoteric - sql

  2. Nice post! Also another way of providing the DefaultConnectionFactory is by adding this in the app.config.

  3. Pingback: Less Than Dot - Blog - Awesome

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s