I'm trying to understand how SQlite and entity frameworks interacts. I create a new fresh console project in my visual studio 2013. I install the nuget packet of SQlite 1.0.92. I create a new empty model (edmx) and try to populate it from a static example database (such as northwind.db). After this I get this error: error 0175: the ado.net provider named "System.Data.SQLite.EF6" is not registered on the computer.
Any ideas?
Thank you Lorenzo
After some tests I found a possible solution. Let's start from the beginning:
Download the SQLite ado.net provider from sqlite server (x86 version). You have to manually register in the GAC those three libraries: system.data.sqlite, system.data.sqlite.ef6, system.data.sqlite.designer with gacutil
Start visual studio 2013. Add reference to nuget sqlite 1.0.92.0 (This will add reference to entity framework 6.1 too).
Your app.config will look like this:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<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" />
</DbProviderFactories>
</system.data>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
</providers>
</entityFramework>
<connectionStrings>
Add a new Sqlite connection to the db in your server explorer and test it. Now add and create a new empty ado.net entity model to your project.
This should work... but when your run the application and pick up some records in one of the dbset you will get an exception telling you that there's no ado.net provider registered for your connection (system.data.sqlite).
Go to your config file and add this to you provider section (just copy the previous sqlite line and cut the .ef6 postfix in the invariant name):
<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
Save all and rebuild the application. Now it works but if you go back to the model detail and try to refresh the model from the db you will get a provider exception again. Comment the last provider added and retry. IT WORKS!!!
I think it's due to a conflict between provider names in the syste.data.sqlite and system.data.sqlite.ef6
Despite this I can't do model first (project a model and then create a new database from it) because I get another ado.net provider exception.
Any ideas about that?
Thank you Lorenzo
_________________ UPDATE 04-2014 _________________
After some testing and googling... I read that when you want to use the VS design tools you have to install the right x86 version of the SQLite drivers. These drivers, to work properly with the VS tools, needs to register their version of the System.Data.SQLite in the GAC during the installation process (only after this VS knows where to find the connection provider for the design tools). When you use the model-first approach and you ask the model to create the db instead of use the correct driver registered in you app.config it tryes to open the entity connection with the one registered in GAC and used by the visualmodel creation tool (and wich is not compatible). I think there's no way at the moment to use the VS's entity modelling tool with the SQLite provider.
Lorenzo
