Connect to SQLite with Entity Framework Core

SQLite is a lightweight, serverless database engine that's ideal for local data storage in .NET applications. When combined with Entity Framework Core, it becomes a powerful solution for rapid development with minimal configuration.

In this tutorial, you’ll learn how to connect a C# application to SQLite using dotConnect for SQLite, generate EF Core models with Entity Developer, and implement full CRUD operations—all within a streamlined, efficient workflow.

Why dotConnect for SQLite?

dotConnect for SQLite offers many other cool features, such as advanced integration with ADO.NET through Visual Studio and enhanced ORM support. You will find a more comprehensive list of features on our website.

Download and activate dotConnect for SQLite

30-day free trial version

Download and install dotConnect for SQLite directly on your machine, or install the Devart.Data.SQLite NuGet package.

To activate a 30-day trial, use the license key assigned to you after completing the registration on the Devart website.

Full version

After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.

To activate a connection in your application, add the License Key to your connection string.

Entity Developer allows you to visually design and generate EF Core models, making database application development faster, easier, and more efficient. If you don't have it already installed, close your Visual Studio instance, download Entity Developer, and install it following the on-screen instructions.

Follow the detailed illustrated guide to create your database model using Entity Developer. When this process is complete, the model you created opens. Check this model and context classes.

If you don't use Microsoft Visual Studio but instead use Microsoft VS Code, JetBrains Rider, etc., you can use Entity Developer as a standalone application and work with models there.

Create an EF Core model using Scaffold-DbContext

You can use Scaffold-DbContext to generate DbContext and entity classes for your database. Run the following command, replacing values with your actual credentials:

Scaffold-DbContext "DataSource=sakila.db;LicenseKey=**********" -provider Devart.Data.SQLite.Entity.EFCore -OutputDir Models

After you execute this command, the ModelContext file and the Models folder containing the table entity classes get generated.

This section connects to an SQLite database and retrieves the first 10 rows from the Actor table. The sakilaModel DbContext is configured with an SQLite connection string in OnConfiguring.

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using var context = new sakilaModel();
            var actors = context.Actors.Take(10).ToList();

            Console.WriteLine("First 10 Actors:");
            foreach (var actor in actors)
            {
                Console.WriteLine($"ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        Console.ReadKey();
    }
}

Run the application. Results are shown in the console, listing the first 10 actors retrieved from the SQLite database. This output verifies that the data context and query are functioning as expected.

This section inserts a new test actor into the Actor table using EF Core’s Add and SaveChanges methods.

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using var context = new sakilaModel();
            var newActor = new Actor
            {
                FirstName = "Test",
                LastName = "Actor",
                LastUpdate = DateTime.Now
            };

            context.Actors.Add(newActor);
            context.SaveChanges();

            Console.WriteLine($"Inserted Actor - ID: {newActor.ActorId}, Name: {newActor.FirstName} {newActor.LastName}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        Console.ReadKey();
    }
}

The console displays the inserted record.

This section updates the test actor’s first and last names. The record is retrieved, modified, and saved with SaveChanges.

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using var context = new sakilaModel();
            var actor = context.Actors.FirstOrDefault(a => a.FirstName == "Test" && a.LastName == "Actor");

            if (actor != null)
            {
                actor.FirstName = "Updated";
                actor.LastName = "Star";
                actor.LastUpdate = DateTime.Now;
                context.SaveChanges();

                Console.WriteLine($"Updated Actor - ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
            }
            else
            {
                Console.WriteLine("Actor not found.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        Console.ReadKey();
    }
}

The console shows the updated record.

This section deletes the test actor from the Actor table using Remove and SaveChanges.

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using var context = new sakilaModel();
            var actor = context.Actors.FirstOrDefault(a => a.FirstName == "Updated" && a.LastName == "Star");

            if (actor != null)
            {
                context.Actors.Remove(actor);
                context.SaveChanges();

                Console.WriteLine($"Deleted Actor - ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
            }
            else
            {
                Console.WriteLine("Actor not found.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        Console.ReadKey();
    }
}

The console confirms the deletion.

Video tutorial: Connect to SQLite with EF Core

In this tutorial, you learned how to integrate Entity Framework Core with SQLite using dotConnect for SQLite. The guide covered model generation with Entity Developer and demonstrated full CRUD operations.

With its simplicity and portability, SQLite is well-suited for desktop, mobile, and lightweight web applications. When combined with EF Core, it provides a flexible and efficient data access solution—making it an ideal choice for projects that require a lightweight, file-based database.