ASP.NET Core 2.0 with SQLite and Entity Framework Core

This article shows how to use SQLite with ASP.NET Core 2.0 using Entity Framework Core. EF Core can now create SQLite databases using Entity Framework migrations which was not possible in previous versions.

Code: https://github.com/damienbod/AspNetCoreSQLite

2017-08-17: Updated to ASP.NET Core 2.0 and EF Core 2.0
2017-02-02: Updated to VS2017 csproj with EF Core msbuild
2017-07-01: Updated to VS2017 csproj and ASP.NET Core 1.1
2016-07-01: Updated to ASP.NET Core 1.0 RTM
2016-05-17: Updated to ASP.NET Core 1.0 RC2 dotnet
2015-11-18: Updated to ASP.NET Core 1.0 RC1
2015-10-16: Updated to ASP.NET Core 1.0 beta8

The project was created using Omnisharp generator-aspnet and then upgraded to version dotnet using the command line tools dotnet, and then upgraded to VS2017 csproj.

The required dependencies are added to the AspNetCoreSQLite.csproj file. This is the hardest part, as you have to figure out which packages are required and also if the packages work together.

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
    <AssemblyName>AspNetCoreMultipleProject</AssemblyName>
    <PackageId>AspNet5MultipleProject</PackageId>
  </PropertyGroup>

  <ItemGroup>
    <Content Update="wwwroot\**\*;Views;Areas\**\Views;appsettings.json;config.json;web.config">
      <CopyToPublishDirectory>PreserveNewest</CopyToPublishDirectory>
    </Content>
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.0" PrivateAssets="All" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.0.0" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
    <DotNetCliToolReference Include="Microsoft.Extensions.SecretManager.Tools" Version="2.0.0" />
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.0" />
  </ItemGroup>
  <ItemGroup>
    <Folder Include="Migrations\" />
  </ItemGroup>
</Project>

Next step is to create the POCO classes and the Entity Framework context. This is a simple class with a Id of type long.

public class DataEventRecord
{
	public long Id { get; set; }
	public string Name { get; set; }
	public string Description { get; set; }
	public DateTime Timestamp { get; set; }
}

The context is defined with a single DbSet and a key mapping.

using Microsoft.EntityFrameworkCore;

namespace AspNet5SQLite.Model
{
    // >dotnet ef migration add testMigration
    public class DataEventRecordContext : DbContext
    {
        public DataEventRecordContext(DbContextOptions<DataEventRecordContext> options) :base(options)
        { }
        
        public DbSet<DataEventRecord> DataEventRecords { get; set; }
      
        protected override void OnModelCreating(ModelBuilder builder)
        { 
            builder.Entity<DataEventRecord>().HasKey(m => m.Id); 
            base.OnModelCreating(builder); 
        } 
    }
}

Entity Framework is then added in the Startup class. The SQLite Provider is used and the connection string defines the path to the SQLite file.

public void ConfigureServices(IServiceCollection services)
{
	var connection = Configuration["Production:SqliteConnectionString"];

	services.AddDbContext<DataEventRecordContext>(options =>
		options.UseSqlite(connection)
	);
	
	services.AddMvc();
	services.AddScoped<IDataEventRecordRepository, DataEventRecordRepository>();
}

The connection string is defined in the config.json file in this application. It is important to define the path as shown below ‘C:\\…’, otherwise the application will not work.

{
    "Production": {
        "SqliteConnectionString": "Data Source=C:\\git\\damienbod\\AspNetCoreSQLite\\src\\AspNetCoreSQLite\\dataeventrecords.sqlite"
    }
}

Now the database can be created using EF migrations. Open the command line and execute the following command in the src directory of the application.

Note: At present the EF Core migrations do not work with msbuild. Due to this, you need to use the old project.json file as well as the global.json to do the migrations from the command line using dotnet.

dotnet ef migrations add testMigration

Now create the database:

dotnet ef database update

This will create a new Migrations folder with the generated classes. Run the application and the database will be created. This can be checked using SQLite Manager in Firefox:

The context can now be used. A simple repository has been created for the CRUD operations.

using System.Collections.Generic;
using System.Linq;
using AspNet5SQLite.Model;
using Microsoft.AspNetCore.Mvc;  
using Microsoft.Extensions.Logging;

namespace AspNet5SQLite.Repositories
{
    public class DataEventRecordRepository : IDataEventRecordRepository
    {
        private readonly DataEventRecordContext _context;

        private readonly ILogger _logger;

        public DataEventRecordRepository(DataEventRecordContext context, ILoggerFactory loggerFactory)
        {
            _context = context;
            _logger = loggerFactory.CreateLogger("IDataEventRecordResporitory");          
        }

        public List<DataEventRecord> GetAll()
        {
            _logger.LogCritical("Getting a the existing records");
            return _context.DataEventRecords.ToList();
        }

        public DataEventRecord Get(long id)
        {
            return _context.DataEventRecords.First(t => t.Id == id);
        }

        [HttpPost]
        public void Post(DataEventRecord dataEventRecord )
        {
            _context.DataEventRecords.Add(dataEventRecord);
            _context.SaveChanges();
        }

        public void Put(long id, [FromBody]DataEventRecord dataEventRecord)
        {
            _context.DataEventRecords.Update(dataEventRecord);
            _context.SaveChanges();
        }

        public void Delete(long id)
        {
            var entity = _context.DataEventRecords.First(t => t.Id == id);
            _context.DataEventRecords.Remove(entity);
            _context.SaveChanges();
        }
    }
}

The repository is used in the MVC controller:

using System.Collections.Generic;
using AspNet5SQLite.Model;
using AspNet5SQLite.Repositories;
using Microsoft.AspNetCore.Mvc;

namespace AspNet5SQLite.Controllers
{
    [Route("api/[controller]")]
    public class DataEventRecordsController : Controller
    {
        private readonly IDataEventRecordRepository _dataEventRecordRepository;

        public DataEventRecordsController(IDataEventRecordRepository dataEventRecordRepository)
        {
            _dataEventRecordRepository = dataEventRecordRepository;
        }

        [HttpGet]
        public IEnumerable<DataEventRecord> Get()
        {
            return _dataEventRecordRepository.GetAll();
        }

        [HttpGet("{id}")]
        public DataEventRecord Get(long id)
        {
            return _dataEventRecordRepository.Get(id);
        }

        [HttpPost]
        public void Post([FromBody]DataEventRecord value)
        {
            _dataEventRecordRepository.Post(value);
        }

        [HttpPut("{id}")]
        public void Put(long id, [FromBody]DataEventRecord value)
        {
            _dataEventRecordRepository.Put(id, value);
        }

        [HttpDelete("{id}")]
        public void Delete(long id)
        {
            _dataEventRecordRepository.Delete(id);
        }
    }
}

The default IoC from ASP.NET 5 is used to define the dependencies in the startup class.

services.AddScoped<IDataEventRecordRepository, DataEventRecordRepository>();

The DefaultContractResolver needs to be set if your using Pascal case json.

services.AddMvc().AddJsonOptions(options =>
{
	options.SerializerSettings.ContractResolver = new DefaultContractResolver();
});

The server side of the application is up and running and CRUD operations function with an MVC 6 application using Entity Framework code first with SQLite.

A simple angular client application has been created to test the CRUD operations.

Once the application is started, this can be tested using the following links:

http://localhost:30660/index.html
http://localhost:30660/#/home/overview/details/1
http://localhost:30660/index.html#/home/overview/create

You can also verify the results in the SQLite Manager in Firefox:

Conclusion

It is now much easy to set up a SQLite database in Entity Framework compared to previous versions, and once the initial bugs from the migrations are sorted, this will a great solution for certain problems, application types.

Links:

https://blogs.msdn.microsoft.com/dotnet/2016/05/16/announcing-entity-framework-core-rc2/

https://github.com/Schr3da/ASP.net-vnext-samples

Getting started with ASP.NET 5 MVC 6 Web API & Entity Framework 7

https://github.com/OmniSharp/generator-aspnet

Tags: ASP.NET 5, ASP.NET Core, ASP.NET Core 1.0, aspnetcore, dotnet, EF, EF7, Entitty Framework 7, Migrations, MVC, MVC 6, SQLite, TopHeaderMenu, vNext