SQLite in .NET Core with Entity Framework Core

SQLite is a self-contained and embedded SQL database engine. In .NET Core, Entity Framework Core provides APIs to work with SQLite.

This page provides sample code to create a SQLite database using package Microsoft.EntityFrameworkCore.Sqlite.

Create sample project

Create a .NET Core 2.x console application in Visual Studio 2017. Add NuGet package reference for Microsoft.EntityFrameworkCore.Sqlite.

Once done, the project file looks like the following:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
     <OutputType>Exe</OutputType>
     <TargetFramework>netcoreapp2.0</TargetFramework>
   </PropertyGroup>
  <ItemGroup>
     <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.1.1" />
   </ItemGroup>
</Project>

Add model Blog

Add a class named Blog with the following code:

    /// <summary>
     /// Blog entity
     /// </summary>
     public class Blog
     {
         [Key]
         public int BlogId { get; set; }
        [Required]
         [MaxLength(128)]
         public string Title { get; set; }
        [Required]
         [MaxLength(256)]
         public string SubTitle { get; set; }
        [Required]
         public DateTime DateTimeAdd { get; set; }
     }

The Blog class includes four properties BlogId, Title, SubTitle and DateTimeAdd. BlogId is annotated as the key column.

Create a DbContext class named MyDbContext

Create a class MyDbContext inherited from DbContext.

public class MyDbContext : DbContext
     {
         public DbSet<Blog> Blogs { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
         {
             optionsBuilder.UseSqlite("Filename=TestDatabase.db", options =>
             {
                 options.MigrationsAssembly(Assembly.GetExecutingAssembly().FullName);
             });
            base.OnConfiguring(optionsBuilder);
         }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
         {
             // Map table names
             modelBuilder.Entity<Blog>().ToTable("Blogs", "test");
             modelBuilder.Entity<Blog>(entity =>
             {
                 entity.HasKey(e => e.BlogId);
                 entity.HasIndex(e => e.Title).IsUnique();
                 entity.Property(e => e.DateTimeAdd).HasDefaultValueSql("CURRENT_TIMESTAMP");
             });
            base.OnModelCreating(modelBuilder);
         }
     }

In the override function OnConfiguring, UseSqlite extended function to configure the connection string. In the options configuration, migration assembly is also configured.

In the override function OnModelCreating, table name is mapped to test.Blogs and Title column is configured as unique index. For column DateTimeAdd the default value is configured using SQL CURRENT_TIMESTAMP.

Use MyDbContext

class Program
     {
         static void Main(string[] args)
         {
             string dbName = "TestDatabase.db";
             if (File.Exists(dbName))
             {
                 File.Delete(dbName);
             }
             using (var dbContext = new MyDbContext())
             {
                 //Ensure database is created
                 dbContext.Database.EnsureCreated();
                 if (!dbContext.Blogs.Any())
                 {
                     dbContext.Blogs.AddRange(new Blog[]
                         {
                             new Blog{ BlogId=1, Title="Blog 1", SubTitle="Blog 1 subtitle" },
                             new Blog{ BlogId=2, Title="Blog 2", SubTitle="Blog 2 subtitle" },
                             new Blog{ BlogId=3, Title="Blog 3", SubTitle="Blog 3 subtitle" }
                         });
                     dbContext.SaveChanges();
                 }
                foreach (var blog in dbContext.Blogs)
                 {
                     Console.WriteLine($"BlogID={blog.BlogId}\tTitle={blog.Title}\t{blog.SubTitle}\tDateTimeAdd={blog.DateTimeAdd}");
                 }
             }
             Console.ReadLine();
         }
     }

In the Main function of the program, the database file will be deleted if existing. And then EnsureCreated function is used to create the database file. After that, data is seeded in table test.Blogs.

Through property Blogs, the blog entries are listed.

Run the program

The following output will show in the console.

Check the database

The database file will be created when the console program runs:

The database schema and data can be viewed through client tools: