June 21, 2013

System.Data.SQLite on Entity Framework 6

This feature has been added.
Versions 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=, 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 because there have been some breaking changes to the provider model in Entity Framework 6, and providers will need to be updated before they can work. (See the linked article for more information.)

An updated provider

Since the System.Data.SQLite provider is open source, I took the liberty of creating a fork and updating it to work with Entity Framework 6. You can find it hosted over on GitHub -- the bricelam/System.Data.SQLite.Linq repository.

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.
Install-Package System.Data.SQLite.Linq -Pre -Source
Enjoy! And be sure to give us any feedback regarding EF6 over on our CodePlex project page.


Unknown said...

Thanks for that Brice.

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.

Any ideas? Did you get VS design time support working?


P.S. I'm on VS2012 Update 3, EF6-beta1, with EF6 tools for VS beta installed.

Brice Lambson said...

@Ross, the EF tools for VS beta don't yet support third-party EF6 providers. As soon as they do, I'll make sure it can work with this updated provider.

Alonzo said...

This is cool!

Anonymous said...

Could you update to EF6 RTM (Nuget is 6.0.1 now)?

Rick F said...

Hey Brice,

We built a large application using SQL Compact and Entity Framework 5.

I know it's unrelated but we're wanting to move the app to a Windows Metro App which means moving to SQLite which is doable BUT Entity Framework 5 isn't supported.

Considering your knowledge of EF / EF6 I was wondering if you were aware of any solutions.


Brice Lambson said...

@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.

Rick F said...

@Brice - thanks for the quick response, I've placed my votes :)

Crossing my fingers!

Mikey G. said...

Has this been merged into the latest SQLite nuget package releases?

Brice Lambson said...

@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.

Anonymous said...

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.

Anonymous said...

Thanks so much for taking the time to share this Brice.

I can't seem to get it to work after following your instructions from the first walk through but changing to the second for the Sqlite I get:

An unhandled exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in mscorlib.dll

any idea what I'm doing wrong

Thanks Leon

Unknown said...
This comment has been removed by the author.
Anonymous said...

Could you update your Nuget Package?
Because it depend SQLite 1.0.88 and the version can not be find at Nuget Gallery now.

Brice Lambson said...

Thanks, Anonymous. Updated to and EF 6.0.2.

Barna said...

What about 64 bits SqLite provider ?
Do I need to recompile you assembly for 64 bits ?
Have you already do that ?
Thank you for your work.

Unknown said...

Brice, all I can say is heartfully thanks!! You save my day!! :D

Anonymous said...

Any updates on this? Cannot seem to make this work with my webmatrix3 project.

Anonymous said...

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.

Any word about that? Anybody else encountered that kind of problem?


Brice Lambson said...

@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.

Anonymous said...

I see, do you know if someone is working on SQLite EF provider with async support?

Thanks again.

Anonymous said...

Thanks for the hard work!

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= ..."

then a long list of error from the same type.

Could you please advice.



Anonymous said...

-- "Thanks, Anonymous. Updated to and EF 6.0.2."

Hi Brice,

Seems this version not work on .Net Framework 4.0 (Assembly System.Sqlite.Linq not found). Is there any way that I could get the old version? or how can I get rid of this error? Thanks!

Anonymous said...

Is there a specific reason why you removed Resources/Common.ConceptualSchemaDefinition.csdl?

Brice Lambson said...

@Anonymous (#9) No, it was just an unused resource.

Brice Lambson said...

@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.

Stijn Herreman said...

Official EF 6 support is planned for the next release. - February XX, 2014 (release scheduled)
- ...
- Add support for Entity Framework 6.
- ...

Source: http://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Brice Lambson said...

@Stijn Herreman Awesome! ;) (I filed a bug and worked with them on the implementation.)

Unknown said...

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 Help
Thank you

Anonymous said...

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

Anonymous said...

I don't succed to create an ADO.NET Entity Data Model using last nuget package and VS2013.

From VS I can browse or create a database but when I use the ADO.NET Entity Data Model Wizard I get an error which say that is not possible find a compatible Entity Framework provider.

Any idea?

Brice Lambson said...

@Anonymous, Did you rebuild your project after installing the provider?

Anonymous said...

There seem to be some nasty problems related to the VS2013 designer and System.Data.SQLite 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.

Anonymous said...

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.

Unknown said...

Hello Brice
I get the error of an incompatible EF database provider. I’m using the supposedly fixed version of System.Data.SQLite.EF6 ver 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

Marc-Antoine Babin said...

Hi, I've got the exact same problem then you, so I would like to know If you were able to solve your problem.