Pages

June 21, 2013

System.Data.SQLite on Entity Framework 6

This feature has been added.
Versions 1.0.91.0 and newer of System.Data.SQLite support EF6, this fork is not required.

If you've tried my tutorial for using Entity Framework on SQLite, you may have noticed that it doesn't work on Entity Framework 6. If you set everything up just like you're supposed to, you still get the following error.

The 'Instance' member of the Entity Framework provider type 'System.Data.SQLite.SQLiteProviderServices, System.Data.SQLite.Linq, Version=1.0.86.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139' did not return an object that inherits from 'System.Data.Entity.Core.Common.DbProviderServices'. Entity Framework providers must extend from this class and the 'Instance' member must return the Singleton instance of the provider.

This is not in any way an official or supported fork; I just wanted to get something out there for the community to play with. Also, I do not intend to submit a pull request for my changes since they very trivial, and the more significant work will be integrating it into their build system.

Installing it

How can you get it? Easy, I've uploaded it to a NuGet feed hosted over on MyGet. You can install it directly from Package Manager Console using the following.

35 comments
:

I've got this working at runtime, but at design time Visual Studio is preventing me from doing certain things (like SQL code generation from model) with the following error:

Error 168: A null was returned after calling the 'GetService' method on a store provider instance of type 'System.Data.SQLite.SQLiteFactory'. The store provider might not be functioning correctly.

Digging around more, it seems Visual Studio is passing System.Data.Common.DbProviderServices as the expected type to the SQLiteFactory function IServiceProvider.GetService(Type serviceType).

If I edit the SQLite source to change the type of SQLiteProviderServices to System.Data.Common.DbProviderServices, then this design time error goes away, but of course I'm then back to the runtime error mentioned in your post.

@Rick F,Our team is currently working through some ideas on how to bring Entity Framework to Windows Store (and Phone) Apps. (See the related CodePlex and UserVoice items). In the meantime, I recommend checking out this video that shows how to use sqlite-net -- an EF-like technology that works in Windows Store Apps.

@Mikey G, No; I have not submitted a pull request to the official System.Data.SQLite project. Their codebase attempts to maintain compatibility with all versions of .NET and adding EF6 support there would require adding #if blocks to the code, adding build infrastructure, and adding additional NuGet packages. I will let them figure out how they want to do. The actually code changes to support EF6 are rather trivial. I decided just to create this unsupported fork to support EF6 in the meantime. I also didn't want to confuse the NuGet feed, so I released on MyGet instead.

just wanted to let you know - if you have installed original system.data.sqlite component from its installer (for designer component for example) - this solution wont work - it wont download this custom wrapper but will use already installed one from GAC. so you will need to uninstall original one first.

Thanks, that helps me a lot.One problem, I can't seem to be able to work with Async methods (like ToListAsync when querying).The function returns the data i wanted, but it does not do it asynchronously, event though i use the 'await' keyword when invoking the function.

@Anonymous, EF/ADO.NET providers need to implement their own async support. Since all I did was copy the existing provider code, this work has not been done. Hence, any async calls will simply execute synchronously.

I'm having trouble to Generate DB from model using Model-First approach. (using right click in the EDMX tab)

Using EF6.2 I get this error:

Encountered the following errors while processing the template: c:\\..SSDLToSQLite.tt:Line 68, column 4: ' compiling transoformations: the type 'system.data.Entity.core.metadata.edm.entitySet' is defined in an assembly that is not referenced. you must add a reference to assemlbly 'EntityFramework version=6.0.0.0 ..."

Hi Mr Lambson,I'm Hieu.I using your example with .net 4.5 successfully.But when I convert project to .net 4.0 it is error.I read your comment for @Anonymous (#8) This was just a workaround to satisfy the 80% case (EF6 on .NET 4.5). Until an official release is made, you will have to manually recompile against .NET 4.0 before using.

But when I change your LinqSqlite project to Net 4.0, it has error, because System.Data.SQLite for 4.0 has no ISQLiteSchemaExtensions, SQLiteParameter....What could i do, Please HelpThank you

Hello Brice,Thank you for posting the article on using EF6 with SQLite. I can confirm that the latest System.Data.SQLite driver version 1.0.91.0 works with EF6. For anyone else that want use EF6 with the latest driver from System.Data.SQLite this app.config worked for me:

There seem to be some nasty problems related to the VS2013 designer and System.Data.SQLite 1.0.92.0 and EF 6.Code first works after serious tweaks to the config, but model first doesn't seem to work. There seems to be much confusion about this.More info on: http://stackoverflow.com/questions/21757843/system-data-sqlite-1-0-91-0-and-ef6-0-2(You're even mentioned, Brice!).Looking at the bug threads at sqlite.org, seems like the devs are aware and planning to fix in the 1.0.93 release?But not sure it's going to work for model first.

Just to clarify my previous post a bit more: these are problems of model first with the VS 2013 Visual designer. It doesn't work whatever config you try, either provider not found errors or incompatibility errors.

Hello BriceI get the error of an incompatible EF database provider. I’m using the supposedly fixed version of System.Data.SQLite.EF6 ver 1.0.93.0 with VS2013. Searched everywhere on the net for the past 2 days. No one seems to know why and how to fix. Any ideas? – and yip… I’ve installed all the NuGet necessities and rebuilt as they suggest in error msg. Thanks