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

by

[ 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…

Connect Entity Model

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… **

More Related Contents:

  1. ASP.NET MVC 2.0 Implementation of searching in jqgrid
  2. Code First: Independent associations vs. Foreign key associations?
  3. Unique Key constraints for multiple columns in Entity Framework
  4. Entity Framework 6 Code first Default value
  5. Improve navigation property names when reverse engineering a database
  6. How to select top N rows for each group in a Entity Framework GroupBy with EF 3.1
  7. Create a Dropdown List for MVC3 using Entity Framework (.edmx Model) & Razor Views && Insert A Database Record to Multiple Tables
  8. EF5 Code First Enums and Lookup Tables
  9. How to configure ProviderManifestToken for EF Code First
  10. Can I specify global mapping rules in Entity Framework Code First?
  11. The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid
  12. Entity Framework loading child collection with sort order
  13. How can I delete 1,000 rows with EF6?
  14. Entity Framework 4 selective lazy loading properties
  15. Equivalent for .HasOptional in Entity Framework Core 1 (EF7)
  16. When should I call SaveChanges() when creating 1000’s of Entity Framework objects? (like during an import)
  17. Adding Validation Attributes With an Entity Framework Data Model
  18. Ramifications of DbSet.Create versus new Entity()
  19. How to pass parameters to the DbContext.Database.ExecuteSqlCommand method?
  20. Keyword not supported: “data source” initializing Entity Framework Context
  21. Entity Framework Data Annotations Set StringLength VarChar
  22. EF 4.1 loading filtered child collections not working for many-to-many
  23. How to map Table per Type (TPT) inheritance in Entity designer?
  24. How do I remove underscore of foreign key fields in code first by convention
  25. Entity Framework Validation confusion – maximum string length of ‘128’
  26. Number of members in conceptual type does not match with number of members on object side type
  27. Delete an object and all of its related entities in Entity Framework
  28. LINQ To Entities + Include + Anonymous type issue
  29. Entity Framework Core 2.0 – Run migrations step by step
  30. EF4 LINQ Ordering Parent and all child collections with Eager Loading (.Include())

Leave a Comment