SQLite in .NET Core with Entity Framework Core

access_time 3 years ago visibility32307 comment 3

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 Administrator at 2 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

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


Learn more arrow_forward

More from Kontext

local_offer dotnetcore

visibility 2952
thumb_up 0
access_time 4 years ago

Sending emails are common in applications. For example, when user registers, we need to send account activation emails. This post summarize the approaches we can use to send emails in .NET Core 1.x and 2.x.

local_offer dotnetcore

visibility 2949
thumb_up 0
access_time 3 years ago

After upgrading to ASP.NET Core 2.1 (.NET Core SDK 2.1.301), you may encounter the following error about encoding: System.ArgumentException    HResult=0x80070057    Message='Cyrillic' is not a supported encoding name. For information on defining a custom encoding, see the ...

Machine Learning with .NET in Jupyter Notebooks

local_offer plot local_offer machine-learning local_offer jupyter-notebook local_offer C# local_offer dotnetcore

visibility 914
thumb_up 0
access_time 11 months ago

In this article, I'm going to show you how to install Jupyter in Windows and then install .NET kernel for Jupyter notebooks. It also shows a machine learning example using ML.NET. The target audience are .NET developers who want to expand their skills in data engineering and science domain with ...

About column

Everything about .NET framework, .NET Core and .NET Standard. 

rss_feed Subscribe RSS