Connect to SQLite with Entity Framework Core

In this tutorial, you'll learn how to create an EF Core model in two ways: using Scaffold-DbContext and Entity Developer. Additionally, we'll show you how to get, update, remove, and add SQLite data into a table.

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.

• 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, as previously mentioned.
• 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.

First of all, it's required to set up a new .NET Application project.

1. Open Visual Studio and click Create New Project. In the Search Template field, type ASP.NET Core Web Application and click the corresponding search result.

2. Name your project. For example, ours will be called SQLite_EF_Core.

3. Select .NET 8 as the target framework and click Create.

4. The next step is to install dotConnect for SQLite in our project. To do that, click Tools, point to NuGet Package Manager, and click Manage NuGet Packages for Solution.

5. The NuGet Package Manager page opens. Click Browse, search for Devart.Data.SQLite.EFCore, and select it. Then, select your project and click Install.

6. Run the application by pressing F5 to open the scaffolded web page.

7. Find the sqlite-sakila-db folder that we have previously downloaded at the requirements phase. Move it into your project to make it look like this:

You can create an EF Core model using either Scaffold-DbContext or Entity Developer. Let's take a closer look at each method.

1. Ensure the EF Core Tools are installed in your development environment. You can install them using the .NET CLI by running the command:

dotnetdotnet tool install --global dotnet-ef

2. Install Scaffold-DbContext using the Package Manager Console. To do this, launch Visual Studio first. The Package Manager Console opens. Execute this command:

Install-Package Microsoft.EntityFrameworkCore.Tools

3. Once installed, it's possible to use Scaffold-DbContext to generate DbContext and entity classes for the SQLite database. Go ahead and run this command in the Package Manager Console:

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

After executing this command, MainContext and the Models folder containing the table entity classes should be generated. You should see them in Solution Explorer.

Install Entity Developer by following the on-screen instructions.

1. In the Solution Explorer, right-click the SQLite_EF_Core project and select Add > New Item.

2. Go to Installed > C# > ASP.NET Core > 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're using the Actor table, let's select only that one.

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 Model properties page, select .NET 8 (or a different framework that your project uses) from Target Framework and click Next.

9. Choose the model diagram content. 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 different parameters you want the object to follow. Let's use the default settings for this tutorial. Click Next.

The model is ready now.

11. Clear the Download 'Devart.Data.SQLite.EFCore' NuGet package checkbox, as we've already added it to the project; then click Finish.

The created model opens. Here, you can modify fields, change connections, then generate SQL code to modify the database or update it from the model.

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.

Now, move on to creating a connection class.

public class DatabaseConnectionClass {
  private readonly MainContext _context;

  public DatabaseConnectionClass() {
    _context = new MainContext();
  }

  public IEnumerable GetActors() {
    return _context.Actors.Take(10).ToList();
  }
}

2. Visualize the retrieved data. To do that, click Controllers in Solution Explorer. Then, click Add New Class and name it ActorsController. Copy this piece of code into it:

[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController: ControllerBase {
  private readonly DatabaseConnectionClass _context;

  public ActorsController() {
    _context = new DatabaseConnectionClass();
  }

  [HttpGet]
  public IEnumerable GetActors() {
    return _context.GetActors();
  }
}

When you run your application using F5 and go to the GetActors endpoint, click Try it out and then Execute.

You'll see the query results in the response body.

Great, but what if we want to get just one particular actor? Let's handle that scenario. Add the following code to DatabaseConnectionClass:

public IEnumerable GetActorById(int Id) {
  return _context.Actors.Where(a => a.ActorId == Id).ToList();
}

Then add this code to the ActorsController class:

[HttpGet("{Id}")]
public IEnumerable GetActorsById(int Id) {
  return _context.GetActorById(Id);
}

Now, run your application again, select the GetActorId endpoint from the dropdown, enter a specific ID, and execute. You should see the actor with the specified ID returned.

To add a new record to the Actors table, proceed with the following steps:

1. Paste this piece of code into DatabaseConnectionClass to have the possibility to insert a new record and return its ID:

public int AddActor(Actor actor) {
  _context.Actors.Add(actor);
  _context.SaveChanges();
  return actor.ActorId;
}

2. Add this code to ActorsController:

[HttpPost]
public int AddActor(Actor actor) {
  return _context.AddActor(actor);
}

If you run the application again, you'll see that the endpoint has been added.

3. Click the drop-down menu and paste this JSON object into it:

{
  "actorId":201,
  "firstName": "Johnny",
  "lastName": "Lewis",
  "filmActors": []
} 

It should look as follows:

4. Click Execute. The response body should contain the ID you assigned the data.

Suppose you need to change Johnny's last name. For this, add this piece of code into DatabaseConnectionClass:

public Actor UpdateActor(Actor actor) {
  _context.Actors.Update(actor);
  _context.SaveChanges();
  return actor;
}

Then paste this code into ActorsController:

[HttpPut]
public Actor UpdateActor(Actor actor) {
  return _context.UpdateActor(actor);
}

Run the application, select UpdateActor, and insert this JSON object:

{
  "actorId":201,
  "firstName": "Johnny",
  "lastName": "Mcginnis",
  "filmActors": []
}

It should look like this.

In the response body, you'll see the record with the updated last name.

Suppose you need to delete Johnny's data from the table. To do this, add this code to the
DatabaseConnection class:

public bool DeleteActor(int Id) {
  var actor = _context.Actors.FirstOrDefault(a => a.ActorId == Id);
  if (actor != null) {
    _context.Actors.Remove(actor);
    _context.SaveChanges();
    return true;
  }
  return false;
}

And paste this code into ActorController:

[HttpDelete("{Id}")]
public bool DeleteActor(int Id) {
  return _context.DeleteActor(Id);
}

Thus, the row will be deleted from the table and true will be returned if it's successful. If no record for that particular ID exists or there is an error, the false status will be displayed. Run the application again and remove the DeleteActor ID.

You can verify that the record with the ID has been deleted using the GetActorById endpoint:

An empty array indicates that the record no longer exists.

Video tutorial: Connect to SQLite with EF Core

In this tutorial, we demonstrated how to work with EF Core using dotConnect for SQLite. It also covered retrieving, inserting, updating, and deleting data, as well as creating an EF Core model with Entity Developer.

Overall, SQLite is an ideal choice for mobile, desktop, and small-scale web applications, which require simple and file-based databases. As you can see, the bundle of EF Core and SQLite can offer flexibility in database management and migrations.