2

I'm attempting to convert a c# application currently running on SQL Server 2014 LocalDB over to SQLite, which appears to do the same job but is much lighter. I spent the whole day reading about it and installing various components.

The problem is that I can't find a way to either migrate my Entity Framework 6 database schema. Nor can I successfully create a new database schema from the SQLite database. Does the Visual Studio 2013 data model designer work with SQLite? How can I get my entity objects created and working properly?

** I'm not looking to create the database tables with a code-first model. I got my database structure already. I'm only looking to query the database with LINQ.

I installed the SQLite package from (the one that provides design-time support for Visual Studio 2013) https://system.data.sqlite.org/

I installed System.Data.SQLite (x86/x64) into my project from NuGet http://www.nuget.org/packages/System.Data.SQLite/

I followed these instructions http://vijayt.com/Post/Using-SQLite-database-in-NET-with-LINQ-to-SQL-

If I go in Server Explorer, I'm able to add the SQLite connection. When I add a new item to the project "ADO.NET Entity Data Model", and then select "EF Designer from database", and then "New Connection", the only options it offers are SQL Server and SQL Server Database File. If the SQLite connection is in App.Config, it appears in the list and I can use it, but it doesn't work properly.

So... what do I do from here?

Etienne Charland
  • 3,424
  • 5
  • 28
  • 58
  • So... what does it support? How is it supposed to be used? As for EF7, does that mean that Visual Studio 2015 RC has built-in support for SQLite with EF7? – Etienne Charland May 27 '15 at 01:01
  • I'm not looking to create database tables. I already got the database structure. I want to query the database with LINQ. – Etienne Charland May 27 '15 at 01:11
  • 1
    Right.... you were talking about migrating so you confused me. In which case it's a duplicate of [this other question](http://stackoverflow.com/questions/25089346/database-first-create-entity-framework-6-1-1-model-using-system-data-sqlite-1-0). – kjbartel May 27 '15 at 01:45
  • Ok WOW... it "can" work but it's a pain in the *ss just to get the basic functions to work. Also I'm just realizing that database functions are not supported... well there are user-defined functions that can be done with C++ code but it's very complicated. I'll wait until .NET has better support for SQLite and stick to SQL Server LocalDB for now. I'm sure it will evolve a lot since people are requesting it for mobile development. Thanks – Etienne Charland May 27 '15 at 02:46
  • I actually started with a database in SqlServer and generated my model using [EntityFramework Reverse POCO Code First Generator](https://efreversepoco.codeplex.com/) so I could then use the Code First approach going forwards. Then I created the SQLite database using [SQL Server Compact & SQLite Toolbox](http://sqlcetoolbox.codeplex.com/) to generate sql scripts for me. It should be noted that EF7 [only supports code first](http://blogs.msdn.com/b/adonet/archive/2014/10/21/ef7-what-does-code-first-only-really-mean.aspx). – kjbartel May 27 '15 at 05:11
  • 1
    I have a number of tips here for this broken scenario: http://erikej.blogspot.dk/2014/11/using-sqlite-with-entity-framework-6.html – ErikEJ May 27 '15 at 05:55
  • Thanks. And a slightly different question, but is it possible to use SQLite User-Defined Functions with LINQ? – Etienne Charland May 27 '15 at 15:18
  • Yes, User Defined Functions can be used with LINQ to SQLite. I got the application running after working around a few bugs and limitations. The application is much snappier with SQLite than with SQL Server LocalDB. – Etienne Charland May 30 '15 at 17:57

1 Answers1

3

Visual Studio data model designer definitely works with sqlite, checked yesterday with VS2013 community edition.

Things you need

  • Sqlite bundle with designer support here https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki I don't know your platform, it can be sqlite-netFx451-setup-bundle-x86-2013-1.0.97.0.exe or sqlite-netFx451-setup-bundle-x64-2013-1.0.97.0.exe. This is the only setup package that is capable of installing the design-time components for Visual Studio 2013. Personally i have 32 bit system with 32 bit visual studio
  • Entity framework 6 tools for VS2012/VS213 https://www.microsoft.com/en-us/download/details.aspx?id=40762, be sure you download for VS2013

  • Add nuget package System.Data.SQLite (x86/x64) 1.0.97 to your project (version does matter) Only after these steps you can list Sqlite connection in Entity Data Model Wizard.

    P.S. Design time support for VS2012 and VS2013 is mutually exclusive

    P.P.S After such drivers personally i want to stay away from System.Data.Sqlite, i even can't complile my project ported from VS2010 with original old drivers 1.0.88 in modern Visusual Studio

AZ_
  • 21,688
  • 25
  • 143
  • 191
vitalygolub
  • 735
  • 3
  • 16