Using EF Core with SQL Server Compact in Traditional .NET Applications

SQL Server Compact

Microsoft SQL Server Compact (SQL CE) is a compact relational database produced by Microsoft for applications that run on desktops. The latest release is SQL Server Compact 4.0 SP1 supporting .NET Framework 4.0 and later. It includes both 32-bit and 64-bit native support. SQL CE targets occasionally connected applications and applications with an embedded database. It is free to download and redistribute. The MSDN documentation is available here

You can use Visual Studio 2017 or newer (including the free Community Edition) combined with my free SQL Server Compact Toolbox extension for SQL CE development. In addition I have a SQL Server Compact related blog and a number of open source tools and libraries to help you make the most of SQL Server Compact.

When using the SQL Server Compact EF Core provider, there are a couple of limitations for migrations you should be aware of. Most of these are a result of limitations in the underlying database engine and are not specific to EF Core.

The SQL Server Compact engine only supports a subset of the data types available on SQL Server, 18 in all, and this limits your possibility of mapping some .NET types directly to SQL Server Compact data types. You can get an overview of the supported data types in the SQL Server Compact Toolbox UI (download link above).

Latest version of NuGet Package Manager and PowerShell

Use Visual Studio 2017, at least Update 3 - version 15.3

Latest version of Windows PowerShell - only required on Windows 8.0/Windows Server 2012 and earlier

Install the Entity Framework Core SQL Server Compact provider

To get the SQL Server Compact EF Core provider in your project you need to install the package for the SQL Server Compact provider from NuGet.org. Your project must target .NET Framework 4.6.1 or later.

Run the following command in Package Manager Console to install the SQL Server Compact 4.0 provider.

Install-Package EntityFrameworkCore.SqlServerCompact40

If you want to use SQL Server Compact 3.5 SP2, make sure the SQL Server Compact 3.5 SP2 runtime is installed, then run:

Install-Package EntityFrameworkCore.SqlServerCompact35

Create Your Model

Define a context and classes that make up your model. Note the new OnConfiguring method that is used to specify the data store provider to use (and, optionally, other configuration too). You can also code generate a model from an existing database, see below.

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace Sample
{
    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlCe(@"Data Source=C:\data\Blogging.sdf");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>()
                .HasMany(b => b.Posts)
                .WithOne(p => p.Blog)
                .HasForeignKey(p => p.BlogId);
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }

        public List<Post> Posts { get; set; }
    }

    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
        public Blog Blog { get; set; }
    }
}

Create Your Database

Now that you have a model, you can use migrations to create a database for you.

In Package Manager Console (Tools –> NuGet Package Manager –> Package Manager Console):

Install-Package Microsoft.EntityFrameworkCore.Tools

to make the migrations commands available in your project.

Add-Migration MyFirstMigration

to scaffold a migration to create the initial set of tables for your model.

Update-Database

to apply the new migration to the database. Because your database doesn't exist yet, it will be created for you before the migration is applied.

If you make future changes to your model, you can use the Add-Migration command to scaffold a new migration to apply the corresponding changes to the database. Once you have checked the scaffolded code (and made any required changes), you can use the Update-Database command to apply the changes to the database.

Create a Model from an existing database

If you already have a database, your can use the Package Manager Console Scaffold-DbContext command to create your model classes from an existing database. This feature is only available in Visual Studio 2015.

To do this, you must add a couple of NuGet packages to your project:

In Package Manager Console (Tools –> NuGet Package Manager –> Package Manager Console):

Install-Package Microsoft.EntityFrameworkCore.Tools  

to make the reverse engineer command available in Package Manager Console. (If not already installed in the section above)

Scaffold-DbContext -Connection "Data Source=C:\data\Chinook.sdf" -Provider EntityFrameworkCore.SqlServerCompact40

to generate DbContext and POCO classes in your project from your existing database.

Use Your Model

You can now use your model to perform data access.

using System;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BloggingContext())
            {
                db.Blogs.Add(new Blog { Url = "http://erikej.blogspot.com" });
                db.SaveChanges();

                foreach (var blog in db.Blogs)
                {
                    Console.WriteLine(blog.Url);
                }
                Console.ReadKey();
            }
        }
    }
}