Using EF Core with SQL Server Compact in Traditional .NET Applications (daily builds)

You can find nightly builds of the Entity Framework Core code base hosted on MyGet but beware that the code base is rapidly changing.

You can find CI builds of the SQL Server Compact provider code base hosted on MyGet - CI builds mean that each Merge to the Master branch with passing tests will be published to the MyGet feed.

Configure the NuGet Package Manager in Visual Studio to point to the feeds below. Or add a file called Nuget.config to your solution folder:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
       <packageSources>
          <add key="SQLCE" value="https://www.myget.org/F/ef7-sqlce/api/v3/index.json" />
          <add key="AspNetVNext" value="https://dotnet.myget.org/F/aspnetcore-dev/api/v3/index.json" />
          <add key="NuGet" value="https://api.nuget.org/v3/index.json" />
       </packageSources>
    </configuration>

You need Visual Studio 2017 version 15.3 and PowerShell 5.0

You can use the free Visual Studio 2017 Community Edition.

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.

Run the following command in Package Manager Console to install the SQL Server Compact 4.0 provider. Set the package source to: SQLCE

Install-Package EntityFrameworkCore.SqlServerCompact40 –Pre

If you want to use SQL Server Compact 3.5 SP2, 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).

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 dbContextOptionsBuilder)
        {
            dbContextOptionsBuilder.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.

Make sure to select the AspNetVNext source in Package Manager Console!

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

Install-Package Microsoft.EntityFrameworkCore.Tools -Pre

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.

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