Entity Framework Core Code-First Tutorial for SQLite

This tutorial guides you through the process of creating a simple application powered by Entity Framework Core designer you can use either. This application will create tables in the database based on the model in run-time, fill them with sample data, and execute queries.

Download Now dotConnect for SQLite

Requirements

If you want to target Entity Framework Core 2.2, this tutorial requires the following:

  • Visual Studio 2017 or higher
  • .NET Core SDK 2.0
  • .NET Framework 4.6.1 or higher

If you want to target Entity Framework Core 3.1, this tutorial requires the following:

  • Visual Studio 2019 or higher
  • .NET Framework 4.6.1 or higher

If you want to target Entity Framework Core 2.2, this tutorial requires the following:

  • Visual Studio 2017 or higher
  • .NET Framework 4.6.1 or higher

If you want to target Entity Framework Core 1.1, this tutorial requires the following:

  • Visual Studio 2013 or higher
  • .NET Framework 4.5.1 or higher

Note that Entity Framework support is available only in Professional Edition of dotConnect for SQLite.

Creating Application

To create the sample application, let's perform the following steps:

  1. Create a new console application.
  2. For Entity Framework Core 2.2 or 3.1, ensure that you are targeting .NET Framework 4.6.1 or later. For Entity Framework Core 1.1, ensure that you are targeting .NET Framework 4.5.1 or later.
  3. For Entity Framework Core 2.2 or 3.1, ensure that you are targeting .NET Framework 4.6.1 or later. For Entity Framework Core 1.1, ensure that you are targeting .NET Framework 4.5.1 or later.
  4. Install the Entity Framework Core NuGet package by executing the following command in the Package Manager Console (for Entity Framework Core 3.1): 
    Install-Package Microsoft.EntityFrameworkCore.Relational


    For Entity Framework Core 2.2, the command will be the following:
    Install-Package Microsoft.EntityFrameworkCore.Relational -Version 2.2.6
    5. For Entity Framework Core 1.1, the command will be the following:
    Install-Package Microsoft.EntityFrameworkCore.Relational -Version 1.1.4

  5. Additionally you need to add references to the following assemblies to your project:

    • Devart.Data.dll
    • Devart.Data.SQLite.dll
    • Devart.Data.SQLite.Entity.EFCore.dll

    Please note that there are three versions of Devart.Data.Oracle.Entity.EFCore.dll assemblies for different Entity Framework Core versions - 1.1, 2.2, and 3.1. They are located respectively in \Entity\EFCore, \Entity\EFCore2, and \Entity\EFCore3 subfolders of the [!ProductName] installation folder.

  6. Create a DbContext descendant.

    [C#] 
    using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
public class MyDbContext : DbContext {
 
}
    [Visual Basic] 
    Imports Microsoft.EntityFrameworkCore
Imports System.Collections.Generic
Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema
Imports System.Data
Imports System.IO
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks

Public Class MyDbContext
	Inherits DbContext

End Class

  7. Register Entity Framework Core provider for using with our DbContext and specify the connection string. For this override the OnConfiguring method.

    [C#] 
    public class MyDbContext : DbContext {
 
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {

     optionsBuilder.UseSQLite(@"DataSource=mydatabase.db;");
  } 
}
    [Visual Basic] 
    Public Class MyDbContext
	Inherits DbContext

	Protected Overrides Sub OnConfiguring(optionsBuilder As DbContextOptionsBuilder)

		optionsBuilder.UseSQLite("DataSource=mydatabase.db;")
	End Sub
End Class

  8. Create entity classes, used in the model. If necessary, set DataAnnotation attributes for the classes and properties.

    [C#] 
      [Table("Product")]
  public class Product {
 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long ProductID { get; set; }
 
    [Required]
    [MaxLength(50)]
    public string ProductName { get; set; }
 
    public string UnitName { get; set; }
    public int UnitScale { get; set; }
    public long InStock { get; set; }
    public double Price { get; set; }
    public double DiscontinuedPrice { get; set; }
 
    public virtual ProductCategory Category { get; set; }
    public virtual ICollection<OrderDetail> OrderDetails { get; set; }
  }
 
  [Table("ProductCategory")]
  public class ProductCategory {
 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long CategoryID { get; set; }
 
    [Required]
    [MaxLength(20)]
    public string CategoryName { get; set; }
 
    public virtual ProductCategory ParentCategory { get; set; }
    public virtual ICollection<ProductCategory> ChildCategories { get; set; }
    public virtual ICollection<Product> Products { get; set; }
  }
 
  [Table("Order Details")]
  public class OrderDetail {
 
    [Column(Order = 1)]
    public long OrderID { get; set; }
 
    [Column(Order = 2)]
    public long ProductID { get; set; }
 
    public double Price { get; set; }
    public double Quantity { get; set; }
 
    public virtual Product Product { get; set; }
    public virtual Order Order { get; set; }
  }
 
  [Table("Orders")]
  public class Order {
 
    public Order() {
 
      OrderDate = DateTime.Now;
    }
 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long OrderID { get; set; }
 
    [Required]
    public DateTime OrderDate { get; set; }
 
    public double Freight { get; set; }
    public DateTime? ShipDate { get; set; }
    public Double Discount { get; set; }
 
    public virtual ICollection<OrderDetail> OrderDetails { get; set; }
 
    [InverseProperty("Orders")]
    public virtual PersonContact PersonContact { get; set; }
 
    public virtual Company Company { get; set; }
 
    public virtual Company ShipCompany { get; set; }
  }
 
  [Table("Company")]
  public class Company {
 
    public Company() {
    }
 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long CompanyID { get; set; }
 
    [Required]
    [MaxLength(40)]
    public string CompanyName { get; set; }
 
    [MaxLength(100)]
    public string Web { get; set; }
 
    [MaxLength(50)]
    public string Email { get; set; }
 
    public virtual AddressType Address { get; set; }
 
    [InverseProperty(nameof(Order.Company))]
    public virtual ICollection<Order> Orders { get; set; }
 
    [InverseProperty(nameof(Order.ShipCompany))]
    public virtual ICollection<Order> ShippedOrders { get; set; }
 
    [InverseProperty(nameof(PersonContact.Companies))]
    public virtual PersonContact PrimaryContact { get; set; }
 
    [InverseProperty(nameof(PersonContact.Company))]
    public virtual ICollection<PersonContact> Contacts { get; set; }
 
  }
 
  [Table("Person Contact")]
  public class PersonContact {
 
    public PersonContact() {
    }
 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long ContactID { get; set; }
 
    [MaxLength(8)]
    public string Title { get; set; }
 
    [MaxLength(50)]
    public string FirstName { get; set; }
 
    [MaxLength(50)]
    public string MiddleName { get; set; }
 
    [MaxLength(50)]
    public string LastName { get; set; }
 
    [MaxLength(25)]
    public string HomePhone { get; set; }
 
    [MaxLength(25)]
    public string MobilePhone { get; set; }
 
    public virtual AddressType Address { get; set; }
 
    public virtual ICollection<Order> Orders { get; set; }
    public virtual Company Company { get; set; }
    public virtual ICollection<Company> Companies { get; set; }
  }
 
  [Table("AddressType")]
  public class AddressType {
 
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }
 
    [MaxLength(120)]
    public string AddressTitle { get; set; }
 
    [MaxLength(60)]
    public string Address { get; set; }
 
    [MaxLength(30)]
    public string City { get; set; }
 
    [MaxLength(20)]
    public string Region { get; set; }
 
    [MaxLength(15)]
    public string PostalCode { get; set; }
 
    [MaxLength(20)]
    public string Country { get; set; }
 
    [MaxLength(25)]
    public string Phone { get; set; }
 
    [MaxLength(25)]
    public string Fax { get; set; }
  }
    [Visual Basic] 
    <Table("Product")> _
Public Class Product

	<Key> _
	<DatabaseGenerated(DatabaseGeneratedOption.Identity)> _
	Public Property ProductID() As Long
		Get
			Return m_ProductID
		End Get
		Set
			m_ProductID = Value
		End Set
	End Property
	Private m_ProductID As Long

	<Required> _
	<MaxLength(50)> _
	Public Property ProductName() As String
		Get
			Return m_ProductName
		End Get
		Set
			m_ProductName = Value
		End Set
	End Property
	Private m_ProductName As String

	Public Property UnitName() As String
		Get
			Return m_UnitName
		End Get
		Set
			m_UnitName = Value
		End Set
	End Property
	Private m_UnitName As String
	Public Property UnitScale() As Integer
		Get
			Return m_UnitScale
		End Get
		Set
			m_UnitScale = Value
		End Set
	End Property
	Private m_UnitScale As Integer
	Public Property InStock() As Long
		Get
			Return m_InStock
		End Get
		Set
			m_InStock = Value
		End Set
	End Property
	Private m_InStock As Long
	Public Property Price() As Double
		Get
			Return m_Price
		End Get
		Set
			m_Price = Value
		End Set
	End Property
	Private m_Price As Double
	Public Property DiscontinuedPrice() As Double
		Get
			Return m_DiscontinuedPrice
		End Get
		Set
			m_DiscontinuedPrice = Value
		End Set
	End Property
	Private m_DiscontinuedPrice As Double

	Public Overridable Property Category() As ProductCategory
		Get
			Return m_Category
		End Get
		Set
			m_Category = Value
		End Set
	End Property
	Private Overridable m_Category As ProductCategory
	Public Overridable Property OrderDetails() As ICollection(Of OrderDetail)
		Get
			Return m_OrderDetails
		End Get
		Set
			m_OrderDetails = Value
		End Set
	End Property
	Private Overridable m_OrderDetails As ICollection(Of OrderDetail)
End Class

<Table("ProductCategory")> _
Public Class ProductCategory

	<Key> _
	<DatabaseGenerated(DatabaseGeneratedOption.Identity)> _
	Public Property CategoryID() As Long
		Get
			Return m_CategoryID
		End Get
		Set
			m_CategoryID = Value
		End Set
	End Property
	Private m_CategoryID As Long

	<Required> _
	<MaxLength(20)> _
	Public Property CategoryName() As String
		Get
			Return m_CategoryName
		End Get
		Set
			m_CategoryName = Value
		End Set
	End Property
	Private m_CategoryName As String

	Public Overridable Property ParentCategory() As ProductCategory
		Get
			Return m_ParentCategory
		End Get
		Set
			m_ParentCategory = Value
		End Set
	End Property
	Private Overridable m_ParentCategory As ProductCategory
	Public Overridable Property ChildCategories() As ICollection(Of ProductCategory)
		Get
			Return m_ChildCategories
		End Get
		Set
			m_ChildCategories = Value
		End Set
	End Property
	Private Overridable m_ChildCategories As ICollection(Of ProductCategory)
	Public Overridable Property Products() As ICollection(Of Product)
		Get
			Return m_Products
		End Get
		Set
			m_Products = Value
		End Set
	End Property
	Private Overridable m_Products As ICollection(Of Product)
End Class

<Table("Order Details")> _
Public Class OrderDetail

	<Column(Order := 1)> _
	Public Property OrderID() As Long
		Get
			Return m_OrderID
		End Get
		Set
			m_OrderID = Value
		End Set
	End Property
	Private m_OrderID As Long

	<Column(Order := 2)> _
	Public Property ProductID() As Long
		Get
			Return m_ProductID
		End Get
		Set
			m_ProductID = Value
		End Set
	End Property
	Private m_ProductID As Long

	Public Property Price() As Double
		Get
			Return m_Price
		End Get
		Set
			m_Price = Value
		End Set
	End Property
	Private m_Price As Double
	Public Property Quantity() As Double
		Get
			Return m_Quantity
		End Get
		Set
			m_Quantity = Value
		End Set
	End Property
	Private m_Quantity As Double

	Public Overridable Property Product() As Product
		Get
			Return m_Product
		End Get
		Set
			m_Product = Value
		End Set
	End Property
	Private Overridable m_Product As Product
	Public Overridable Property Order() As Order
		Get
			Return m_Order
		End Get
		Set
			m_Order = Value
		End Set
	End Property
	Private Overridable m_Order As Order
End Class

<Table("Orders")> _
Public Class Order

	Public Sub New()

		OrderDate = DateTime.Now
	End Sub

	<Key> _
	<DatabaseGenerated(DatabaseGeneratedOption.Identity)> _
	Public Property OrderID() As Long
		Get
			Return m_OrderID
		End Get
		Set
			m_OrderID = Value
		End Set
	End Property
	Private m_OrderID As Long

	<Required> _
	Public Property OrderDate() As DateTime
		Get
			Return m_OrderDate
		End Get
		Set
			m_OrderDate = Value
		End Set
	End Property
	Private m_OrderDate As DateTime

	Public Property Freight() As Double
		Get
			Return m_Freight
		End Get
		Set
			m_Freight = Value
		End Set
	End Property
	Private m_Freight As Double
	Public Property ShipDate() As System.Nullable(Of DateTime)
		Get
			Return m_ShipDate
		End Get
		Set
			m_ShipDate = Value
		End Set
	End Property
	Private m_ShipDate As System.Nullable(Of DateTime)
	Public Property Discount() As [Double]
		Get
			Return m_Discount
		End Get
		Set
			m_Discount = Value
		End Set
	End Property
	Private m_Discount As [Double]

	Public Overridable Property OrderDetails() As ICollection(Of OrderDetail)
		Get
			Return m_OrderDetails
		End Get
		Set
			m_OrderDetails = Value
		End Set
	End Property
	Private Overridable m_OrderDetails As ICollection(Of OrderDetail)

	<InverseProperty("Orders")> _
	Public Overridable Property PersonContact() As PersonContact
		Get
			Return m_PersonContact
		End Get
		Set
			m_PersonContact = Value
		End Set
	End Property
	Private Overridable m_PersonContact As PersonContact

	Public Overridable Property Company() As Company
		Get
			Return m_Company
		End Get
		Set
			m_Company = Value
		End Set
	End Property
	Private Overridable m_Company As Company

	Public Overridable Property ShipCompany() As Company
		Get
			Return m_ShipCompany
		End Get
		Set
			m_ShipCompany = Value
		End Set
	End Property
	Private Overridable m_ShipCompany As Company
End Class

<Table("Company")> _
Public Class Company

	Public Sub New()
	End Sub

	<Key> _
	<DatabaseGenerated(DatabaseGeneratedOption.Identity)> _
	Public Property CompanyID() As Long
		Get
			Return m_CompanyID
		End Get
		Set
			m_CompanyID = Value
		End Set
	End Property
	Private m_CompanyID As Long

	<Required> _
	<MaxLength(40)> _
	Public Property CompanyName() As String
		Get
			Return m_CompanyName
		End Get
		Set
			m_CompanyName = Value
		End Set
	End Property
	Private m_CompanyName As String

	<MaxLength(100)> _
	Public Property Web() As String
		Get
			Return m_Web
		End Get
		Set
			m_Web = Value
		End Set
	End Property
	Private m_Web As String

	<MaxLength(50)> _
	Public Property Email() As String
		Get
			Return m_Email
		End Get
		Set
			m_Email = Value
		End Set
	End Property
	Private m_Email As String

	Public Overridable Property Address() As AddressType
		Get
			Return m_Address
		End Get
		Set
			m_Address = Value
		End Set
	End Property
	Private Overridable m_Address As AddressType

	<InverseProperty(nameof(Order.Company))> _
	Public Overridable Property Orders() As ICollection(Of Order)
		Get
			Return m_Orders
		End Get
		Set
			m_Orders = Value
		End Set
	End Property
	Private Overridable m_Orders As ICollection(Of Order)

	<InverseProperty(nameof(Order.ShipCompany))> _
	Public Overridable Property ShippedOrders() As ICollection(Of Order)
		Get
			Return m_ShippedOrders
		End Get
		Set
			m_ShippedOrders = Value
		End Set
	End Property
	Private Overridable m_ShippedOrders As ICollection(Of Order)

	<InverseProperty(nameof(PersonContact.Companies))> _
	Public Overridable Property PrimaryContact() As PersonContact
		Get
			Return m_PrimaryContact
		End Get
		Set
			m_PrimaryContact = Value
		End Set
	End Property
	Private Overridable m_PrimaryContact As PersonContact

	<InverseProperty(nameof(PersonContact.Company))> _
	Public Overridable Property Contacts() As ICollection(Of PersonContact)
		Get
			Return m_Contacts
		End Get
		Set
			m_Contacts = Value
		End Set
	End Property
	Private Overridable m_Contacts As ICollection(Of PersonContact)

End Class

<Table("Person Contact")> _
Public Class PersonContact

	Public Sub New()
	End Sub

	<Key> _
	<DatabaseGenerated(DatabaseGeneratedOption.Identity)> _
	Public Property ContactID() As Long
		Get
			Return m_ContactID
		End Get
		Set
			m_ContactID = Value
		End Set
	End Property
	Private m_ContactID As Long

	<MaxLength(8)> _
	Public Property Title() As String
		Get
			Return m_Title
		End Get
		Set
			m_Title = Value
		End Set
	End Property
	Private m_Title As String

	<MaxLength(50)> _
	Public Property FirstName() As String
		Get
			Return m_FirstName
		End Get
		Set
			m_FirstName = Value
		End Set
	End Property
	Private m_FirstName As String

	<MaxLength(50)> _
	Public Property MiddleName() As String
		Get
			Return m_MiddleName
		End Get
		Set
			m_MiddleName = Value
		End Set
	End Property
	Private m_MiddleName As String

	<MaxLength(50)> _
	Public Property LastName() As String
		Get
			Return m_LastName
		End Get
		Set
			m_LastName = Value
		End Set
	End Property
	Private m_LastName As String

	<MaxLength(25)> _
	Public Property HomePhone() As String
		Get
			Return m_HomePhone
		End Get
		Set
			m_HomePhone = Value
		End Set
	End Property
	Private m_HomePhone As String

	<MaxLength(25)> _
	Public Property MobilePhone() As String
		Get
			Return m_MobilePhone
		End Get
		Set
			m_MobilePhone = Value
		End Set
	End Property
	Private m_MobilePhone As String

	Public Overridable Property Address() As ddressType
		Get
			Return m_Address
		End Get
		Set
			m_Address = Value
		End Set
	End Property
	Private Overridable m_Address As ddressType

	Public Overridable Property Orders() As ICollection(Of Order)
		Get
			Return m_Orders
		End Get
		Set
			m_Orders = Value
		End Set
	End Property
	Private Overridable m_Orders As ICollection(Of Order)
	Public Overridable Property Company() As Company
		Get
			Return m_Company
		End Get
		Set
			m_Company = Value
		End Set
	End Property
	Private Overridable m_Company As Company
	Public Overridable Property Companies() As ICollection(Of Company)
		Get
			Return m_Companies
		End Get
		Set
			m_Companies = Value
		End Set
	End Property
	Private Overridable m_Companies As ICollection(Of Company)
End Class

<Table("AddressType")> _
Public Class AddressType

	<Key> _
	<DatabaseGenerated(DatabaseGeneratedOption.Identity)> _
	Public Property Id() As Long
		Get
			Return m_Id
		End Get
		Set
			m_Id = Value
		End Set
	End Property
	Private m_Id As Long

	<MaxLength(120)> _
	Public Property AddressTitle() As String
		Get
			Return m_AddressTitle
		End Get
		Set
			m_AddressTitle = Value
		End Set
	End Property
	Private m_AddressTitle As String

	<MaxLength(60)> _
	Public Property Address() As String
		Get
			Return m_Address
		End Get
		Set
			m_Address = Value
		End Set
	End Property
	Private m_Address As String

	<MaxLength(30)> _
	Public Property City() As String
		Get
			Return m_City
		End Get
		Set
			m_City = Value
		End Set
	End Property
	Private m_City As String

	<MaxLength(20)> _
	Public Property Region() As String
		Get
			Return m_Region
		End Get
		Set
			m_Region = Value
		End Set
	End Property
	Private m_Region As String

	<MaxLength(15)> _
	Public Property PostalCode() As String
		Get
			Return m_PostalCode
		End Get
		Set
			m_PostalCode = Value
		End Set
	End Property
	Private m_PostalCode As String

	<MaxLength(20)> _
	Public Property Country() As String
		Get
			Return m_Country
		End Get
		Set
			m_Country = Value
		End Set
	End Property
	Private m_Country As String

	<MaxLength(25)> _
	Public Property Phone() As String
		Get
			Return m_Phone
		End Get
		Set
			m_Phone = Value
		End Set
	End Property
	Private m_Phone As String

	<MaxLength(25)> _
	Public Property Fax() As String
		Get
			Return m_Fax
		End Get
		Set
			m_Fax = Value
		End Set
	End Property
	Private m_Fax As String
End Class

  9. Add our classes to the DbContext descendant as DbSet properties. If necessary, you can also write fluent mapping, by overriding the OnModelCreating method.

    [C#] 
    public class MyDbContext : DbContext {
 
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
 
    optionsBuilder.UseSQLite(@"DataSource=mydatabase.db;");
  }
 
  protected override void OnModelCreating(ModelBuilder modelBuilder) {
 
    modelBuilder.Entity<OrderDetail>()
      .HasKey(p => new { p.OrderID, p.ProductID });
  } 
  
  public DbSet<Product> Products { get; set; }
  public DbSet<ProductCategory> ProductCategories { get; set; }
  public DbSet<OrderDetail> OrderDetails { get; set; }
  public DbSet<Order> Orders { get; set; }
  public DbSet<Company> Companies { get; set; }
  public DbSet<PersonContact> PersonContacts { get; set; }
}
    [Visual Basic] 
    Public Class MyDbContext
	Inherits DbContext

	Protected Overrides Sub OnConfiguring(optionsBuilder As DbContextOptionsBuilder)

		optionsBuilder.UseSQLite("DataSource=mydatabase.db;")
	End Sub

	Protected Overrides Sub OnModelCreating(modelBuilder As ModelBuilder)

		modelBuilder.Entity(Of OrderDetail)().HasKey(Function(p) New From { _
			p.OrderID, _
			p.ProductID _
		})
	End Sub

	Public Property Products() As DbSet(Of Product)
		Get
			Return m_Products
		End Get
		Set
			m_Products = Value
		End Set
	End Property
	Private m_Products As DbSet(Of Product)
	Public Property ProductCategories() As DbSet(Of ProductCategory)
		Get
			Return m_ProductCategories
		End Get
		Set
			m_ProductCategories = Value
		End Set
	End Property
	Private m_ProductCategories As DbSet(Of ProductCategory)
	Public Property OrderDetails() As DbSet(Of OrderDetail)
		Get
			Return m_OrderDetails
		End Get
		Set
			m_OrderDetails = Value
		End Set
	End Property
	Private m_OrderDetails As DbSet(Of OrderDetail)
	Public Property Orders() As DbSet(Of Order)
		Get
			Return m_Orders
		End Get
		Set
			m_Orders = Value
		End Set
	End Property
	Private m_Orders As DbSet(Of Order)
	Public Property Companies() As DbSet(Of Company)
		Get
			Return m_Companies
		End Get
		Set
			m_Companies = Value
		End Set
	End Property
	Private m_Companies As DbSet(Of Company)
	Public Property PersonContacts() As DbSet(Of PersonContact)
		Get
			Return m_PersonContacts
		End Get
		Set
			m_PersonContacts = Value
		End Set
	End Property
	Private m_PersonContacts As DbSet(Of PersonContact)
End Class

  10. Now let's choose how to create the database. We can generate Code-First Migrations. Or, for test purposes, we can implement the analogue of Entity Framework 6 initialization strategy DropCreateDatabaseAlways.

    [C#] 
      public static class MyDbContextSeeder {
 
    public static void Seed(MyDbContext context) {
 
      context.Database.EnsureDeleted();
      context.Database.EnsureCreated();
 
      context.ProductCategories.Add(new ProductCategory() {
        CategoryName = "prose"
      });
      context.ProductCategories.Add(new ProductCategory() {
        CategoryName = "novel"
      });
      context.ProductCategories.Add(new ProductCategory() { 
        CategoryName = "poetry",
        ParentCategory = context.ProductCategories.Local.Single(p => p.CategoryName == "novel")
      });
      context.ProductCategories.Add(new ProductCategory() {
        CategoryName = "detective story"
      });
      context.ProductCategories.Add(new ProductCategory() { 
        CategoryName = "fantasy",
        ParentCategory = context.ProductCategories.Local.Single(p => p.CategoryName == "novel")
      });
      context.ProductCategories.Add(new ProductCategory() { 
        CategoryName = "pop art",
        ParentCategory = context.ProductCategories.Local.Single(p => p.CategoryName == "fantasy")
      });
      context.ProductCategories.Add(new ProductCategory() { 
        CategoryName = "textbook"
      });
      context.ProductCategories.Add(new ProductCategory() { 
        CategoryName = "research book",
        ParentCategory = context.ProductCategories.Local.Single(p => p.CategoryName == "textbook")
      });
      context.ProductCategories.Add(new ProductCategory() { 
        CategoryName = "poem",
        ParentCategory = context.ProductCategories.Local.Single(p => p.CategoryName == "novel")
      });
      context.ProductCategories.Add(new ProductCategory() { 
        CategoryName = "collection",
        ParentCategory = context.ProductCategories.Local.Single(p => p.CategoryName == "textbook")
      });
      context.ProductCategories.Add(new ProductCategory() { 
        CategoryName = "dictionary",
        ParentCategory = context.ProductCategories.Local.Single(p => p.CategoryName == "collection")
      });
 
      context.Products.Add(new Product() { 
        ProductName = "Shakespeare W. Shakespeare's dramatische Werke",
        Price = 78,
        Category = context.ProductCategories.Local.Single(p => p.CategoryName == "prose")
      });
      context.Products.Add(new Product() { 
        ProductName = "King Stephen. 'Salem's Lot", 
        Price = 67, 
        Category = context.ProductCategories.Local.Single(p => p.CategoryName == "poetry")
      });
      context.Products.Add(new Product() { 
        ProductName = "Plutarchus. Plutarch's moralia", 
        Price = 89, 
        Category = context.ProductCategories.Local.Single(p => p.CategoryName == "prose")
      });
      context.Products.Add(new Product() { 
        ProductName = "Twain Mark. Ventures of Huckleberry Finn", 
        Price = 34, 
        Category = context.ProductCategories.Local.Single(p => p.CategoryName == "novel")
      });
      context.Products.Add(new Product() { 
        ProductName = "Harrison G. B. England in Shakespeare's day", 
        Price = 540, 
        Category = context.ProductCategories.Local.Single(p => p.CategoryName == "novel")
      });
      context.Products.Add(new Product() { 
        ProductName = "Corkett Anne. The salamander's laughter", 
        Price = 5,
        Category = context.ProductCategories.Local.Single(p => p.CategoryName == "poem")
      });
      context.Products.Add(new Product() { 
        ProductName = "Lightman Alan. Einstein''s dreams", 
        Price = 5,
        Category = context.ProductCategories.Local.Single(p => p.CategoryName == "poem")
      });
 
      context.Companies.Add(new Company() {
        CompanyName = "Borland UK CodeGear Division",
        Web = "support.codegear.com/"
      });
      context.Companies.Add(new Company() {
        CompanyName = "Alfa-Bank",
        Web = "www.alfabank.com"
      });
      context.Companies.Add(new Company() {
        CompanyName = "Pioneer Pole Buildings, Inc.",
        Web = "www.pioneerpolebuildings.com"
      });
      context.Companies.Add(new Company() {
        CompanyName = "Orion Telecoms (Pty) Ltd.",
        Web = "www.oriontele.com"
      });
      context.Companies.Add(new Company() {
        CompanyName = "Orderbase Consulting GmbH",
        Web = "orderbase.de"
      });
 
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2007, 4, 11),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Borland UK CodeGear Division")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2006, 3, 11),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Borland UK CodeGear Division")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2006, 8, 6),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Alfa-Bank")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2004, 7, 6),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Alfa-Bank")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2006, 8, 8),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Alfa-Bank")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2003, 3, 1),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Pioneer Pole Buildings, Inc.")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2005, 8, 6),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Orion Telecoms (Pty) Ltd.")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2006, 8, 1),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Orion Telecoms (Pty) Ltd.")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2007, 7, 1),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Orion Telecoms (Pty) Ltd.")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2007, 2, 6),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Orderbase Consulting GmbH")
      });
      context.Orders.Add(new Order() {
        OrderDate = new DateTime(2007, 8, 1),
        Company = context.Companies.Local.Single(c => c.CompanyName == "Orderbase Consulting GmbH")
      });
 
      context.SaveChanges();
    }
  }
    [Visual Basic] 
    Public NotInheritable Class MyDbContextSeeder
	Private Sub New()
	End Sub

	Public Shared Sub Seed(context As MyDbContext)

		context.Database.EnsureDeleted()
		context.Database.EnsureCreated()

		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "prose" _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "novel" _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "poetry", _
			Key .ParentCategory = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "novel") _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "detective story" _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "fantasy", _
			Key .ParentCategory = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "novel") _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "pop art", _
			Key .ParentCategory = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "fantasy") _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "textbook" _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "research book", _
			Key .ParentCategory = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "textbook") _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "poem", _
			Key .ParentCategory = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "novel") _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "collection", _
			Key .ParentCategory = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "textbook") _
		})
		context.ProductCategories.Add(New ProductCategory() With { _
			Key .CategoryName = "dictionary", _
			Key .ParentCategory = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "collection") _
		})

		context.Products.Add(New Product() With { _
			Key .ProductName = "Shakespeare W. Shakespeare's dramatische Werke", _
			Key .Price = 78, _
			Key .Category = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "prose") _
		})
		context.Products.Add(New Product() With { _
			Key .ProductName = "King Stephen. 'Salem's Lot", _
			Key .Price = 67, _
			Key .Category = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "poetry") _
		})
		context.Products.Add(New Product() With { _
			Key .ProductName = "Plutarchus. Plutarch's moralia", _
			Key .Price = 89, _
			Key .Category = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "prose") _
		})
		context.Products.Add(New Product() With { _
			Key .ProductName = "Twain Mark. Ventures of Huckleberry Finn", _
			Key .Price = 34, _
			Key .Category = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "novel") _
		})
		context.Products.Add(New Product() With { _
			Key .ProductName = "Harrison G. B. England in Shakespeare's day", _
			Key .Price = 540, _
			Key .Category = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "novel") _
		})
		context.Products.Add(New Product() With { _
			Key .ProductName = "Corkett Anne. The salamander's laughter", _
			Key .Price = 5, _
			Key .Category = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "poem") _
		})
		context.Products.Add(New Product() With { _
			Key .ProductName = "Lightman Alan. Einstein''s dreams", _
			Key .Price = 5, _
			Key .Category = context.ProductCategories.Local.[Single](Function(p) p.CategoryName = "poem") _
		})

		context.Companies.Add(New Company() With { _
			Key .CompanyName = "Borland UK CodeGear Division", _
			Key .Web = "support.codegear.com/" _
		})
		context.Companies.Add(New Company() With { _
			Key .CompanyName = "Alfa-Bank", _
			Key .Web = "www.alfabank.com" _
		})
		context.Companies.Add(New Company() With { _
			Key .CompanyName = "Pioneer Pole Buildings, Inc.", _
			Key .Web = "www.pioneerpolebuildings.com" _
		})
		context.Companies.Add(New Company() With { _
			Key .CompanyName = "Orion Telecoms (Pty) Ltd.", _
			Key .Web = "www.oriontele.com" _
		})
		context.Companies.Add(New Company() With { _
			Key .CompanyName = "Orderbase Consulting GmbH", _
			Key .Web = "orderbase.de" _
		})

		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2007, 4, 11), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Borland UK CodeGear Division") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2006, 3, 11), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Borland UK CodeGear Division") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2006, 8, 6), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Alfa-Bank") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2004, 7, 6), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Alfa-Bank") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2006, 8, 8), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Alfa-Bank") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2003, 3, 1), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Pioneer Pole Buildings, Inc.") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2005, 8, 6), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Orion Telecoms (Pty) Ltd.") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2006, 8, 1), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Orion Telecoms (Pty) Ltd.") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2007, 7, 1), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Orion Telecoms (Pty) Ltd.") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2007, 2, 6), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Orderbase Consulting GmbH") _
		})
		context.Orders.Add(New Order() With { _
			Key .OrderDate = New DateTime(2007, 8, 1), _
			Key .Company = context.Companies.Local.[Single](Function(c) c.CompanyName = "Orderbase Consulting GmbH") _
		})

		context.SaveChanges()
	End Sub
End Class

  11. Now let's add code that creates the context, re-creates the database, fills it with the test data, and executes LINQ to Entities queries. Before compiling the following code add the Microsoft.EntityFrameworkCore namespace to the using list (for C#) or to the Imports list (for VB).

    [C#] 
      class Program {
 
    static void Main(string[] args) {
 
      var context = new MyDbContext ();
 
      Console.WriteLine("Entity Framework Core (EF7) Code-First sample");
      Console.WriteLine();
 
      MyDbContextSeeder.Seed(context);
 
      Console.WriteLine("Products with categories");
      Console.WriteLine();
 
      var query = context.Products.Include(p => p.Category)
          .Where(p => p.Price > 20.0)
          .ToList();
 
      Console.WriteLine("{0,-10} | {1,-50} | {2}", "ProductID", "ProductName", "CategoryName");
      Console.WriteLine();
      foreach (var product in query )
        Console.WriteLine("{0,-10} | {1,-50} | {2}", product.ProductID, product.ProductName, product.Category.CategoryName);
 
      Console.ReadKey();
    }
  }
    [Visual Basic] 
    Class Program

	Private Shared Sub Main(args As String())

		Dim context = New MyDbContext()

		Console.WriteLine("Entity Framework Core (EF7) Code-First sample")
		Console.WriteLine()

		MyDbContextSeeder.Seed(context)

		Console.WriteLine("Products with categories")
		Console.WriteLine()

		Dim query = context.Products.Include(Function(p) p.Category).Where(Function(p) p.Price > 20.0).ToList()

		Console.WriteLine("{0,-10} | {1,-50} | {2}", "ProductID", "ProductName", "CategoryName")
		Console.WriteLine()
		For Each product As var In query
			Console.WriteLine("{0,-10} | {1,-50} | {2}", product.ProductID, product.ProductName, product.Category.CategoryName)
		Next

		Console.ReadKey()
	End Sub
End Class

  12. Now we can run the application. It will create tables in the database, fill them with data, execute a query and output its results to the console:

    Entity Framework Core (EF7) Code-First sample

Products with categories

ProductID  | ProductName                                        | CategoryName

1          | Harrison G. B. England in Shakespeare's day        | novel
2          | Twain Mark. Ventures of Huckleberry Finn           | novel
3          | Plutarchus. Plutarch's moralia                     | prose
4          | Shakespeare W. Shakespeare's dramatische Werke     | prose
5          | King Stephen. 'Salem's Lot                         | poetry

Conclusion

This article shows how to create a console application, working with an SQLite database via Entity Framework Core, using dotConnect for SQLite as an Entity Framework Core provider. dotConnect for SQLite is an ADO.NET provider from Devart with support for such ORM solutions as Entity Framework v1 - v6, Entity Framework Core, NHibernate, and Devart's own ORM LinqConnect.

dotConnect for SQLite includes Entity Developer - a powerful visual ORM model designer with support for Database First, Model First, and mixed development approaches and powerful code generation.


Back to list

dotConnect for SQLite

Get enhanced ORM-enabled data provider for SQLite and develop .NET applications working with SQLite data quickly and easily!

Find out more

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.