Building an Entity Model Using SQLite

Learn about creating entity data models for our practical applications using SQLite, generating entity models, and setting up the SQLite database.

We'll cover the following

Practical applications usually need to work with data in a relational database or another data store. We will define an entity data model for the Northwind database stored in SQL Server or SQLite. It will be used in most apps we create in subsequent chapters.

Database files

The Northwind4SQLServer.sql and Northwind4SQLite.sql script files are different. The script for SQL Server creates 1313 tables as well as related views and stored procedures. The script for SQLite is a simplified version that only creates 1010 tables because SQLite does not support as many features. The main projects only need those 1010 tables, so we can complete every task with either database.

We should create a separate class library project for our entity data models. This allows easier sharing between backend web servers and frontend desktop, mobile, and Blazor WebAssembly clients.

Creating a class library for entity models using SQLite

We will now define entity data models in a class library so that they can be reused in other types of projects, including client-side app models.

  • If we are not using SQL Server, we will need to create this class library for SQLite.

  • If we are using SQL Server, then we can create both a class library for SQLite and one for SQL Server and then switch between them as we choose.

We will automatically generate some entity models using the EF Core command-line tool:

Step 1: Use your preferred code editor to create a new project, as defined in the following list:

  • Project template: Class Library or classlib

  • Project file and folder: Northwind.Common.EntityModels.Sqlite

  • Workspace or solution file and folder: PracticalApps

Step 2: In the Northwind.Common.EntityModels.Sqlite project, add package references for the SQLite database provider and EF Core design-time support, as shown in the following markup:

Get hands-on with 1400+ tech skills courses.