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:
I've submitted the example code to GitHub for your reference:
sqlite-example