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 (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 number of limitations 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.

Latest version of NuGet Package Manager and PowerShell

Installing EF Core requires an up-to-date version of NuGet Package Manager. You can install the latest version from Visual Studio Gallery. Make sure you restart Visual Studio after installing the update.

Visual Studio 2015: [Install the latest NuGet extension for VS2015] (https://visualstudiogallery.msdn.microsoft.com/5d345edc-2e2d-4a9c-b73b-d53956dc458d)

Visual Studio 2013: Install the latest NuGet extension for VS2013

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

Install the Entity Framework Core RC2 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.5.1 or later.

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

Install-Package EntityFrameworkCore.SqlServerCompact40 –Pre

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 –Pre

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):

* ```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**):

1. ```Install-Package Microsoft.EntityFrameworkCore.Tools -Pre ``` to make the reverse engineer command available in Package Manager Console. (If not already installed in the section above)
* ```Install-Package EntityFrameworkCore.SqlServerCompact40.Design -Pre``` to install the SQL Server Compact reverse engineer component.
* ```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.

A reverse engineer component for SQL Server Compact 3.5 SP2 is also available: EntityFrameworkCore.SqlServerCompact35.Design

## Use Your Model
You can now use your model to perform data access.

```csharp
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();
            }
        }
    }
}