Reverse engineer model from database

[rowanmiller]

Looks like we didn't have a work item tracking this. We need tooling to reverse engineer a model from a database. For the initial cut this should include the functionality from EF6.x (ability to select tables etc.).

This does not include the ability to incrementally update the model based on changes to the database, #831 tracks that.

[lajones]

Moved comment from #2465.

From @ErikEJ: Is this a good time to start investigating using Reverse Engineering with SQLCE, or is it too early?

From @divega: @ErikEJ I personally believe it is a good time for you to get started with it. If you hit issues or have any feedback we can make adjustments.

From ErikEJ: @divega Sounds great, is there some docs/design specs? Or a drawing? 😄

I have a few questions:
1: Does this info still apply: https://stackoverflow.com/questions/29300777/is-there-an-entity-framework-7-database-first-poco-generator?
2: If so, how does the command know which provider to use?
3: Is the rev-eng package simply installed as needed? (EntityFramework.SqlServer.Design)
4: Will I "simply" implement the equivalent of EntityFramework.SqlServer.Design ?

[lajones]

@ErikEJ @divega

1. The commands listed in https://stackoverflow.com/questions/29300777/is-there-an-entity-framework-7-database-first-poco-generator are still accurate.
2. That's the problem - at the moment it's hard-coded to use EntityFramework.SqlServer.Design. @rowanmiller are you ok if I add back in the --provider flag to pass in the name of the RevEng provider assembly? Also if the argument is not provided do you want to a) error or b) default to the existing value?
3. At the moment you must reference EntityFramework.SqlServer.Design in your project.json and hence it is installed when package restore is run on the project. For your new provider your new package should be referenced in project.json instead.
4. Yes. Hopefully simply :-). You must have a class that implements IDatabaseMetadataModelProvider. I'll write this up but in the meantime details below.

A) On IDatabaseMetadataModelProvider, GenerateMetadataModel(string connectionString) generates an IModel containing EntityTypes. DbContextTemplate() and EntityTypeTemplate() return the relevant templates as strings (you get to look them up however you want - however note #2418 where we'll be providing a way for customers to override the default templates).

B) First the DbContextTemplate template is executed being passed an instance of DbContextGeneratorModel. On DbContextGeneratorModel you'll find a property MetadataModel - which is the model you generated in step A) above.

C) Then we loop over all EntityTypes in the MetadataModel and on each one execute the EntityTypeTemplate passing in an EntityTypeGeneratorModel. Note that on EntityTypeGeneratorModel you'll find a property EntityType which is the EntityType being processed (and which can refer to its parent - the metadata model - if needed).

D) Each Model passed in also has a Helper property - which is an instance of DbContextCodeGeneratorHelper or EntityTypeCodeGeneratorHelper respectively. These contain helpful methods for ordering the entities, setting up configuration for the DbContext based on the model etc. Feel free to inherit from these classes and override methods or provide new ones as you see fit (but bear in mind #2465).

[rowanmiller]

I think we need to quickly discuss how we want the "provider invariant name" to work in EF7. My vote is that we just use the assembly/package name, but we should discuss this.

[ErikEJ]

@lajones Thanks, that should be enough to get me going, will also await your "unblocking" of the hardcoded provider name.
Re 3: - This is my understanding of the current workflow, can you sanity check?

a: As a developer, I want to reverse engineeer my existing SQL CE database to use it with EF7.
b: In my project.json (if I have one) or using Package Manager Console, I add the
EntityFramework7.SqlServerCompact40.Design package in my Data Access project
b: To use the Reverse-Engineer command from PMC I must also add the EntityFramework7.Commands package
d: I can now run the "Reverse-Engineer" command, specifying a connection string and the name of the design package (or dll) : EntityFramework7.SqlServerCompact40.Design
e: Now some code will be generated in my Data Access Projects that models my existing database file schema

Is that just about correct?

[ErikEJ]

@lajones @divega @rowanmiller Rev Eng now works for the SQLCE provider! Thanks for your timely support 😄

[rowanmiller]

Sweet 🎉

[divega]

@ErikEJ not sure if I have said this before but... you rock! 😄

[ErikEJ]

@divega @lajones Thanks to your excellent groundwork!

[lajones]

@ErikEJ You're v welcome. Thanks for all your hard work!

[ErikEJ]

@lajones And the E2E tests revealed a couple of small bugs in my SQL CE scripting engine: https://sqlcetoolbox.codeplex.com/SourceControl/changeset/96873 - thanks!

[lajones]

@ErikEJ - Glad to help!

[lajones]

Closing this over-arching issue for the whole of RevEng. Now we'll track each individual bug/feature as it comes up. Raised issue #2751 to track ability to select tables.

[claysmith]

How do you specify the output path for ef scaffold reverse engineering?

[lajones]

A lot has changed since the above comments. If you do dnx ef dbcontext scaffold --help it will give you the options. Specifically there's a -o option on the command line to specify a sub-directory of whatever project you are in.

[claysmith]

I am running CLR-x64-1.0.0-beta7-15532 and -o is not an option

[alfberga]

And i'm running CLR-x64-1.0.0-beta7-15540 and -o is not an option too.
And no DataAnnotation in reverse engineering generated models

[ErikEJ]

You need to run a beta 8 daily build for that feature to work. And you need to customize the templates in order to force the tooling to use DataAnnotations (I am working on a blog post)

[lajones]

@claysmith - that's correct - the -o option was checked in with commit 5b19fbb. Before that you could only output to the project path. You are of course free to move the files after they've been output.
@ErikEJ - don't work too hard on that blog post. Things have been churning a lot in this area (which is why I haven't written any docs about it). #2947 changes it so we no longer allow customizable templates.

[ErikEJ]

@lajones thanks for the heads up! How do you switch between attribute and fluent then?

[lajones]

@ErikEJ Current plan is to have a command-line switch - see PR #3013

[alfberga]

Sorry for an old question: when do you think to develop the reverse engineering of Views?

[rowanmiller]

@alfberga that will happen after the initial RTM of EF7.

@lajones is there any way to track reverse engineer into thinking a view is a table at the moment?

[lajones]

You mean "trick" reverse engineering? No I'm afraid not. Each provider retrieves the corresponding metadata in their own *DatabaseModelFactory code.

On the other hand it's not likely to be complicated to add it. We have an existing issue for this: #1679. It's been on the Backlog for a while - perhaps it's time to revisit it?

[roji]

FYI everyone, I've just published the Npgsql reverse engineering provider. I've confirmed that the EF7 database-first tutorial works just fine with it.

[ErikEJ]

Congrats!

[lajones]

Yep congrats! Really cool!