ASP.NET Core 3: Add Entity Framework Core to Existing Project

Last week was an unofficial kicked off a series of posts associated with a refresh of the ASP.NET Basics repo frequently reference in this blog to reflect the state of affairs now that .NET Core 3 has been released. The new repo is ASP.NET Basics Refresh because naming is hard.

This post is going to take the API project created last week for the Swagger/OpenAPI with NSwag and ASP.NET Core 3 post and replace the generated data with a database using Entity Framework Core. If you want to follow along with this post the files before any changes can be found here.

Add NuGet Packages

Entity Framework Core is no longer included with .NET Core by default so we install a couple of NuGet packages to get started. I’m going to give the .NET CLI command, but this could also be done using the Visual Studio NuGet Package Manager UI. This post will also be using SQLite, but Entity Framework Core supports multiple databases you would need to install the package for the database you are interested in using.

Here are the commands to install the package we will be using. This is also assuming your terminal is in the same directory as the project file.

dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design

Add a DbContext

Just as a reminder we already have a Contact class in the Models directory with the following definition.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}

Next, I added a Data directory to the project and then added a new class called ContactedDbContext. The DbContext only exposes one DbSet for Contacts.

public class ContactsDbContext : DbContext
{
    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext(DbContextOptions<ContactsDbContext> options) 
        : base(options)
    { }
}

Configuration for the Connection String

In the appsettings.json file, which is where the application will pull configuration from by default, we are going to add a connection strings section to hold our default connection. The following is my full appsettings.json with the connection string for SQLite. If you are using a different database provider your connection string could be drastically different.

{
  "ConnectionStrings": {
    "DefaultConnection": "DataSource=app.db"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Check out Configuration in ASP.NET Core for more details on the different ways to handle configuration.

Register DbContext with the Services Container

Open Startup.cs and in the ConfigureServices function, we are going to use the AddDbContext extension method to add our new DbContext and tell it to use SQLite with the connection string from our appsettings.json. The following is the full function with the first two lines being the ones we added.

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ContactsDbContext>(options =>
        options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));

    services.AddControllers();
    services.AddOpenApiDocument(document => 
        document.PostProcess = d => d.Info.Title = "Contacts API");
}

Check out the official doc for more information on Dependency injection in ASP.NET Core.

Create and Apply Initial Migration

For this bit, we are going to head back to the command line open to the directory that contains the csproj for the project we are working with. The first thing we need to do is to install the Entity Framework Core Tool using the following command which will install the tool globally.

dotnet tool install --global dotnet-ef

Next, we will create a migration called Initial that output in the Data/Migrations directory using the following command.

dotnet ef migrations add Initial -o Data/Migrations

Now that we have a migration lets use it to create our database. Note that the same command will be used in the future when applying migrations to an existing database.

dotnet ef database update

Check out the official docs for more information on the Entity Framework Core Tool or Global Tools in general.

Scaffold a New Controller

If you are using the code from GitHub at this point you will need to delete the ContactsController as it is going to be recreated using Visual Studio’s tooling.

Right-click on the directory where the controller should be created, the Controllers directory in the example, and select Add and then Controller.

On the dialog that pops up, we want to select API Controller with actions, using Entity Framework and then click Add.

On the next screen specify the model classdata context, and controller name before clicking Add. In the sample case, we are going to use our Contact class for the model, ContactDbContext for the data context to generate a controller named ContactController.

After clicking add the requested controller will be generated with all the functions needed for CRUD operations for the selected model class. The code for our sample controller can be found here.

Try it out

Running the application and hitting our swagger UI with the help of NSwag we can see all the options our API has available and even try them out which will now hit our application’s database.

Another great option to test out APIs which has a lot of really great features is Postman. Either option will allow you to try out your API without having to build a client.

Wrapping Up

Hopefully, this post will help you get a jump start on integrating Entity Framework Core in your ASP.NET Core 3 applications. As a reminder Entity Framework Core supports a lot of different database providers. If you have any question I recommend checking Microsoft’s official docs on Getting Started with Entity Framework Core.

The code with all the above changes can be found here.

ASP.NET Core Configuration Issue with In Process Hosting

Since ASP.NET Core 2.2 was released I have been working on getting all my different applications updated and using in-process hosting. I pretty quickly hit an issue with an application that uses SQLite. As soon as the application tried to access the database I ended up with the following error.

SqliteException: SQLite Error 14: ‘unable to open database file’. Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int rc, sqlite3 db) Microsoft.Data.Sqlite.SqliteConnection.Open() Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(bool errorsExpected)

Issue

After some Googling, I found an issue on GitHub that details the problem. It turns out that when the application gets its current directory it is returning the path to the IIS process that is hosting the application instead of the directory when the application is.

Work Around

On another GitHub issue, I found a link to a recommended workaround. Add the following class somewhere in your application. This code comes here.

internal class CurrentDirectoryHelpers
{
    internal const string AspNetCoreModuleDll = "aspnetcorev2_inprocess.dll";

    [System.Runtime.InteropServices.DllImport("kernel32.dll")]
    private static extern IntPtr GetModuleHandle(string lpModuleName);

    [System.Runtime.InteropServices.DllImport(AspNetCoreModuleDll)]
    private static extern int http_get_application_properties(ref IISConfigurationData iiConfigData);

    [System.Runtime.InteropServices.StructLayout(System.Runtime.InteropServices.LayoutKind.Sequential)]
    private struct IISConfigurationData
    {
        public IntPtr pNativeApplication;
        [System.Runtime.InteropServices.MarshalAs(System.Runtime.InteropServices.UnmanagedType.BStr)]
        public string pwzFullApplicationPath;
        [System.Runtime.InteropServices.MarshalAs(System.Runtime.InteropServices.UnmanagedType.BStr)]
        public string pwzVirtualApplicationPath;
        public bool fWindowsAuthEnabled;
        public bool fBasicAuthEnabled;
        public bool fAnonymousAuthEnable;
    }

    public static void SetCurrentDirectory()
    {
        try
        {
            // Check if physical path was provided by ANCM
            var sitePhysicalPath = Environment.GetEnvironmentVariable("ASPNETCORE_IIS_PHYSICAL_PATH");
            if (string.IsNullOrEmpty(sitePhysicalPath))
            {
                // Skip if not running ANCM InProcess
                if (GetModuleHandle(AspNetCoreModuleDll) == IntPtr.Zero)
                {
                    return;
                }

                IISConfigurationData configurationData = default(IISConfigurationData);
                if (http_get_application_properties(ref configurationData) != 0)
                {
                    return;
                }

                sitePhysicalPath = configurationData.pwzFullApplicationPath;
            }

            Environment.CurrentDirectory = sitePhysicalPath;
        }
        catch
        {
            // ignore
        }
    }
}

Finally, in the Main function of the Program class add the following line as the first thing in the function.

CurrentDirectoryHelpers.SetCurrentDirectory();

Wrapping Up

With the above changes, all will work as expected. It is my understanding that this issue will be addressed at some point in the future as a patch so this should just be a temporary fix.

Entity Framework Core: SQLite Concurrency Checks

Most of the work I have done with SQLite has been on single-user systems, but recently I had to work on a SQLite project that was going to have a handful of concurrent users and a subset of the user activities would need to deal with concurrency issues. In the past, in a situation like this, I have been using SQL Server and use the rowversion or timestamp column type which places a unique value on the row on any updates or inserts.

There is a page in the official docs on Concurrency Tokens, but for me, it wasn’t super helpful. Thankfully after some searching, I came across the GitHub issue In ASP.Net Core 2.x with Entity Framework Core, Concurrency Control not working with SQLite which had a solid sample as one of the replies. This post is going to walk through an example implementation of that sample. The starting point of the code can be found in this GitHub repo.

Sample Background

The sample project being used is a simple web application to manage a contact list. The repo contains an implementation using Postgres and one using Sqlite. This whole post will only be touch files found in the Sqlite folder/project.

Model Changes and Data Migration

SQLite doesn’t have the concept of a timestamp column, but this solution is going to emulate one. To do this we are going to change the Contact model found in the Models folder. We are going to add a Timestamp property with a Timestamp data annotation. The following is the full model class with the new property at the bottom.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Subregion { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
    [Timestamp] public byte[] Timestamp { get; set; }
}

Next, let’s create a new migration with the change to the model. I’m using the .NET CLI so from a command prompt in the project directory run the following command.

dotnet ef migrations add ContactTimestamp --context ContactsDbContext

In the Migrations directory, open newly created migration. It should be named something like *_ContactTimestamp.cs.  In the Up function, we are going to add a couple of triggers to the new Timestamp column. These triggers are going to assign a random blob to the timestamp column when a row is inserted or updated which is how we are simulating the function of SQL Server’s Timestamp data type. The following is the full Up function with the added triggers.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<byte[]>(
        name: "Timestamp",
        table: "Contacts",
        rowVersion: true,
        nullable: true);

    migrationBuilder.Sql(
        @"
        CREATE TRIGGER SetContactTimestampOnUpdate
        AFTER UPDATE ON Contacts
        BEGIN
            UPDATE Contacts
            SET Timestamp = randomblob(8)
            WHERE rowid = NEW.rowid;
        END
    ");

    migrationBuilder.Sql(
        @"
        CREATE TRIGGER SetContactTimestampOnInsert
        AFTER INSERT ON Contacts
        BEGIN
            UPDATE Contacts
            SET Timestamp = randomblob(8)
            WHERE rowid = NEW.rowid;
        END
    ");
}

To apply the migration to the database you can use the following command.

dotnet ef database update --context ContactsDbContext

Testing it out

Now for a quick and dirty test, we are going to add a ConcurrencyTest function to the existing ContactsController. This function is going to ensure a specific contact exists, then pull the contact from two different DBContexts, make a mutation on the resulting contact objects, then attempt to save. The first save will work and the second should fail. Please note that this function isn’t an example of how things should be done just a quick and dirty way to prove that the concurrency check is happening.

[Route("ConcurrencyTest")]
public void ConcurrencyTest()
{
    var context1 = new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                                         .UseSqlite("Data Source=Database.db").Options);
    var context2 = new ContactsDbContext(new DbContextOptionsBuilder<ContactsDbContext>()
                                         .UseSqlite("Data Source=Database.db").Options);

    var contactFromContext1 = context1.Contacts.FirstOrDefault(c => c.Name == "Test");

    if (contactFromContext1 == null)
    {
        contactFromContext1 = new Contact
        {
            Name = "Test"
        };

        context1.Add(contactFromContext1);
        context1.SaveChanges();
    }

    var contactFromContext2 = context2.Contacts.FirstOrDefault(c => c.Name == "Test");

    contactFromContext1.Address = DateTime.Now.ToString();
    contactFromContext2.Address = DateTime.UtcNow.ToString();

    context1.SaveChanges();
    context2.SaveChanges();
}

Run the application and hit the ConcurrenctTest route which is http://localhost:1842/ConcurrencyTest for my test. The following is the resulting exception.

An unhandled exception occurred while processing the request.

DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

Wrapping Up

While the information wasn’t the easiest in the world to locate, as you can see Entity Framework Core using SQLite has good support for concurrency control. The above is just one option for its implementation. I hope this saves you all so time.

The code in its final state can be found here.

Add Entity Framework Core to an Existing ASP.NET Core Project

I had a Web API application that I was using to test something that had no database interaction at all that I needed to add database interaction too. Going through my posts I didn’t find a guide to add Entity Framework Core to an existing project, so that is what this post is going to cover. We will be using SQLite for our database.

Project Creation

The project we are starting with is just a new ASP.NET Core Web API created using the .NET CLI using the following command from a command prompt.

dotnet new webapi

This gives us a new Web API with no database interaction and makes sure we are all on the same page for the rest of the post.

Project File Changes

Open the csproj file associated with your project. First, we need to add a package reference to the Entity Framework Core tools.

Before:
<ItemGroup>
  <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.5" />
</ItemGroup>

After:
<ItemGroup>
  <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.5" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.1" PrivateAssets="All" />
</ItemGroup>

Next, we need to add a reference to the Entity Framework Core Tools for the .NET CLI.

Before:
<ItemGroup>
  <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.2" />
</ItemGroup>

After:
<ItemGroup>
  <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.2" />
  <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" />
</ItemGroup>

The final change to the project file is only needed if you are using SQLite and it is to keep the database file from showing up in the file list. Add the following and adjust app.db to match your database name.

<ItemGroup>
  <None Update="app.db" CopyToOutputDirectory="PreserveNewest" />
</ItemGroup>

App Settings

Now we need to store the database connection string. Again we are using SQLite in this example so if you are trying to use SQL Server (include LocalDb) your connection string will look much different. This is also a place where you need to replace app.db with the database name you want to use.

Open the appsettings.json file and add a ConnectionStrings section similar to the following.

"ConnectionStrings": {
  "DefaultConnection": "DataSource=app.db"
}

The following is my full settings file after the change just to make sure the context is clear.

{
  "ConnectionStrings": {
    "DefaultConnection": "DataSource=app.db"
  },
  "Logging": {
    "IncludeScopes": false,
    "Debug": {
      "LogLevel": {
        "Default": "Warning"
      }
    },
    "Console": {
      "LogLevel": {
        "Default": "Warning"
      }
    }
  }
}

Model and Db Context

If you are a long time reader it will be no surprise that the model we will be using is that of a contact. The following is my full contact model.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Subregion { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}

Now that we have a model we need to add a DBContext.

public class ContactsDbContext : DbContext
{
    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext(DbContextOptions<ContactsDbContext> options)
        : base(options)
    {

    }
}

Startup

Now that we have the application settings, model, and context from above open up the Startup class and in the ConfigureServices function add the following code to get the ContactsDbContext into the container.

services.AddDbContext<ContactsDbContext>(options => 
    options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));

Initial Migration

Now that the project is all set to go let’s add an initial migration that will get the table for our contacts set up. From a command prompt in the project directory run the following command.

dotnet ef migrations add Contacts

This will add a Migrations directory to the project with the code needed to add the contacts table. If you want to go ahead and apply the migration to the database run the following command.

dotnet ef database update

Since we didn’t have a database yet the above command creates one which will show up in the root of our project with a name that matches our application settings since we are using SQLite.

Wrapping Up

Adding Entity Framework Core to a project is pretty easy, but not something I do a lot, so this will serve as a good reminder of the steps. Hope it helps you out as well.

Entity Framework Core with SQLite Migration Limitations

This is part of what has turned into a series on Entity Framework Core with SQLite. The other parts can be found below.

Entity Framework Core with SQLite
Entity Framework Core Errors Using Add-Migration
Entity Framework Core with SQLite Scaffolding

The starting point of the code for this post can be found here.

Migration Limitations when using SQLite

SQLite’s ALTER TABLE is limited which in turn limits what Entity Framework Core can do via a migration. The official docs on the subject can be found here. These limitations are on the Entity Framework Team’s list of issues as an open enhancement and can be tracked here.

As long as you are just adding new tables or columns you would never notice the limitation, but if you have spelling problems like I do then the need to rename a column can be important. Thankfully things like ReSpeller (link is to the pro page, but a free version is available in ReSharpers extension manager) help with my spelling issues.

Unsupported example with a column rename

As an example of how to handle a migration that isn’t supported, we are going to rename the State property of the Contact class to Subregion.

Rename property on the model

Open the Contact class which can be found in the Models directory and make the following change.

Before:
public string State { get; set; }

After:
public string Subregion { get; set; }
Add a migration

With the property name change using the following command in the Package Manager Console to create a new migration.

Add-Migration RenameContactStateToSubregion -c ContactsDbContext

Which produces the following migration class.

public partial class RenameContactStateToSubregion : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn(
            name: "State",
            table: "Contacts",
            newName: "Subregion");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn(
            name: "Subregion",
            table: "Contacts",
            newName: "State");
    }
}
Error trying to apply the migration

As expected when an attempt to apply the above migration results in the following exception.

System.NotSupportedException: SQLite does not support this migration operation (‘RenameColumnOperation’). For more information, see http://go.microsoft.com/fwlink/?LinkId=723262.

Modify migration to manually rename the column

Searching for how to rename a column in SQLite will turn up a lot of results including this from the official docs and answers like this on StackOverflow. The gist of the how to do a rename is to create a new table with the desired schema, copy the data from the original table, drop the old table, and finally rename the new table to match the original name.

Now knowing the process the migration above can be modified to apply SQL directly instead of using Entity Framework Core to generate the SQL. This can be done by using the Sql function of the MigrationBuilder class. The following is the resulting migration.

public partial class RenameContactStateToSubregion : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            @"PRAGMA foreign_keys = 0;

              CREATE TABLE Contacts_temp AS SELECT *
                                            FROM Contacts;
              
              DROP TABLE Contacts;
              
              CREATE TABLE Contacts (
                  Id         INTEGER NOT NULL
                                     CONSTRAINT PK_Contacts PRIMARY KEY AUTOINCREMENT,
                  Address    TEXT,
                  City       TEXT,
                  Email      TEXT,
                  Name       TEXT,
                  Phone      TEXT,
                  PostalCode TEXT,
                  Subregion  TEXT
              );
              
              INSERT INTO Contacts 
              (
                  Id,
                  Address,
                  City,
                  Email,
                  Name,
                  Phone,
                  PostalCode,
                  Subregion
              )
              SELECT Id,
                     Address,
                     City,
                     Email,
                     Name,
                     Phone,
                     PostalCode,
                     State
              FROM Contacts_temp;
              
              DROP TABLE Contacts_temp;
              
              PRAGMA foreign_keys = 1;");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
    }
}

You will notice that I didn’t bother doing the Down function, but the same idea would apply when trying to undo a migration. SQLiteStudio or similar tools can be used to generate the SQL above if SQL isn’t something you want to deal with.

Fix other references to the renamed field

This isn’t really the topic of this post, but I wanted to throw in a reminder that after a rename like this there are places that will need to be updated that the tooling may not have picked up. For example, make sure all your views are using the new column as well as any bind statements in your controllers.

Wrapping up

The first time I hit the need to rename a column and it resulted in an exception it was extremely frustrating. Over time as I learned what the tooling around SQLite provides it has become less of an issue. I look forward to seeing what the Entity Framework team does in the future around this issue. The finished code can be found here.

Entity Framework Core with SQLite Scaffolding

This is the third in what is turning into a series of post about using SQLite with Entity Framework Core. This post is going to cover adding a migration, scaffolding a controller and related views, and a few things that are harder to do using SQLite. The following are the first two post.

Entity Framework Core with SQLite
Entity Framework Core Errors Using Add-Migration

Adding Model, DbContext, Controller, and Views

If you have any experience with Entity Framework Core or have read any of my past entries on the subject this section is going to repeat some of the same information, but I am including it so someone who is looking for a full example will have it.

Model

In the Models folder add a Contact class similar the following.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}
DbContext

In the Data folder add a ContactsDbContext that inherits from DbContext. The following is an example that auto applies migrations to a database, if you don’t need that functionality it can be dropped out.

public sealed class ContactsDbContext : DbContext
{
    private static bool _created;

    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext(DbContextOptions<ContactsDbContext> options)
        : base(options)
    {
        if (_created) return;
        Database.Migrate();
        _created = true;
    }
}

Now that the application has a model and a related DbContext the following can be used to add a migration that will create a Contacts in the SQLite database. Run from the Package Manager console.

Add-Migration AddContacts -Context ContactsDbContext

Add-Migration is a Powershell command to add a migration (surprise!), AddContacts is the name of the migration and -Context ContactsDbContext is an argument that lets the command know which DbConext to use. The Context is only needed if your application has more than one DbContext.

Controller and Views

With the above complete Visual Studio provides some tooling that makes it very fast to create a controller with views for listing, adding, editing, and deleting items. To begin right-click on the Controllers folder and select Add > New Scaffolded Item.

Select the MVC Controller with views, using Entity Framework option and click Add.

On the next dialog use the drop downs to select a model class and a data context class. Then verify the controller name and click add.

When the process completes the following items will have been added to your project.

Controllers
 - ContactsController.cs
Views
 - Contacts
   - Create.cshtml
   - Delete.cshtml
   - Details.cshtml
   - Edit.cshtml
   - Index.cshtml
Add to nav bar

To add a link to the new section of the app to the nav bar open the _Layout.cshtml in the Views/Shared/ directory. The following is the section of the file that needs to be changed to add an item to the nav bar.

<ul class="nav navbar-nav">
    <li><a asp-area="" asp-controller="Home" asp-action="Index">Home</a></li>
    <li><a asp-area="" asp-controller="Contacts" asp-action="Index">Contacts</a></li>
    <li><a asp-area="" asp-controller="Home" asp-action="About">About</a></li>
    <li><a asp-area="" asp-controller="Home" asp-action="Contact">Contact</a></li>
</ul>

Specifically, the following line was added to provide access to the contact list page.

<li><a asp-area="" asp-controller="Contacts" asp-action="Index">Contacts</a></li>

Wrapping up

With the above, the application will be runnable. The code for this post can be found here. The next post in this series will cover the limitations of migrations when using SQLite with Entity Framework Core.

 

Entity Framework Core with SQLite

All the applications used as examples for ASP.NET Core and Entity Framework Core from this site so far used database running SQL Server/SQL Express. In addition to the Microsoft-based SQL databases, Entity Framework Core has support for a number of other database providers. This post is going to look at using SQLite. A full list of the support database providers can be found here.

Starting point

Using Visual Studio 2017 I started with a new ASP.NET Core project using Individual User Accounts which ensured all the Entity Framework Core bits were present. The template in RC 4 used packages based on the Core 1.0.3 which I upgraded to 1.1.0. The project at this point can be found here.

Just a side note this project was created when Visual Studio 2017 was at the RC 4 stage. This code associated with this post will be updated when Visual Studio 2017 is released.

Naming warning

As you will be able to see with the structure of the solution I started this work using the project name SQLite. With this project name, it was impossible to get the SQLite package to install. If you see something like the following renaming your project should get you running.

Cycle detected: 
   Sqlite (>= 1.0.0) -> Microsoft.EntityFrameworkCore.Sqlite (>= 1.1.0) -> Microsoft.Data.Sqlite (>= 1.1.0) -> SQLite (>= 3.13.0)

This issue is where I found out what the problem was.

Add SQLite Packages

Right-click on the project file and click Manage NuGet Packages.

Select Browse and in the search box enter “Microsoft.EntityFramework.Sqlite” and install the two packages that are found.

Remove SqlServer Packages

While still in the Manage NuGet Packages screen click on the Installed tab. Select and uninstall the following packages.

Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.SqlServer.Design

Configuration changes

Open appsettings.json and in the ConnectionStrings delete the line for DefaultConnection. Next, in the same section add a line for a SQLite connection string. The following is the result.

"ConnectionStrings": {
  "Sqlite": "Data Source=Database.db"
}

The above will expect the database file to be in the same location as the application is running. For a debug build the database file can be found in the \bin\Debug\netcoreapp1.0\ directory of the project.

Startup changes

The final location to change is in the ConfigureServices function of the Startup class. The following shows the addition of the application DB context before and after the changes.

Before:
services
  .AddDbContext<ApplicationDbContext>(options =>  options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
 
After:
services
  .AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(Configuration.GetConnectionString("Sqlite")));

Wrapping up

The application is now runnable using SQLite as its backing data store. At this point, the only thing using data access is related to identity. The first time an attempt is made to access the database you may be prompted to apply migrations.

I have been using SQLite Studio to view the data in my database if you have that need outside of the application it does a good job.

The code in its final state can be found here.