.NET Core - New database

In this walkthrough, you will create a .NET Core console app that performs basic data access against a SQLite database using Entity Framework Core. You will use migrations to create the database from your model. See ASP.NET Core - New database for a Visual Studio version using ASP.NET Core MVC.

Notes:

Prerequisites

The following prerequisites are needed to complete this walkthrough:

Create a new project

  • Create a new ConsoleApp.SQLite folder for your project and use the dotnet command to populate it with a .NET Core app.
mkdir ConsoleApp.SQLite
cd ConsoleApp.SQLite/
dotnet new console

Install Entity Framework Core

To use EF Core, install the package for the database provider(s) you want to target. This walkthrough uses SQLite. For a list of available providers see Database Providers.

  • Install Microsoft.EntityFrameworkCore.Sqlite and Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design
  • Manually edit ConsoleApp.SQLite.csproj to add a DotNetCliToolReference to Microsoft.EntityFrameworkCore.Tools.DotNet

    Note: A future version of dotnet will support DotNetCliToolReferences via dotnet add tool

ConsoleApp.SQLite.csproj should now contain the following:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp1.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="1.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.1" PrivateAssets="All" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.0" />
  </ItemGroup>
</Project>

Note: The version numbers used above were correct at the time of publishing.

  • Run dotnet restore to install the new packages.

Create the model

Define a context and entity classes that make up your model.

  • Create a new Model.cs file with the following contents.
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;

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

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=blogging.db");
        }
    }

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

Tip: In a real application you would put each class in a separate file and put the connection string in a configuration file. To keep the tutorial simple, we are putting everything in one file.

Create the database

Once you have a model, you can use migrations to create a database.

  • Run dotnet ef migrations add InitialCreate to scaffold a migration and create the initial set of tables for the model.
  • Run dotnet ef database update to apply the new migration to the database. This command creates the database before applying migrations.

Notes:

  • When using relative paths with SQLite, the path will be relative to the application's main assembly. In this sample, the main binary is bin/Debug/netcoreapp1.1/ConsoleApp.SQLite.dll, so the SQLite database will be in bin/Debug/netcoreapp1.1/blogging.db.

Use your model

  • Open Program.cs and replace the contents with the following code:

    using System;
    
    namespace ConsoleApp.SQLite
    {
        public class Program
        {
            public static void Main()
            {
                using (var db = new BloggingContext())
                {
                    db.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
                    var count = db.SaveChanges();
                    Console.WriteLine("{0} records saved to database", count);
    
                    Console.WriteLine();
                    Console.WriteLine("All blogs in database:");
                    foreach (var blog in db.Blogs)
                    {
                        Console.WriteLine(" - {0}", blog.Url);
                    }
                }
            }
        }
    }
    
  • Test the app:

    dotnet run

    One blog is saved to the database and the details of all blogs are displayed in the console.

    ConsoleApp.SQLite>dotnet run
    Project ConsoleApp.SQLite (.NETCoreApp,Version=v1.1) will be compiled because 
    inputs were modified
    Compiling ConsoleApp.SQLite for .NETCoreApp,Version=v1.1
    
    Compilation succeeded.
        0 Warning(s)
        0 Error(s)
    
    Time elapsed 00:00:00.8735339
    
    1 records saved to database
    
    All blogs in database:
     - http://blogs.msdn.com/adonet
    

Changing the model:

  • If you make changes to your model, you can use the dotnet ef migrations add command to scaffold a new migration to make the corresponding schema changes to the database. Once you have checked the scaffolded code (and made any required changes), you can use the dotnet ef database update command to apply the changes to the database.
  • EF uses a __EFMigrationsHistory table in the database to keep track of which migrations have already been applied to the database.
  • SQLite does not support all migrations (schema changes) due to limitations in SQLite. See SQLite Limitations. For new development, consider dropping the database and creating a new one rather than using migrations when your model changes.

Additional Resources