2

I have data stored in a MySQL database and I'm accessing it through entity framework 6. What I want to do is to extract the data and store it in a server-independent manner, so that I can use this data in the future for bootstraping of a larger database (where the data from MySQL is only a small portion of). The target database will not be MySQL.

The MySQL data was there before, so I used Database First. For the new database I use Code First. The MySQL data also needs some clean-ups, what is done in code. So the workflow is:

  1. Read data from MySQL with Entity Framework database first
  2. Optimize/enhance/correct it in code
  3. Store in another database using code first

So my first thought was using SQLite, but sadly, Code First is not supported.

Using some other format (i.e. XML/DataSets/CSV) to store the data would require me to reimplement the import code.

Right now I didn't find another embedded database with Entity Framework support. As Entity Framework is meant for relational databases, I would say that Redis (or other NoSQL dbs) won't do the trick.

I also thought if "converting" H2 using IKVM.NET and then using H2 as SQLite replacement would be an option -- but there's the risk that this might fail due to some internal dependency not supported by IKVM.NET.

So I would be glad to get answers to the following questions:

  1. Would the H2 way work? Is there even a stable/working ADO.NET provider?
  2. I wouldn't care much about the local storage format (could also be JSON etc.) -- is there anything not-SQL, but providing Enitity Framework support?
  3. Did I miss something? Is there a way I just didn't see yet?

Here are some related questions I stumbled upon which deal with the possibility of using Entity Framework to use files:

Community
  • 1
  • 1
munro
  • 31
  • 4
  • Do you really need EF to create the database tables for you, would it be too much of a burden to do that by hand? Most definitions would probably need minor changes from the MySQL versions. And after that you can use Code First approach. The only thing missing from the SQLite driver is the creation of the database tables based on the code, as far as I understand. – Sami Kuhmonen Feb 23 '15 at 10:03
  • Yes and no. I'm looking for the "least work" solution. If I create the create statements by myself I have additional work. Currently I'm checking the "export from DB to XML and fill the target context manually" approach. This is also additional work but provides me with the benefit, that the XML files can easily be modified (i.e. correction of typos). – munro Feb 23 '15 at 10:16

1 Answers1

0

There is no complete solution, but there are many ready-made parts:

This project wraps the compiled Jar of H2 after IKVM.Net with classes that implement the ADO.Net interface to allow for easy use in .Net projects:
https://code.google.com/p/h2sharp/

EF provider for SharpHSQL (which doesn't work, because, SharpHSQL is too old, it's port of Hypersonic 1.4):
https://github.com/ArsenShnurkov/SharpHSQL/tree/master/src/SharpHsql.Linq

here is the description of build process:
https://groups.google.com/forum/#!topic/h2-database/QAvFqbyd4_0
https://code.google.com/p/h2sharp/wiki/BuildingH2Sharp

The authors of above code did a great job of writing such huge amount, so i think it's nothing wrong with writing some more to finish your task.

user1709408
  • 528
  • 4
  • 16