Database first create entity framework 6.1.1 model using system.data.sqlite 1.0.93

Question

I have a project that I just updated using nuget. This updated entity framework from 6.1 to 6.1.1, and it updated sqlite to 1.0.93. I wanted to update my model from my latest database. I did the steps of 1) Model from Database 2) Select sqlite database 3) Generate

After the generation I received the following error message shown below. I have the 1.0.93 design time components install. Does anyone know what exactly is causing this error. The project references and versions all match the versions shown above.

Error Message:

Your project references the latest Entity Framework; however and Entity Framework database provider compatible with this version could not be found for you data connection.

Update:

I ended up installing 6.1.0 manually using the package manager console

Install-Package EntityFramework -Version 6.1.0

Then in my csporj files replacing

packagesEntityFramework.6.1.1

with

packagesEntityFramework.6.1.0

I tried creating EF Designer from Database and Code First from Database but I am still getting the same error.

UPDATE

I have followed the instructions Tom has provided, and thanks time for taking the time to respond in depth it is appreciated. But I cannot get the entity framework designer to work with SQLite 1.0.93. What I have found:

1) When I add the SQLite data source from the tools menu as Tom describes I see the SQLite data provider.

2) But when I restart visual studio the data source is not connected

3) The data source can be refreshed and it is valid after restart

4) Add new data item but SQLite is not listed as a provider

I have double checked the registry and EF6 is appended to the invariant name, the SQLite dlls are registered in the GAC. I will try to see what else I can find but at this point I am not sure what to look for. As a side note I have tried dotConnect and it does not work either with EF 6.1.1.

Update 2

Does anyone know if the Entity Framework Designer has a logging option to find out what might be happening?

Answer 1

[ UPDATE : for an easier solution that works with Visual Studio 2013 Update 4 (Pro and Ultimate) and recent versions of Sqlite Providers and EF, look at the solution 'broslav' posted below. Don't know if it works with Express editions... ]

Ok, this is a suggestion. This does NOT work for Visual Studio Express editions. Also, I'm not sure how to get it to work for an update of an existing project. And it is an absolute pain in the butt. I haven't tested if everything is required, but this should work:

First, do NOT use the NuGet package for SQLite, but download the assembly installer from here: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Install the Setups for 32-bit Windows (.NET Framework 4.5.1) installer: sqlite-netFx451-setup-bundle-x86-2013-1.0.93.0.exe (10.00 MiB) (NOT the 64-bit version). This is the only one with the designer.

Install and select to install in the GAC and install the Designer for VS13.

For the following, the exact order is important!

I made a video of these steps, see: http://vimeo.com/103372740

Create a project in VS13. Target the build to framework 4.5.1 and x86 explicitly. Save and build.

Next, install the latest EF 6 package (6.1.1) from NuGet. Save and build.

Manually add references to the installed SQLite assemblies (including the Designer) , under the Reference Manager under Assemblies you can find the Extensions option for selecting the four added assemblies in the GAC : System.Data.SQLite Core + Designer + for Entity Framework + for LINQ.. Save and build.

Then create a connection to your database via Connect to Database under Tools. Before you do anything else Save and Build and then RESTART Visual Studio. DO NOT DO ANYTHING ELSE before starting the ADO.NET Entity Data Model wizard, so DO NOT refresh the database connection.

Add an ADO.NET Entity Data Model, select Generate from Database. Your database connection will show up in the drop-down list. Keep it there, but select to create a New Connection anyway and select the exact same database again (as if you are creating a new connection). It sounds silly, but it is crucial, see image below...

When I actually Added an ADO.NET Entity Data Model this way it did present the annoying error, but the Next button is selectable and everything worked regardless (amazingly)!

Designer works, retrieving data and writing data works.

Haven't tested deployment on another machine though... I'm using Win7 64-bit.

CHANGED UPDATE: To get the correct configuration to actually access the database, you have to install the System.Data.SQLite.EF6 package from NuGet after doing all of the above, then add <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" /> to the App.Config and remove the other providers and remove everything in between <system.data></system.data>, otherwise you'll get some exception. But note that this means that every time you want to update the EDMX model, you have to change the App.Config invariantName="System.Data.SQLite" to invariantName="System.Data.SQLite.EF6" and vice versa.

** Is this getting ridiculous? Yes, it is! And it gave me quite a headache... **