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.

Add a migration

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

Which produces the following migration class.

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.

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.

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.

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 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.

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.

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

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.

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.

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.

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.

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.