Entity Framework Support in ADO.NET Provider for SQLite

The latest ADO.NET evolution focuses on raising the level of abstraction from the logical (relational) level to the conceptual (entity) level. For this purpose Microsoft introduces the Entity Framework, designed to simplify data - object conversion and embed data access queries into program code.

The Devart dotConnect product line supports the ADO.NET Entity Framework for the following databases: Oracle, MySQL, PostgreSQL, SQLite, DB2, and the following cloud applications: Salesforce, Dynamics CRM, SugarCRM, Zoho CRM, MailChimp, Salesforce Marketing Cloud (formerly, ExactTarget), Bigcommerce, Magento, QuickBooks, and FreshBooks.

What is ADO.NET Entity Framework?

The Entity Framework is a set of technologies in ADO.NET that support development of data-oriented software applications. The Entity Framework is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits:

Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships.
Applications are freed from hard-coded dependencies on a particular data engine or storage schema.
Mappings between the conceptual model and the storage-specific schema can change without changing the application code.
Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.
Multiple conceptual models can be mapped to a single storage schema.
Language-integrated query support provides compile-time syntax validation for queries against a conceptual model.

Entity Framework Support Key Features

Conceptual approach to enterprise development
High performance
Wide support for LINQ to Entities
Wide support for EntitySQL
Full CRUD (Create, Retrieve, Update, Delete) support
Wide support for server data types
Visual Studio EDM Wizard support(reverse engineering of database objects to entity model)
Visual Studio LightSwitch support
Generate Database Wizard (Model-First) support
Configurable Code-First CreateDatabase/DatabaseExists/DropDatabase functionality support
Support for Code-First Migrations with database-specific features
Support for database-specific system functions in LINQ to Entities and Entity SQL
Full-text search support
Batch updates
Entity Framework provider configuration

Using the Entity Data Provider

Use of dotConnect for SQLite in the Entity Framework is similar to use of standard SqlClient. Generally it conforms to the following scheme (for Entity Framework v1 - v6):

Open your project.
Add new item ADO.NET Entity Data Model to your project and specify its name.
Choose Generate from database.
Choose the dotConnect you use as data provider and specify the connection string.
Select tables that are actually used in your project and click Generate to finish the process. If the selected tables contain foreign keys, they will be reflected in the generated models as well.
Specify a name for the model namespace. Note that this name should be different from the database (schema) name.

For your convenience there is a brief Entity Framework Tutorial that you can use to get acquainted with the technology. You can also take a look at Entity Framework samples available with the product, or download separate Entity Framework Query Samples package. The latter is a standard Microsoft demo with added Oracle/MySQL/PostgreSQL/SQLite connectivity.

Using the Entity Data Provider with Entity Framework v6

Since there are no global Entity Framework provider registration for Entity Framework v6, you should register our provider manually in the config file of your project. For this you should add the following line:

<provider invariantName="Devart.Data.SQLite" type="Devart.Data.SQLite.Entity.SQLiteEntityProviderServices, 
Devart.Data.SQLite.Entity.EF6, Version=5.1.26.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />

to the entityFramework -> providers section.

<entityFramework>
    <providers>
      <provider invariantName="Devart.Data.SQLite" type="Devart.Data.SQLite.Entity.SQLiteEntityProviderServices, 
      Devart.Data.SQLite.Entity.EF6, Version=5.1.26.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </providers>
</entityFramework>

Note: replace 5.1.26.0 with the actual assembly version.

If it suits for your project, you may omit the exact assembly version:

<entityFramework>
    <providers>
        <provider invariantName="Devart.Data.SQLite" type="Devart.Data.SQLite.Entity.SQLiteEntityProviderServices, Devart.Data.SQLite.Entity.EF6" />
    </providers>
</entityFramework>

This section appears in the project config file only after you add the NuGet EntityFramework package either explicitly, by executing the "install-package EntityFramework" command, or implicitly, by adding an Entity Framework v6 model for SQL Server via EDM Wizard.

Using the Entity Data Provider with Entity Framework Core

dotConnect for SQLite currently supports Entity Framework Core. Entity Framework Core is supported for the Full .NET Framework platform of version 4.5.1 and higher and for .NET Core. Dynamic database object creation (tables/FK/PK/indexes/triggers/sequences) based on an Entity Framework model is supported. See our Entity Framework Core Code-First Tutorial for Full .NET Framework and for .NET Core.

Database-First via Package Manager Console is supported. See our Entity Framework Core Database-First Tutorial for Full .NET Framework and for .NET Core.

Please note that only a part of Entity Framework provider configuration settings is supported for Entity Framework Core. Also, not all Entity Framework v6 features are supported because Entity Framework Core does not support a lot of Entity Framework v6 features itself.

In order to use Entity Framework Core for Full .NET Framework, you need Visual Studio 2013 or higher. For .NET Core you need Visual Studio 2017 or higher.

In order to register an Entity Framework provider and set a connection string, you need to override the OnConfiguring method of your DbContext class descendant.

[C#]

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
  
  optionsBuilder.UseSQLite("DataSource=mydatabase.db;");
}

[Visual Basic]

Protected Overrides Sub OnConfiguring(optionsBuilder As DbContextOptionsBuilder)

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

The mapping supported by Entity Framework Core is described in details in the corresponding section of Entity Framework documentation. If you plan to support several data sources for one base Entity Framework model, that is mapped to tables and columns named differently in different data sources, you can use special provider-specific extension methods in fluent mapping in order to support specific mapping for each data source simultaneously. dotConnect for SQLite provides the following extension methods:

ForSQLiteToTable() - specifies the table name.
ForSQLiteHasColumnName() - specifies the column name.
ForSQLiteHasColumnType() - specifies the column type.
ForSQLiteHasName() - specifies the the name of the index and primary key.
ForSQLiteHasConstraintName() - specifies the foreign key.
ForSQLiteHasDefaultValueSql() - specifies the column default value.

Deploying an Entity Framework Project

When deploying Entity Framework projects, it is necessary to:

Check if the required assemblies Devat.Data.dll, Devart.Data.SQLite.dll, and Devart.Data.SQLite.Entity.EF<Version>.dll are available for your application. The <Version> here is the version of Entity Framework used in your application. For Entity Framework v1 the assembly is called Devart.Data.SQLite.Entity.EF1.dll "1", for Entity Framework v4 and v5 it is Devart.Data.SQLite.Entity.EF4.dll, for Entity Framework v6 it is Devart.Data.SQLite.Entity.EF6.dll. For Entity Framework Core it is Devart.Data.SQLite.Entity.EFCore.dll. The assembly should reside in the application's folder or in GAC.

Please note that there are three versions of Devart.Data.SQLite.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 dotConnect for SQLite installation folder.
If you use Code-First Migrations feature of Entity Framework v4 or Entity Framework v5, you also need to check if the Devart.Data.SQLite.Entity.Migrations.EF4.dll assembly is available for your application. For Entity Framework v6 this assembly is not needed.
For Entity Framework v1 - v6, you need to inform your environment about the provider factory existence (add the entry to the DbProviderFactories section as it is described above).

<system.data>
  <DbProviderFactories>
	<remove invariant="Devart.Data.SQLite" />
	<add name="dotConnect for SQLite" invariant="Devart.Data.SQLite"
	 description="Devart dotConnect for SQLite"
	 type="Devart.Data.SQLite.SQLiteProviderFactory, Devart.Data.SQLite,
       Version=5.1.26.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
  </DbProviderFactories>
</system.data>

Replace 5.1.26.0 here with your actual version.

You can find the necessary assemblies in the Entity folder in the provider installation folder, in the subfolder, corresponding to the Entity Framework version necessary.

If you are registering the provider in the machine.config file, please take into account the Entity Framework version. Entity Framework v1 - specific applications require an entry in the .NET Framework 2.0 configuration file, and Entity Framework v4 and later - specific applications should be registered in the .NET Framework 4.0 configuration file. For Entity Framework Core there is no need to register the provider in the machine.config file.

You can also read Micrsoft's Deployment Considerations on ADO.NET Entity Framework.

Visual Model Creation

dotConnects for MySQL, Oracle, PostgreSQL, SQLite, DB2, and Salesforce are tightly integrated with Visual Studio Entity Data Model Wizard for generating model definitions. dotConnects also include several demo projects that show how to use the product in conceptual programming against data.

Moreover, dotConnect providers have a bundled visual ORM model designer - Entity Developer . Entity Developer provides far more advanced model editing functionality than standard Visual Studio EDM Designer. It provides better support for Model-First and Database-First development wider mapping support, and powerful template-based code generation. Visit this webpage for more information on Entity Developer benefits.

dotConnect providers also support Visual Studio LightSwitch, so you can develop LightSwitch applications with our providers. You can find a tutorial on creating a simple LightSwitch application using dotConnect for SQLite here: Tutorial: How to Connect Visual Studio LightSwitch to SQLite with dotConnect for SQLite Data Provider .

You can get more information at our website.

All the provider documentation: dotConnect for SQLite Documentation
The Entity Framework section: https://docs.devart.com/dotconnect/sqlite/EFSupport.html
The blog articles dedicated to Entity Framework: https://blog.devart.com/tag/entity-framework

Compatibility and Requirements

dotConnects for Oracle, MySQL, PostgreSQL, SQLite, and DB2 are compatible with the latest release version of ADO.NET Entity Framework and Visual Studio. The following server versions are required for Entity Framework to function properly:

Oracle: 9.2.0.4 or higher
MySQL: 5.0 or higher
PostgreSQL: 8.0 or higher
SQLite: any supported version
DB2: 9 and higher

Migration between ADO.NET Entity Data Model (.edmx) and Devart Entity Model (.edml)

To migrate your ADO.NET Entity Data Model to Devart Entity Model, change the extension of the model file to edml (edmx -> edml), set the Build Action property of your model to "DevartEntityDeploy", and Custom Tool to "DevartEfGenerator". After that the model can be edited with the Devart Entity Developer.

To migrate your Devart Entity Model to ADO.NET Entity Data Model, change the model file extension (edml ->edmx), set the Build Action property of your model to "EntityDeploy", and Custom Tool to "EntityModelCodeGenerator". After that the model can be edited with Entity Designer.

Both *.edml and *.edmx can be opened and edited manually using XML Editor.

Back to list