How to Connect to SQLite with Entity Framework Core

This tutorial covers how to use Entity Framework Core to perform operations in .NET applications. Also, you'll learn how to create Entity Framework Core models using Entity Developer.

Entity Framework Core 8 offers such advantages:

• EF 8 easily integrates with JSON in databases to work with collections of various data types.
• It lets you map and query lists and arrays directly in JSON columns.
• The framework allows writing raw SQL queries, even for types not included in EF models.
• EF 8 now supports HierarchyId for Hierarchical data in SQL Server.
• It makes application state management smoother with lazy loading and change tracking.

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

Visit the dotConnect for SQLite download page. You have the option to select either the free trial or the full version of dotConnect for SQLite. The free trial version gives you 30 days to explore all features, but it cannot be used for deploying commercial projects and does not require a license key for activation.

Download and install the trial version

For the trial version, simply download and install dotConnect for SQLite on your machine. No license key is required for the trial, and you can start exploring the product immediately.

Purchase and access the full version

After purchasing the full version, go to your profile's Licenses page. You'll find a list of your licenses, including your dotConnect for SQLite purchase. Click the Details button next to your dotConnect for SQLite license. Here, you'll find your license details, including the Activation Key.

Download the full version

Click the Download Product Versions button to access the list of available product versions. Select the most recent version and download the installer.

Execute the downloaded file and follow the on-screen instructions to install dotConnect for SQLite on your machine and activate all features associated with your license key.

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:

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.Entity.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.

4. Next, retrieve some data from the database using the generated context. Right-click the SQLite_EF_Core project, select Add Class, and add DatabaseConnectionClass. Finally, paste this code into it:

public class DatabaseConnectionClass
{
    private readonly MainContext _context;

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

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

5. 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 into DatabaseConnectionClass:

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

Then add this code into 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 into 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.

Let's imagine it's necessary to delete data about Johnny 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.

1. Install the Entity Developer program and open it.

2. Navigate to File > New Model.

3. Under Categories, click Entity Framework, select EF Core Model, and click Create.

4. In Entity Developer: Create Model Wizard, select Database First and click Next.

5. The Setup data connection properties window opens. Enter the path to the folder with the saved Sakila database:

6. Choose the modal content by selecting Generate From Database and click Next.

7. Choose the database objects you want to scaffold.

After clicking Next, you will be asked to set up naming rules. This will define the naming convention that the property names in the database object should follow.

8. Adjust model properties to your needs. The only thing that will change on this page is the Target Framework. Since we are using .NET 8, we are going to select it. However, if your project uses a different framework, you need to specify it instead. Once done, click Next.

9. Choose the model diagram content. For this tutorial, we will use All Entities. Click Next.

10. On the Choose Code Generation Templates page, define different parameters for the object to follow. We're going to use the default settings for our object.

11. Finally, click Finish. The created model will look like this:

In this tutorial, we demonstrated how to work with Entity Framework Core 8 using dotConnect for SQLite. You also learned how to retrieve, add, update, and delete data, as well as how to create an EF Core model using 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.