Converting from EDMX model to Code First?

Question

Hi Guys,

I ran across this blog post about the EF Power Tools, and one of the cool features is the ability to start with a database and produce a code first model for it, so you can quickly generate the skeleton of what you need:

http://blogs.msdn.com/b/adonet/archive/2011/05/18/ef-power-tools-ctp1-released.aspx?CommentPosted=true#commentmessage

I am actually trying to find something slightly different. I have spent a lot of time mapping our entire database to an EDMX model, which was quite the chore because all our tables are in MySQL and are in MYISAM format. Hence there are now foreign key relationships included in the database schema, so I had to build all that by hand for each table as I build the data model. I also renamed a bunch of the properties to use PascalCase, so the conceptual model is now vastly different to the physical model. Now the downside to the database first EDMX approach is that the EDMX files contain specific details about your database, so you can't just change the underlying provider from say, MySQL to SQL Server, and expect anything to work. The physical model is MySQL specific (actually connector specific, since it won't work with other connectors, like the Devart dotConnect for MySQL), and won't translate.

As I understand it, Code First makes it easier to avoid database dependencies because it can abstract you away all the underlying specifics of the database, and we can avoid having to build an entirely separate database model for each database we wish to support. So, I would like to convert my EDMX model to Code First code!

Something similar to the 'Reverse Engineer Code First' option in the power tools, but rather than do it from a physical database, I would like to be able to do it from my EDMX file, since it contains a lot more information in it now that I have done my conceptual modelling.

Does anything like this exist? Seems like it would be possible to write a T4 template to do this?

Answer 1

Hi,

You have the ADO.NET DbContext Generator, which you can run on your model to get the DbContext/DbSet classes for your model, you won't however get the relationships defined in the model generated, but maybe you can customize the template to do this.

However, there may be a another way to do this. In  your model, you have the option to generate a database from the model. What you could try is to change the DDL Generation Template from SSDLToMySQL.tt to SSDLToSQL10.tt. This will maybe give you a clean SQL server DDL which you can run on a SQL server and reverse engineer back to Code-First. Maybe this works.

Hope this helps!

---

--Rune

Answer 2

Sounds like a good idea, but that would generate the exact schema I have in my MySQL database (I would hope), which does not match my conceptual model at all. I have renamed all the table columns to be PascalCase, and made a lot of other changes to make it more useful at the C# level, so I need that to come through in the Code First model also.

But I did come up with the idea of looking at the SSDLtoSQL10.tt template to see if I can use the information in there to build my own T4 template that will generate a Code First model from my EDMX file. If I can do that, then I can continue to use the EDMX to model my database, and generate a Code First model from it to use that would (hopefully) be database provider agnostic.

Answer 3

After dealing with issues with mapping Sbytes and tinyint's in MySQL, I changed my schema to get the DDL code to work properly and write the DB schema out. Interestingly what I discovered is that it writes the DDL based on the conceptual model, not the original SSDL. So the table columns match exactly once you do that, so it ignores any SSDL mappings. Which is not what I wanted :(

Answer 4

Hi,

Yes, it uses the CSDL to generate the DDL (since it's intension is Model-First approach) and since your orignial edmx schema differs between the csdl and ssdl it wont work.

But I did misunderstand you a bit in your first post, since I thought you wanted the CSDL model in CF, but what you really want is your MySql model into CF.

What you could do is to convert your database schema from MySql to SQL Server with a 3rd party app, just search for "migrate mysql to sql server" and you will get some hits. You can then generate your CF from the converted database.

 

---

--Rune

Answer 5

Yes, but that will just get me a fully mapped schema using the table names and column names that the MySQL database uses. It won't bring over any of the conceptual layer changes I have made in the schema either.

I will try hacking my own T4 template and see what I can come up with.

Answer 6

Hi Kendall, have you build/find any solution? I have the same problem as you are.

Answer 7

I am working on a solution as a T4 template for my own model at the moment. There are some things that don't map well to Code First because it is not supported at the moment (like table splitting or entity splitting across two tables), so I got stuck there. But I decided to simply take that out of my model and keep the tables separate and use a 1:1 mapping for them so it will map. Just need to get around to working on the T4 template again.

I worked out how to crack open the MDL layers to determine the mappings involved, and just need to write all the code to generate the schema and handle all the associations etc.

My ideal goal is to keep my EDMX file so I can use it to model the database and keep it in sync with DB changes, and have the Code First model get dynamically generated from the EDMX file using a T4 script. Then I get the best of both worlds, and if I want to change the Code First model, I just change my T4 scripts :)

Soon I should have a blog where I can post all this stuff, but at the moment I don't really have anywhere to post it. Once I have it working I will post it somewhere and post here where I posted my code (maybe a project on an Open Source repository or something?).

Answer 8

I also got rid of support for complex types in my model (I only used them in one entity), because it take a lot more coding to make that work right :) And my goal is to be able to generate Code First for other ORM solutions from the same template also (or a variant of it), so I can test using different ORM solutions in my project to compare them to EF (like OpenAccess ORM from Telerik).

Answer 9

Great idea :) It would be nice to have it as an open source project, maybe to post it at CodePlex? If you need contributors, just write here, and keep us updated what is happening.  

Answer 10

We use Perforce internally for our source code system, and I don't know the first thing about Mercurial (and I don't know how to use Team Foundation Server). I suppose Codeplex would be as good a place as any to host this project; can it be used with Visual Studio 2010 Professional, or does it require the Team version of VS2010 to use TFS? In which case I would need to get Mercurial and figure out how to use it, to submit stuff from my Perforce depot to Codeplex :)

I used to do this a long time ago with CVS submitting to Linux projects, but it seems like CVS is gone by the wayside these days!

Answer 11

Also, once it is done, it would be good to find a way to package it up so that people can download it from within VS2010 somehow? Where do I find information on that?

Answer 12

How's this coming? I'd be very interested in this tool!

Answer 13

Still no tool after 9 months?

Answer 14

I had a basic template working for my uses, but I ended up giving up a long time ago on Entity Framework and now use a Micro-ORM. So alas I won't be working on this :( ...

Answer 15

can you share what you had? Maybe some of us can finish it.

Answer 16

I dug up the code so I could ZIP it up and post it somewhere. Where is the best place to post something like that?

Answer 17

Hi,

Maybe you want also consider the approach used in this article: http://weblogs.asp.net/jgalloway/archive/2011/02/24/generating-ef-code-first-model-classes-from-an-existing-database.aspx

Personally, I found it very useful.

Regards, Mehran