How to Use Entity Framework (EF) Core
With SQLite

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.

dotConnect for SQLite is an ideal ADO.NET data provider to use for all SQLite-related operations. It offers features like on-the-fly connector creation, flexible configuration, seamless integration with Visual Studio, and enhanced ORM support. You can download a free trial on this page.

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

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. After installing the NuGet package, proceed to activate the trial version or enter your license. Visit the dotConnect for SQLite download page and download the dcsqlite.exe installer. Then, execute the downloaded file and follow the instructions to install dotConnect for SQLite on your machine. This process not only installs dotConnect for SQLite but also ensures that all features are activated and available for use in your project. Also, note that registered users download the full version from their account, not the trial one.

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

8. 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 "Data 
Source=..\SQLite_EF_Core\sqlite-sakila-db\sakila.db" -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.