1

I have a Asp.Net webapi 2 system that works with sql server. I developed it using entity fraework 6.1.3 code first data models and fluent mappings with the typical workflow of add-migration/update-database. I love it.

I have a need to create the exact same software with a lighter weight db to run on a raspberry pi device. It's the disconnected version of the software that will replay/resync all of its data to the cloud version (sql server).

I realize I may need to relax some of my constraints, but starting at the extreme, I would like to target the exact same code base with something like sqlite and xcopy deploy it to my raspberry pi and run in on mono under kestrel web server.

Ideally, I'd just like to change my connection string to point to an empty sqlite db, do a update-database and have the exact same software initially run on my windows development box (and then xcopy it over).

I have read a lot about sqlite entity framework support but a) it doesn't seem to support migrations b) it doesn't seem to support fluent mapping

I could get by using a tool to convert my sql server db to sqlite (every time I change schema) and thus avoid the need to update-database. But the lack of fluent mappings would still prevent the data model to be properly mapped to the existing sqlite schema.

Does anybody have some thoughts/recommendation for sqlite that my help me accomplish my goals?

Do you have any other database recommendations that would help me accomplish my goals - for instance I looked at vistadb, but I don't think they support fluent either.

The devart sqlite driver seems to support everything I need but their examples are all old school and AFAIK they don't have one single example that is a modern code first model with fluent mappings. And even if they did fully support code first wth fluent I am concerned there would be some syntax differences and I am not sure my existing sql server targeting code would be compatible with it. I asked the question on their forums and sent an email but haven't received a response yet.

Thanks

t316
  • 1,149
  • 1
  • 15
  • 28
  • Have you looked at EF 7? (currently in beta) – ErikEJ Aug 10 '15 at 19:19
  • I think the work for the SQLite provider on ef 7 has been postponed. So, I don't think I can use it yet... – t316 Aug 10 '15 at 19:27
  • The EF7 SQLite provider is available in beta 6 – ErikEJ Aug 11 '15 at 05:51
  • Do you know if I can use ef7 with .net 4.6 and all of my existing webapi code or do I need to upgrade to .net core? And is it an easy upgrade to got from ef 6 to ef 7? Thanks – t316 Aug 11 '15 at 11:31

1 Answers1

0

You could consider using EF7, which is a API compatible new version of Entity Framework, that fully supports migrations and fluent mappings with SQLite. EF7 runs on .NET 4.6 and .NET Core. Depending on what features in EF6 you use, it could be an easy upgrade, in particular since you already use Code First.

http://ef.readthedocs.org/en/latest/getting-started/linux.html

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • I actually got the devart sqlite drivers to work with Asp.Net 5 by manually setting the migration sql generator to their implementation in the Configuration constructor. However, their implementation is still missing alter/drop sql generation for pks and fks I think. It does vanilla models nicely but when you try to change a column name that had an index or constraint, it errors out. – t316 Aug 13 '15 at 16:53
  • If that is a problem for you, you should not be using SQLite, as it does not support any kind of schema changes via ALTER or DRROP – ErikEJ Aug 13 '15 at 17:13
  • It's not a problem unless I can't find a way to sync my model changes to sqlite after having them applied to sql server. Since model changes can have columns dropped and relationships changed as you discover new nuances, I am concerned I'll be stuck and not be able maintain code first migrations for sqlite. Maybe at every iteration I can use a tool to convert the sql server db to sqlite and add the sequences table myself manually. That should give the migration engine what it wants, no? – t316 Aug 13 '15 at 17:24
  • I wonder if I can have two configurations going to two different MigrationDirectories for the same DbContext and in the sqlite version I always first update to the TargetMigration:0 first and then delete the prior migration cs files and add one single migration that goes forward to the current state of the data model. This will naturally create everything from scratch in one swoop and have no alters or drops. The sql version of the migration will contain the true history of changes because its migration sql generation is more complete... I will try that tonight - what do you think? – t316 Aug 13 '15 at 18:04
  • Mind you this project is in no way complete! E.g. Many To Many cardinality is not supported, as well as change graph even aggregation. Migrations dó work though ! – Samjongenelen Sep 29 '15 at 08:26