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.
Prerequisites
- Visual Studio 2022: Our IDE of choice. If you do not have it on your machine, go to the official website to download and install it. We will use the community version, so you can get it as well.
- dotConnect for SQLite: A high-performance ADO.NET data provider for SQLite with enhanced ORM support and database connectivity features.
- Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation.
- Sakila database: A sample database for learning and testing. Download the folder and unzip the file to use it.
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.
No license key is required, and you can start exploring the product immediately.
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.
Create a .NET project
1. Open Visual Studio and select Create a new project. Choose Console App, and click Next.
2. Enter a project name (for example, SQLite_EF_Core), then click Create.
3. Right-click the project in Solution Explorer and select Manage NuGet Packages.
4. On the Browse tab, search for and install the following packages:
Create an EF Core model via Entity Developer
1. Right-click anywhere inside Solution Explorer and select Add > New Item.
2. Go to Installed > C# Items > Data, select Devart EF Core Model, and click Add.
3. In the Create Model Wizard, select Database First and click Next.
4. Fill in the details of your SQLite database connection and click Next.
5. Select Generate From Database and click Next.
6. Choose the database objects you want to scaffold. You can select all, but since we've been using the Actor table, let's select only it.
7. Define the naming convention for the property names in the database object and click Next. We suggest keeping the default settings this time.
8. On the next page, configure the necessary settings, including file selection for saving the connection, and defining the names of DbContext classes. Select your EF Core version and .NET framework and click Next.
9. Choose the model diagram contents. You can use all entities, split the entities by database, or do a custom selection. For this tutorial, select All Entities and click Next.
10. Choose code generation templates for your objects. You can define parameters according to your preferences or apply default settings. For this tutorial, use the default settings. Click Next.
11. Your model is ready now. Click Finish.
The model you created opens.
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.
Connect to SQLite and retrieve data
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.
Insert new records using EF Core
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.
Update SQLite data using EF Core
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.
Delete SQLite data using EF Core
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
Conclusion
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.