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.
Provides integrated support for the SQLite Encryption Extension with AES, Blowfish, TripleDES, Cast128, and RC4 encryption algorithms.
Fully supports EF Core, Dapper, NHibernate, LinqConnect, and more for efficient data management.
Conforms to ADO.NET standards for seamless integration with .NET applications.
Includes specific features and fully supports all unique data types for accurate and complete data representation.
Features native integration with Visual Studio and complete design-time support for accelerated development.
Includes priority support, detailed documentation, and regular updates for continuous improvement.
You can start using dotConnect for SQLite immediately with a 30-day free trial. Choose one of the following options:
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.
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 ModelsThis command generates the ModelContext file and the Models folder containing the table entity classes.
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.
Thanks to its simplicity and portability, SQLite is a great choice for desktop, mobile, and lightweight web applications. When combined with EF Core, it provides a flexible and efficient way to access and manage data. This makes it an excellent option for projects that require a lightweight, file-based database solution.
In this tutorial, we explored how to integrate Entity Framework Core with SQLite using dotConnect for SQLite. The guide explained how to generate a data model with Entity Developer and demonstrated how to perform CRUD operations. You can try it yourself with a fully functional free trial to see how it can simplify many everyday development tasks.
Scaffold-DbContext with a dotConnect connection string (including License key) to generate the DbContext and entity classes.
I'm a technical content writer who loves breaking complex tech topics into clear and helpful content that's enjoyable to read. With a solid writing background and growing skill in software development and database tools, I create content that's accurate, easy to follow, and genuinely useful. When I'm not writing, you'll probably find me learning something new or sweating it out at the gym.
