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.

image

Check the database

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

image

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

image

image

info Last modified by Raymond at 4 months ago * This page is subject to Site terms.

More from Kontext

Entity Framework Core Code-First - Generate Covering Index with Columns Included

local_offer entity-framework local_offer asp.net core local_offer Azure local_offer C#

visibility 20
thumb_up 0
access_time 11 days ago

In SQL Server or some other relational databases, it is a very common requirement to create covering index with columns included in index pages beside the index key columns. With Entity Framework Core, you can also easily generate covering indexes using purely C# code. Scenario For ...

open_in_new ASP.NET Core

local_offer pandas local_offer sqlite

visibility 117
thumb_up 0
access_time 3 months ago

In my previous posts, I showed how to use  jaydebeapi or sqlite3 pack...

open_in_new Python Programming

local_offer sqlite local_offer python local_offer Java

visibility 86
thumb_up 0
access_time 4 months ago

To read data from SQLite database in Python, you can use the built-in sqlite3 package . Another approach is to use SQLite JDBC driver via  ...

open_in_new Python Programming

local_offer python local_offer sqlite

visibility 40
thumb_up 0
access_time 4 months ago

SQLite is one of the most commonly used embedded file databases. All the mainstream programming language/framework provides APIs to interact with SQLite database. In my previous article  ...

open_in_new Python Programming

info About author

comment Comments (3)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

R
Raymondarrow_drop_down

Hello,

I didn’t quite get this. Can you be more specific? Do you mean the sample code is not working or the UI is not responsive?

format_quote

Comment is deleted or blocked.

reply Reply
R
Raymondarrow_drop_down

I've submitted the example code to GitHub for your reference:

sqlite-example

format_quote

person Rodri access_time 2 years ago
Re: SQLite in .NET Core with Entity Framework Core

Hello, from where I can download the example because I am trying to make a small application but I get an error when accessing or creating the database. Thank you
reply Reply
account_circle Rodri
Hello, from where I can download the example because I am trying to make a small application but I get an error when accessing or creating the database. Thank you
reply Reply

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.


Learn more arrow_forward