How to get SQLite up and running in Visual Studio?

Question

I'm having major issues trying to connect an existing SQLite database to my C# application. I've installed SQLite bundle for 32 and 64 bit, I've installed SQLite/SQL Server Compact Tools extension, installed SQLite dlls into GAC, added various different provider tags suggested by dozens of blog posts to my app.config, to my 32 bit machine config, to my 64 bit machine config. Does anybody know just what exactly does one have to do to make Visual Studio let me hook up my SQLite DB to my application?

I'm trying to add a new ADO.NET Entity Data Model to my application by using the option "EF Designer from database". I'm either getting hit with "Unable to find the requested .Net Framework Data Provider" and Visual Studio cannot even find my DB in the dialog (even though I can see it in the Server Explorer).

Or, after I've installed an additional ADO.NET SQLite as VS extension, I can see my SQLite database and test connection is sucess ful, but a soon as hit the Next button I get this:

I've installed EF only through the System.Data.Sqlite NuGet package, I'm maximally confused about this whole provider business.

Please HELP! Thanks!!!

Answer 1

Ok, by following a provided helpful link to a detailed guideline I was finally able to get everything up and running. I've done exactly what's described here: https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider

However I'm taking the liberty of providing some additional tips for anybody stumbling over the same issue.

1. I'm still confused about what machine config to manipulate (VS is 32 bit but I'm developing a 64 bit application), so I manipulated both without trying which one matters in that context.
2. The duplicate closing tag for the DbProviderFactories in the machine config that's desribed in the mentioned github page as well as in numerous other blogs might not always be the problem and was not the case on my machine. But if you encounter it, clean it up anyway.
3. If you apply the described changes, double check to make sure that you yourself do not introduce new duplicate tags, otherwise your Visual Studio will start up with an unusual looking start page and crahs, after fixing it all of your pinned editor and tooling windows will be gone and you'll have to re-arrange everything!
4. Your existing SQLite database (even if visible in the Server Explorer) might still not show up in the Choose Your Data Connection section of the Entity Data Model Wizard after hitting Next.
5. In that case just select New Connection..., Continue, enter your connection string there into the mask and test the connection before hitting OK. Then your database should be selected and it should work.
6. In case you don't know the connection string of your SQLite database, but managed to get it into the Server Explorer, you can get it by right clicking and selecting Properties in the Server Explorer, it will show up in the Properties window.