Get SQLite to work with VS2013 Data Model Designer

Question

I have followed very closely this great tutorial about how to connect SQLite with Visual Studio 2013, but when I get to the Entity Data Model Wizard part of the tutorial, I can't see my SQLite connection or the SQLite provider, there are only options for the SQL Server.

I have followed the tutorial very closely, I even tried to install the SQL Server Compact/SQLite Toolbox, to compile the project to x86, to install the Entity Framework Visual Studio tools, and still nothing happens.

My packages.config looks like:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="CommonServiceLocator" version="1.2" targetFramework="net45" />
  <package id="EntityFramework" version="6.1.2" targetFramework="net45" />
  <package id="NodaTime" version="1.3.0" targetFramework="net45" />
  <package id="Prism" version="5.0.0" targetFramework="net45" />
  <package id="Prism.Composition" version="5.0.0" targetFramework="net45" />
  <package id="Prism.Interactivity" version="5.0.0" targetFramework="net45" />
  <package id="Prism.Mvvm" version="1.0.0" targetFramework="net45" />
  <package id="Prism.PubSubEvents" version="1.0.0" targetFramework="net45" />
  <package id="System.Data.SQLite.Core" version="1.0.94.0" targetFramework="net45" />
  <package id="System.Data.SQLite.EF6" version="1.0.94.0" targetFramework="net45" />
</packages>

any advices on this?

---

EDIT 1:

I am kinda shifting to SQL Server CE 4.0, even though it's being deprecated. I rather SQLite for lack of 4Gb limitation, but I can't make it work, and I have read EF isn't smoothly integrated on it.

But now I have a new problem, this arises both with SQL Server CE 4 and MySQL:

I hate to be stuck in a project for such little things like this, anyone knows how to solve this?

Answer 1

I followed the tutorial as well, check everything also in the comments. What was missing on my system (Visual Studio 2015, Entitiy Framework 6.1.3 and sqlite 1.0.103 as nuget packages was to add the following lines in App.config in the section "entityFramework / Providers" (I added it after the standard: 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" />

After that, the already included connection to sqlite in the Server Explorer (this worked already) was the standard one for Entity Framework.

Answer 2

I got my project to work with SQL Server CE 4.0, but not with SQLite.

I had two projects under the same solution (one for the program itself and another as a test project), and apparently they were conflicting with each other some how. I removed all EF and SQLCE4 references from nuget and etc. from both projects and ONLY added them back to my program project, when it was running as it should, I added it to the test project. I was able to get the Entity Data Model Wizard working, and apparently all is good, but I didn't have done any EF operations so far.

I am really busy with another project so I don't have much time to completely test this, make some CRUD operations and even try it on SQLite. I did a quick test using a new fresh solution and apparently SQLite and SQLSeverCE4 are working, when added to a console app, I added them and generated a Entity Data Model Wizard as the tutorial points out. BUT in my project currently I can't test SQLite for time limitations. I want to give you guys a more complete answer when I have more time but for now, that's all I can test.

Answer 3

I ran into this exact problem this morning (I was following the blog post you referenced in your original problem) and I found the issue, and how to resolve it:

1. Uninstall the nuget package for SqlLite EF6
2. Remove the system.data entries from the app.config
3. Install the nuget package

4. Only put the two removes and one add in your app.config

   <DbProviderFactories>
     <remove invariant="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" />
   

5. Now the SQLLite will show up in the ADO.NET Entity Data Model connection area

You may need to add the second "add" back into the system.data for it to work at runtime since I haven't gotten that far yet.

Answer 4

Have you got ONLY the 1.0.94 SQLite VS Tools installed in GAC?