ASP.NET Core 1.0 with SQLite and Entity Framework 7

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

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

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 RC2 dotnet using the command line tools dotnet.

The global.json in the solution also needs to be updated to match the default runtime version. Now the application can be started from the command line or Visual Studio. If required, the specific runtime can also be defined in the project file.

{
  "projects": [ "src", "test" ],
  "sdk": {
    "version": "1.0.0-preview1-002702"
  }
}

The required dependencies are added to the project.json file. This is the hardest part, as you have to figure out which packages are required and also if the packages work together. EF commands are no longer required in the project.json file.

{
  "userSecretsId": "aspnet-WebApp_Auth-96d521e3-30ac-4a8f-967f-abc243174dce",

  "dependencies": {
    "Microsoft.NETCore.App": {
      "version": "1.0.0",
      "type": "platform"
    },
    "Microsoft.EntityFrameworkCore.Sqlite": "1.0.0",
    "Microsoft.EntityFrameworkCore.Sqlite.Design": "1.0.0",
    "Microsoft.EntityFrameworkCore.Tools": {
      "version": "1.0.0-preview2-final",
      "imports": [
        "portable-net45+win8+dnxcore50",
        "portable-net45+win8"
      ]
    },
    
    "Microsoft.AspNetCore.Authentication.Cookies": "1.0.0",
    "Microsoft.AspNetCore.Diagnostics": "1.0.0",
    "Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore": "1.0.0",
    "Microsoft.AspNetCore.Identity.EntityFrameworkCore": "1.0.0",
    "Microsoft.AspNetCore.Mvc": "1.0.0",
    "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
    "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
    "Microsoft.AspNetCore.StaticFiles": "1.0.0",
    "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
    "Microsoft.Extensions.Configuration.Json": "1.0.0",
    "Microsoft.Extensions.Configuration.UserSecrets": "1.0.0",
    "Microsoft.Extensions.PlatformAbstractions": "1.0.0",
     "Microsoft.Extensions.DependencyModel": "1.0.0-*",
    "Microsoft.Extensions.Logging": "1.0.0",
    "Microsoft.Extensions.Logging.Console": "1.0.0",
    "Microsoft.Extensions.Logging.Debug": "1.0.0",
    "Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0"
  },

  "tools": {
    "Microsoft.AspNetCore.Razor.Tools": {
      "version": "1.0.0-preview2-final",
      "imports": "portable-net45+win8+dnxcore50"
    },
    "Microsoft.AspNetCore.Server.IISIntegration.Tools": {
      "version": "1.0.0-preview2-final",
      "imports": "portable-net45+win8+dnxcore50"
    },
    "Microsoft.EntityFrameworkCore.Tools": {
      "version": "1.0.0-preview2-final",
      "imports": [
        "portable-net45+win8+dnxcore50",
        "portable-net45+win8"
      ]
    },
    "Microsoft.Extensions.SecretManager.Tools": {
      "version": "1.0.0-preview2-final",
      "imports": "portable-net45+win8+dnxcore50"
    },
    "Microsoft.VisualStudio.Web.CodeGeneration.Tools": {
      "version": "1.0.0-preview2-final",
      "imports": [
        "portable-net45+win8+dnxcore50",
        "portable-net45+win8"
      ]
    }
  },

  "frameworks": {
    "netcoreapp1.0": {
      "imports": [
        "dotnet5.6",
        "dnxcore50",
        "portable-net45+win8"
      ]
    }
  },

  "buildOptions": {
    "emitEntryPoint": true,
    "preserveCompilationContext": true
  },

  "runtimeOptions": {
    "gcServer": true
  },

  "publishOptions": {
    "include": [
      "wwwroot",
      "Views",
      "appsettings.json",
      "web.config"
    ]
  },

  "scripts": {
    "prepublish": [ "npm install", "bower install", "gulp clean", "gulp min" ],
    "postpublish": [ "dotnet publish-iis --publish-folder %publish:OutputPath% --framework %publish:FullTargetFramework%" ]
  }
}

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\\AspNet5SQLite\\src\\AspNet5SQLite\\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.

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:

SQLite_ASP_NET5_02

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

SQLite_ASP_NET5_04

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

SQLite_ASP_NET5_03

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

12 comments

  1. SQLite doesn’t support concurrent inserts or multi-threading (unlike SQL Server CE) and the whole database file becomes locked for each operation. It needs explicit locks in the codes, otherwise you will get ‘Database is locked’ errors.

  2. Hi Saeid

    Thanks for the comment. This would make SQL Server CE the better choice if you had this requirement. I would need to implement this, if I used a Scoped instance in a real production application. Thanks for this.

    Greetings Damien

  3. Would a relative path for the db connection string be possible?

    1. Hi Paul

      At present not, but I’m guessing this will work in the next version. Maybe you could create a gitHub issue.

      Greetings Damien

  4. […] Damian Bod wrote a post about ASP.NET 5 using SQLite and Entity Framework 7 […]

  5. […] Yeoman generator-aspnetとEintity Framework 7で簡単なアプリケーションを作るOPC Diary情報源: ASP.NET 5 with SQLite and Entity Framework 7 | Software Engineering […]

  6. […] ASP.NET 5 with SQLite and Entity Framework 7 – damienbod […]

  7. I’m doing some test with asp.net 5 beta8 crl x64 and Sqlite, however sqlite is
    only running at runtime 1.0.0-beta8 clr x86. Perspectiva have any of
    Sqlite work with x64 architecture.

  8. I’m doing some test with asp.net 5 beta7 and Sqlite, however sqlite is
    only running at runtime 1.0.0-beta7 clr x86. Perspectiva have any of
    Sqlite work with x64 architecture.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: