SQLite EF Core Database Provider

Original link

This database provider allows Entity Framework Core is used with SQLite. The provider is maintained as part of the Entity Framework Core project.

Supported Database Engines

• SQLite (3.7 onwards)

Supported platforms

• The. NET Framework (4.5.1 onwards)

• . NET Core

• Mono (4.2.0 onwards)

• Universal Windows Platform

Getting Started with EF-Core on Universal Windows Platform (UWP) with a New Database

Getting Started with EF core in. NET core Console App with a New database

SQLite EF Core Database Provider Limitations

. Net Core EF Core's SQLite usage and deployment

ASP. NET core Development-Entity Framework (EF) Core

Apply EF to access SQLite data

Entity Framework Documentation && NuGet

Testing with SQLite

SQLite has an in-memory mode , allows-you-use SQLite-to-write tests against a relational database, with Out the overhead of actual database operations.

Example Testing Scenario

Consider the following service that allows application code to perform some operations related to blogs. Internally it uses a DbContext , connects to a SQL Server database. It would is useful to swap this context to connect to a in-memory SQLite database So we can write effic Ient tests for this service without has to modify the code, or does a lot of work to create a test double of the context.

Get your context readyavoid configuring the database providers

In your tests you is going to externally configure the context to use the InMemory provider. If You is configuring a database provider by overridingOnConfiguringin your context and then your need to add some conditional Code to ensure this configure the database provider ifone has not already been configured.

Tip

If you are using ASP. need this code since your database provider is Co should Nfigured outside of the context (in Startup.cs).

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFProviders.InMemory;Trusted_Connection=True;ConnectRetryCount=0");
    }
}

Add a constructor for testing

The simplest-to-enable testing against a different database is to modify your context to expose a constructor that ACC Epts a DbContextOptions<TContext>.

public class BloggingContext : DbContext
{
    public BloggingContext()
    { }

    public BloggingContext(DbContextOptions<BloggingContext> options)
        : base(options)
    { }

  dbcontextoptions<tcontext> tells the context all of it settings, such as which database to Connec T to. This is the same object, which is built by running, the Onconfiguring method in your context.

Writing Tests

The key to testing with this provider are the ability to tell the context to use SQLite, and control the scope of the In-memory database. The scope of the database is controlled by opening and closing the connection. The database is scoped to the duration, the connection is open. Typically want a clean database for each test method.

using BusinessLogic;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Linq;

namespace TestProject.SQLite
{
    [TestClass]
    public class BlogServiceTests
    {
        [TestMethod]
        public void Add_writes_to_database()
        {
            // In-memory database only exists while the connection is open
            var connection = new SqliteConnection("DataSource=:memory:");
            connection.Open();

            try
            {
                var options = new DbContextOptionsBuilder<BloggingContext>()
                    .UseSqlite(connection)
                    .Options;

                // Create the schema in the database
                using (var context = new BloggingContext(options))
                {
                    context.Database.EnsureCreated();
                }

                // Run the test against one instance of the context
                using (var context = new BloggingContext(options))
                { 
                    var service = new BlogService(context);
                    service.Add("http://sample.com");
                }

                // Use a separate instance of the context to verify correct data was saved to database
                using (var context = new BloggingContext(options))
                {
                    Assert.AreEqual(1, context.Blogs.Count());
                    Assert.AreEqual("http://sample.com", context.Blogs.Single().Url);
                }
            }
            finally
            {
                connection.Close();
            }
        }

        [TestMethod]
        public void Find_searches_url()
        {
            // In-memory database only exists while the connection is open
            var connection = new SqliteConnection("DataSource=:memory:");
            connection.Open();

            try
            {
                var options = new DbContextOptionsBuilder<BloggingContext>()
                    .UseSqlite(connection)
                    .Options;

                // Create the schema in the database
                using (var context = new BloggingContext(options))
                {
                    context.Database.EnsureCreated();
                }

                // Insert seed data into the database using one instance of the context
                using (var context = new BloggingContext(options))
                {
                    context.Blogs.Add(new Blog { Url = "http://sample.com/cats" });
                    context.Blogs.Add(new Blog { Url = "http://sample.com/catfish" });
                    context.Blogs.Add(new Blog { Url = "http://sample.com/dogs" });
                    context.SaveChanges();
                }

                // Use a clean instance of the context to run the test
                using (var context = new BloggingContext(options))
                {
                    var service = new BlogService(context);
                    var result = service.Find("cat");
                    Assert.AreEqual(2, result.Count());
                }
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

SQLite is a lightweight database that adheres to the acid-based relational database management system, which is contained in a relatively small C library. It is the public domain project established by D.richardhipp. It is designed to be embedded , and has been used in many embedded products, it occupies a very low resource , in the embedded device, may only need hundreds of K of memory is enough. It can support Windows/linux/unix and so on mainstream operating system , and can be combined with many programming languages, such as TCL, C #, PHP, Java, and ODBC interface, also compared to MySQL, PostgreSQL, the two open source world-renowned database management system, is processing faster than they do. The first alpha version of SQLite was born in May 2000. To 2015 has been 15 years, SQLite also ushered in a version of SQLite 3 has been released.

Unlike the common client-server paradigm, the SQLite engine is not a separate process for communicating with it, but rather a major part of connecting to a program . So the main communication protocol is the direct API call within the programming language . This has a positive effect on total consumption, delay time, and overall simplicity. The entire database (definitions, tables, indexes, and data itself) is stored in a single file on the host host. Its simple design is done by locking the entire data file at the start of a transaction . [1]

SQLite Tutorials

SQLite is a software library that implements a self-contained, server-free, 0-configured, transactional SQL database engine . SQLite is the most widely deployed SQL database engine in the world. SQLite source code is not subject to copyright restrictions.

Why use SQLite?

• There is no need for a separate server process or operation of the system (no server).

• SQLite does not require configuration, which means that installation or management is not required.

• A complete SQLite database is stored in a single cross-platform disk file.

• SQLite is very small, is lightweight, fully configured when less than 400KiB, omit optional feature configuration when less than 250KiB.

• SQLite is self-sufficient, which means that no external dependencies are required.

• SQLite transactions are fully ACID-compatible and allow secure access from multiple processes or threads.

• SQLite supports the functionality of most query languages for the SQL92 (SQL2) standard.

• SQLite is written using ansi-c and provides a simple and easy-to-use API.

• SQLite can be run in UNIX (Linux, Mac os-x, Android, IOS) and Windows (Win32, WinCE, WinRT).

Current EF Core supported databases:

• Microsoft SQL Server
• Sqlite
• Postgres (Npgsql)
• SQL Server Compact Edition
• InMemory (for testing purposes)

The following will increase:

• Mysql
• IBM DB2

SQLite EF Core Database Provider