Code First with SQL CE

• Download build - 4.9 MB
• Download source - 54.4 KB

Introduction

In this article, I present a sample project using Entity Framework 6 with code first. The project is a part of a DVD collection application with SQL CE database. I found the code first approach very good for project development and prototyping.

The whole project is written in C# using Visual Studio 2010 on Windows 7.

SQL CE

Microsoft SQL Server Compact (SQL CE) is a compact, free single file relational database produced by Microsoft for applications that run on mobile devices and desktops. Prior to the introduction of the desktop platform, it was known as SQL Server for Windows CE and SQL Server Mobile Edition. The latest release is the SQL Server Compact 4.0  supporting .NET Framework 4.0. 

More info.  

Building 

The solution is divided into three projects - all of them are .NET 4.0.

• Model: Model - project with entity classes used for data persistence and business logic
• Dal: Data Access layer - project with DbContext class which is responsible for accessing database
• Test: A console program to test the solution

Model

We start building our solution with this project - because this project defines our database model.

Using NuGet, we download and reference Entity Framework. We also add reference .NET System.ComponentModel.DataAnnotations. Now we can start coding our model. We just define our entities:

• Role.cs
• FilmGenere.cs
• FilmTitle.cs
• Actor.cs
• Producer.cs
• FilmActorRole.cs

We are using annotation to help database generation.

• [Key] - defines the key property - Id
• [Required] - defines the required, e.g., not null
• [StringLength(LEN)] - defines max char length

For relations, we include the ForeignKey field and class field, which is annotated with:

• [ForeignKey(ForeignKeyField)]

Example FilmTitle class:

public class FilmTitle
{
    [Key]
    public int Id { get; set; }
    [Required]
    [StringLength(100)]
    public String Title { get; set; }
    [Required]
    [StringLength(1000)]
    public String Story { get; set; }
    [Required]
    public DateTime ReleaseDate { get; set; }
    [Required]
    public int Duration { get; set; }
    [StringLength(1000)]
    public String Notes { get; set; }

    [Required]
    public int FilmGenereId { get; set; }
    [ForeignKey("FilmGenereId")]
    public FilmGenere FilmGenere { get; set; }

    public List<Producer> Producers { get; set; }

    public List<FilmActorRole> FilmActorRoles { get; set; }
} 

After adding all model classes, we start with new project.

Dal

First, we have to add a reference to Entity framework using NuGet and a reference to project Model.

The main class in this project is the DvdContext which extends DbContext. Into this class, we add DBSet-s of our entity classes.

Our DvdContext now looks like:

public class DvdContext : DbContext
{
    public DbSet<Actor> Actors { get; set; }
    public DbSet<FilmActorRole> FilmActorRoles { get; set; }
    public DbSet<FilmGenere> FilmGeneres { get; set; }
    public DbSet<FilmTitle> FilmTitles { get; set; }
    public DbSet<Producer> Producers { get; set; }
    public DbSet<Role> Roles { get; set; }
} 

Test

We are already done. Just add a console program with references to Dal and Model projects and Entity Framework.

Because we are using SqlCe - we must add also a reference to the EntityFramework.SqlServerCompact using NuGet.

Now we are ready to start using our database. Let's write a simple query to our DvdContext.

static void Main(string[] args)
{
    using (Dal.DvdContext db = new Dal.DvdContext())
    {
        db.Actors.ToList();
    }
} 

After running the application, a database file: CFSqlCe.Dal.DvdContext.sdf is created in project Build folder. If we connect the database file with Visual Studio server explorer, we can see that all required tables have been created.

Customizing

Database File

Database file is named as full class name of our DvdContext. We can override this using connection string written into App.config file.

<connectionStrings>
    <add name="CFSqlCe.Dal.DvdContext" 
    providerName="System.Data.SqlServerCe.4.0" 
    connectionString="Data Source=D:\DvdDatabase.sdf" />
</connectionStrings> 

The above connection string maps our DvdContext to DvdDatabase.sdf file on our system. After running the application, DvdDatabase.sdf is created.

Initialization

Default database initialization with Code First is - "create if not existing". We can control that using our custom class for database initialization.

The database initializer is set using a static constructor of our DvdContext.

static DvdContext()
{
    // Database initialize
    Database.SetInitializer<DvdContext>(new DbInitializer());
    using (DvdContext db = new DvdContext())
        db.Database.Initialize(false);
}

class DbInitializer : DropCreateDatabaseAlways<DvdContext>
{
} 

With this initializer, our database gets recreated every time we run our application. This is just fine for development.

Seeding

Another good feature of database initializer is the possibility to seed data after creation. We do that just overriding Seed method of our DbInitializer:

class DbInitializer : DropCreateDatabaseAlways<DvdContext>
{
protected override void Seed(DvdContext context)
{
    // insert some file generes
    context.FilmGeneres.Add(new FilmGenere()
    {
        Name = "Action"
    });
    context.FilmGeneres.Add(new FilmGenere()
    {
        Name = "SciFi"
    });
    context.FilmGeneres.Add(new FilmGenere()
    {
        Name = "Comedy"
    });
    context.FilmGeneres.Add(new FilmGenere()
    {
        Name = "Romance"
    });
    // some roles
    context.Roles.Add(new Role()
    {
        Name = "Lead"
    });
    context.Roles.Add(new Role()
    {
        Name = "Supporting"
    });
    context.Roles.Add(new Role()
    {
        Name = "Background"
    });
    // some actors
    context.Actors.Add(new Actor()
    {
        Name ="Chris",
        Surname ="Pine",
        Note = "Born in Los Angeles, California"
    });
    context.Actors.Add(new Actor()
    {
        Name = "Zachary",
        Surname = "Quinto",
        Note = "Zachary Quinto graduated from 
        Central Catholic High School in Pittsburgh"
    });
    context.Actors.Add(new Actor()
    {
        Name = "Tom",
        Surname = "Cruise"
    });
    // producers
    context.Producers.Add(new Producer()
    {
        FullName = "J.J. Abrams",
        Email = "jj.adams@producer.com",
        Note = "Born: Jeffrey Jacob Abrams"
    });
    base.Seed(context);
} 

Querying and Entering Data

Now, it is time to try adding some data and query from our just created database. In our test project, I add two FilmTitles.

using (Dal.DvdContext db = new Dal.DvdContext())
{
    // film generes
    Model.FilmGenere actionGenere = db.FilmGeneres.Where
    (g => g.Name == "Action").SingleOrDefault();
    Model.FilmGenere scifiGenere = db.FilmGeneres.Where
    (g => g.Name == "SciFi").SingleOrDefault();
    // find the producer
    Model.Producer jjAbrams = db.Producers.Where
    (p => p.FullName == "J.J. Abrams").SingleOrDefault();
    // we found the producer
    if (jjAbrams != null)
    {
        // add some films to that producer
        Model.FilmTitle film1 = new Model.FilmTitle()
        {
            Title = "Mission: Impossible III",
            ReleaseYear = 2006,
            Duration = 126,
            Story = "Ethan Hunt comes face to face with a dangerous and ...",
            FilmGenere = actionGenere
        };
        film1.Producers = new List<Model.Producer>();
        film1.Producers.Add(jjAbrams);
        db.FilmTitles.Add(film1);
        Model.FilmTitle film2 = new Model.FilmTitle()
        {
            Title = "Star Trek Into Darkness",
            ReleaseYear = 2013,
            Duration = 132,
            Story = "After the crew of the 
            Enterprise find an unstoppable force  ...",
            FilmGenere = scifiGenere
        };
        film2.Producers = new List<Model.Producer>();
        film2.Producers.Add(jjAbrams);
        db.FilmTitles.Add(film2);
        // add some film roles
        Model.Role leadRole = db.Roles.Where(r => 
        r.Name == "Lead").SingleOrDefault();
        Model.Role supportingRole = db.Roles.Where(r => 
        r.Name == "Supporting").SingleOrDefault();
        // load the actors
        Model.Actor tom = db.Actors.Where(a => 
        a.Surname == "Cruise").SingleOrDefault();
        Model.Actor quinto = db.Actors.Where(a => 
        a.Surname == "Quinto").SingleOrDefault();
        Model.Actor pine = db.Actors.Where(a => 
        a.Surname == "Pine").SingleOrDefault();
        // add filmroles
        db.FilmActorRoles.Add(new Model.FilmActorRole()
        {
            Actor = tom,
            Role = leadRole,
            FilmTitle = film1,
            Character = "Ethan",
            Description = "Ethan Hunt comes face to face with 
            a dangerous and sadistic arms dealer while trying to 
            keep his identity secret in order to protect his girlfriend."
        });
        db.FilmActorRoles.Add(new Model.FilmActorRole()
        {
            Actor = pine,
            Role = leadRole,
            FilmTitle = film2,
            Character = "Kirk",
            Description = "Captain Kirk"
        });
        db.FilmActorRoles.Add(new Model.FilmActorRole()
        {
            Actor = quinto,
            Role = supportingRole,
            FilmTitle = film2,
            Character = "Spock",
            Description = "Spock was born in 2230, 
            n the city of Shi'Kahr on the planet Vulcan"
        });
    }
    // save data to db
    db.SaveChanges();
} 

Conclusion

Code first with SQL CE allows us to fast prototype our projects with a simple local DB engine. With database initializers, we can fast drop and recreate changed entities with sample data. This approach is very useful when we change our model a lot.

Migration to a production SQL server database is trivial.

For further reading, I suggest:

• Programming Entity Framework: Code First
• Programming Entity Framework: DbContext

History

• November 2013 - First version of article.