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

[ 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... **

This is what worked for me:

• had Windows 8.1 Pro x64
• had VisualStudio 2013 Ultimate Update 4
• installed http://system.data.sqlite.org/downloads/1.0.94.0/sqlite-netFx451-setup-bundle-x86-2013-1.0.94.0.exe (in GAC, plus designer)
• installed EFTools6.1.2ForVS2013.msi from https://www.microsoft.com/en-us/download/details.aspx?id=40762
• in old or new solution/project that targets .NET 4.5.1 and Any CPU installed via NuGet package System.Data.SQLite
• added new model from database (I did not see the SQLite provider here until I installed the NuGet package inside the project)
• to actually access the data, I had to rearrange the add and remove items inside the DbProviderFactories tag in the app.config

So the packages I had were:

    <packages>      <package id="EntityFramework" version="6.1.1" targetFramework="net451" />      <package id="System.Data.SQLite" version="1.0.94.1" targetFramework="net451" />      <package id="System.Data.SQLite.Core" version="1.0.94.0" targetFramework="net451" />      <package id="System.Data.SQLite.EF6" version="1.0.94.0" targetFramework="net451" />      <package id="System.Data.SQLite.Linq" version="1.0.94.1" targetFramework="net451" />    </packages>

and the app.config that worked for me had contents:

<?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>  <entityFramework>    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">      <parameters>        <parameter value="mssqllocaldb" />      </parameters>    </defaultConnectionFactory>    <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>  <system.diagnostics>    <sources>      <!-- This section defines the logging configuration for My.Application.Log -->      <source name="DefaultSource" switchName="DefaultSwitch">        <listeners>          <add name="FileLog" />          <!-- Uncomment the below section to write to the Application Event Log -->          <!--<add name="EventLog"/>-->        </listeners>      </source>    </sources>    <switches>      <add name="DefaultSwitch" value="Information" />    </switches>    <sharedListeners>      <add name="FileLog" type="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" initializeData="FileLogWriter" />      <!-- Uncomment the below section and replace APPLICATION_NAME with the name of your application to write to the Application Event Log -->      <!--<add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="APPLICATION_NAME"/> -->    </sharedListeners>  </system.diagnostics>  <startup>    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" />  </startup>  <system.data>    <!--        NOTE: The extra "remove" element below is to prevent the design-time              support components within EF6 from selecting the legacy ADO.NET              provider for SQLite (i.e. the one without any EF6 support).  It              appears to only consider the first ADO.NET provider in the list              within the resulting "app.config" or "web.config" file.    -->    <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>  <connectionStrings>    <add name="WorkinDataEntities" connectionString="metadata=res://*/WorkinDataModel.csdl|res://*/WorkinDataModel.ssdl|res://*/WorkinDataModel.msl;provider=System.Data.SQLite.EF6;provider connection string="data source=X:\dev\proj\workin\bin\data.db"" providerName="System.Data.EntityClient" />  </connectionStrings></configuration>

As you can see, somehow in the end, for me, it was not necessary to remove "EF6" from invariantName or to remove or rearrange other providers or default connection factories. I didn't have to do any reg hacks. Rearranging add/remove tags (that were added during NuGet System.Data.SQLite 1.0.94.1 package installation) in the start-up project's app.config made the difference.

After all the above, I updated EntityFramework to 6.1.2 via NuGet and both updating the model from database via designer and accessing the data at runtime still works.

I finally have steps to consistently add Sqlite and EF6 to a project:

EF6: versions 6.0.0 to be 6.1.3 (currently the latest version).System.Data.Sqlite: 1.0.93 - 1.0.95 but not 1.0.98 (currently the latest version).

To avoid getting System.Data.Sqlite 1.0.98 , do not install using Nuget Package manager. Manually install System.Data.Sqlite. Unfortunately, http://system.data.sqlite.org/ does not readily list previous downloads.

x86 System.Data.Sqlite 1.093

x64 System.Data.Sqlite 1.093

I use Nuget Package manager and install EF6 and then I manually added System.Data.Sqlite library. I have tried multiple times to use 1.0.98 but I cannot

App.config File

For my implementation, I am working with database first an manually coding the entity data classes

 public partial class MyDbContextEF : DbContext{    public MyDbContext() : base("name=MyDbContext") { }    public DbSet<DataRecord> DataRecords { get; set; }}[Table("TableName")]public class DataRecord{    [Key]    public Int64 RowID { get; set; }    public string Name { get; set; }}