Entity Framework uses some configurations of Sqlite, entitysqlite

Entity Framework uses some configurations of Sqlite, entitysqlite

Some time ago I tried to use the Entity Framework for Sqlite environment and found some pitfalls. Record them.

I also tried to configure multiple databases, including Sqlite, SQL Server, SQL Server LocalDB, and SQL Server Compact.

 

The demo project structure I created and the packages installed through NuGet:

EFDemo. MultipleDB. UI references the EFDemo. MutipleDB project.

 

1. Exception 1

The Entity Framework provider type 'System. data. SQLite. EF6.SQLiteProviderServices, System. data. SQLite. EF6 'registered in the application config file for the ADO. NET provider with invariant name 'System. data. SQLite. EF6 'could not be loaded. make sure that the assembly-qualified name is used and that the assembly is available to the running application. see http://go.microsoft.com/fwlink? LinkId = 260882 for more information.

I referenced Sqlite-related dll (SEE), but I found that the generated file in EFDemo. MultipleDB. UI does not automatically copy these dll files.

2.

So I copied them manually.

2. Exceptions 2

The exception is gone, and another exception occurs.

The Entity Framework provider type 'System. data. entity. sqlServer. sqlProviderServices, EntityFramework. sqlServer 'registered in the application config file for the ADO. NET provider with invariant name 'System. data. sqlClient 'could not be loaded. make sure that the assembly-qualified name is used and that the assembly is available to the running application. see http://go.microsoft.com/fwlink? LinkId = 260882 for more information.

This time I added such a section in the code without calling it, but let it exist in the Code:

/// <Summary> /// solve the problem that the provider cannot automatically load // (no call is required, just put it here) /// </summary> private static void FixProvidersNotAutoLoadProblem () {var _ = typeof (System. data. SQLite. EF6.SQLiteProviderFactory); var _ = typeof (System. data. entity. sqlServer. sqlProviderServices); var ___ = typeof (System. data. entity. sqlServerCompact. sqlCeProviderServices );}

3. Exceptions 3

With the above method, the exception above is gone, and another new one comes.

Unable to determine the provider name for provider factory of type 'System. Data. SQLite. SQLiteFactory '. Make sure that the ADO. NET provider is installed or registered in the application config.

Sqlite packages are installed through NuGet, but NuGet does not seem to help generate the complete configuration, so you need to complete it yourself.

Here we paste the complete content. Note that the red content is added:

  <system.data>    <DbProviderFactories>      <remove invariant="System.Data.SQLite" />      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/>      <remove invariant="System.Data.SQLite.EF6" />      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />      <remove invariant="System.Data.SqlServerCe.4.0" />      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />    </DbProviderFactories>  </system.data>  <entityFramework>    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">      <parameters>        <parameter value="System.Data.SqlServerCe.4.0" />      </parameters>    </defaultConnectionFactory>    <providers>      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />      <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />    </providers>  </entityFramework>

4. Although Code First is used, System. Data. SQLite. EF6 cannot automatically generate the table structure of the database and must be created manually. (Of course, you can find out if there are other EF providers that support Sqlite Migration)

 

OK. The pitfalls are filled in.

 

5. Set DataDirectory

My Sqlite connection string is written as follows:

<Add name = "BloggingContext_SQLite" connectionString = "Data Source = | DataDirectory | \ Blogging_SQLite.db" providerName = "System. Data. SQLite. EF6"/>

For ease of development, I put the database file in the project, which looks like:

How can I tell the connection string DataDirectory here?

        private static void SetDataDir()         {            DirectoryInfo baseDir = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);            string data_dir = baseDir.FullName;            if ((baseDir.Name.ToLower() == "debug" || baseDir.Name.ToLower() == "release")                && (baseDir.Parent.Name.ToLower() == "bin"))            {                data_dir = Path.Combine(baseDir.Parent.Parent.FullName, "App_Data");            }                        AppDomain.CurrentDomain.SetData("DataDirectory", data_dir);        }

Called during program initialization.

 

Appendix:

The complete code is here