EdmGen2.exe

NEW POST: Defining Primary Keys

TonyGravagno — Wed, 04 Nov 2009 07:23:41 GMT

My client has a large database provided by an upline provider. The upline periodically provides database updates, but their tables don't define the primary keys. Updating the tables with code generated from entity models requires primary keys to be generated. grrr So to regenerate valid models from the new databases we need to manually define the primary keys each time a new version is issued. That's time consuming. We could run a routine that uses Alter Table to set the primary key, but I'd rather set this in the model generated from EdmGen2 (using the source in custom code) rather than having to update the database itself.

At some point I had code that would parse C# code that was generated from models, and it would add "IsPrimaryKey = true" into the Column Attribute decorating key properties. I'm looking for something more elegant.

Thanks!

NEW POST: Proprietary ConceptualEdmGen.dll ?

TonyGravagno — Wed, 04 Nov 2009 07:03:32 GMT

I confess that I've been a bit adverse to using the Retrofit enhancements because they're encapsulated in ConceptualEdmGen.dll which does not come with source. The "as-is" nature of the Ms-PL license allows for generous distribution of assets contributed to this repository, but it also implies there is no support if anything ever goes wrong. Great, we work with it. But when we don't get source or support then we're putting ourselves in an awkward position by making use of "stuff found on the 'net".

On one hand, I don't want to insult the generosity of Ankit Malpani, James Terwilliger, or anyone else who have contributed, and continue to support that complex effort. I will not ask for that DLL to be published as source.

On the other hand I'm hoping that this component can be reduced to simply the "rocket science" value-add which they're providing, while rolling the source for the more generic functionality into the EdmGen2 core.

For example, the code that allows generation for only selected tables seems to be generic compared to the more sophisticated inheritance functionality. Actually, if someone can point me to a place where I can learn how to do this on my own, I'd be happy (teach a man how to fish...).

Comments are welcome to help provide warm fuzzies for those of us who are shy about making "real" use of code that we can't control/fix/upgrade, etc.

Thanks!

NEW POST: Including Views in models

TonyGravagno — Wed, 04 Nov 2009 06:48:19 GMT

I've hacked up EdmGen2 into a custom utility that generates a ready-to-use assembly from a GUI, simply by pointing it at a database. I'm going to look into the RetrofitModel enhancement to select only desired tables from the entire database. But I also need to generate models/code to query views. Can someone suggest how to do this?

Thanks to all involved in the creation of EdmGen and EdmGen2, and especially for posting the source!

UPDATED WIKI: Home

MikeKaufman — Mon, 19 Oct 2009 21:18:25 GMT

EdmGen2 is a command-line tool for the Microsoft ADO.NET Entity Framework. The tool can be used as a replacement for the EdmGen.exe tool that ships with the .Net framework 3.5 SP1. EdmGen.exe can only read and write the CSDL, SSDL & MSL file formats. However, EdmGen2.exe can read and write the EDMX file format used by the Visual Studio design tools. Additionally, EdmGen2.exe can translate between EDMX and CSDL, SSDL & MSL formats, and the source code can act as examples on using the tooling APIs defined in the System.Data.Entity.Design assembly.

Additionaly, EdmGen2.exe contains some experimental functionality not found in EdmGen.exe. Ankit Malpani, an intern with Microsoft Research in summer of 2008, and James Terwilliger, a Post Doc researcher with MSR, updated EdmGen2.exe with the ability to identify inheritance relatationships in relational database schema. You can access this functionality by using the "RetrofitModel" option. The RetrofitModel option connects to a database instance and constructs an EDM model (csdl, msl, ssdl, and edmx files) that includes inheritance. The tool uses data mining techniques to identify TPT and TPH patterns in the database instance, as well as vertical partitioning, and constructs a suitable model and mapping. To use the tool, launch EdmGen2 with the following arguments:

    /RetrofitModel "connection string" "provider" "entitiy name"

For instance, the following will connect to a local AdventureWorks database, and create files and an EDM instance named AVWorks:

    EdmGen2 /RetrofitModel "Server=(local);Integrated Security=true;Initial Catalog=AdventureWorks;" "System.Data.SqlClient" "AVWorks"

In the previous version (version 1.0.20090401), the RetrofitModel option would bring up a dialog that would allow the user to choose the tables to include in the model. With version 1.0.20091019, the ConceptualEdmGen dll now has two additional public methods that can set the list of tables without bringing up a dialog – one that pulls the list of tables from a file, and one where the list of tables is fed directly to the method as a list of strings.

The EdmGen2 code as it appears in the package uses the “from file” option, where it looks for a file called “Tables.txt” in the current directory, and if found, feeds its contents to the dll to set the list of tables. So for instance, if the following were the contents of the file “Tables.txt”:

HumanResources.Department
HumanResources.Employee
HumanResources.EmployeeAddress
HumanResources.EmployeeDepartmentHistory
HumanResources.JobCandidate
HumanResources.Shift

EdmGen2 would generate (for the RetrofitModel option) a model for all of the tables in the HumanResources schema for AdventureWorks. For both methods, an empty list will result in all tables in the database being added to the model. The table selection UI will still appear if neither table selection method is called.

UPDATED RELEASE: EdmGen2.Zip (version 1.0.20090401) (Mar 30, 2009)

Mon, 19 Oct 2009 21:17:26 GMT

UPDATED RELEASE: EdmGen2 (version 1.0.20091019) (Oct 19, 2009)

Mon, 19 Oct 2009 21:17:07 GMT

UPDATED RELEASE: EdmGen2.Zip (version 1.0.20080601) (Jun 20, 2008)

Mon, 19 Oct 2009 21:16:52 GMT

UPDATED RELEASE: EdmGen2.Zip, version 1.0.20090401 (Mar 30, 2009)

Mon, 19 Oct 2009 21:16:23 GMT

UPDATED RELEASE: EdmGen2, version 1.0.20091019 (Oct 19, 2009)

Mon, 19 Oct 2009 21:15:32 GMT

UPDATED WIKI: Home

MikeKaufman — Mon, 19 Oct 2009 21:14:35 GMT

    /RetrofitModel "connection string" "provider" "entitiy name"

For instance, the following will connect to a local AdventureWorks database, and create files and an EDM instance named AVWorks:

    EdmGen2 /RetrofitModel "Server=(local);Integrated Security=true;Initial Catalog=AdventureWorks;" "System.Data.SqlClient" "AVWorks"

In the previous version, the RetrofitModel option would bring up a dialog that would allow the user to choose the tables to include in the model. The ConceptualEdmGen dll now has two additional public methods that can set the list of tables without bringing up a dialog – one that pulls the list of tables from a file, and one where the list of tables is fed directly to the method as a list of strings.

The EdmGen2 code as it appears in the package uses the “from file” option, where it looks for a file called “Tables.txt” in the current directory, and if found, feeds its contents to the dll to set the list of tables. So for instance, if the following were the contents of the file “Tables.txt”:

HumanResources.Department
HumanResources.Employee
HumanResources.EmployeeAddress
HumanResources.EmployeeDepartmentHistory
HumanResources.JobCandidate
HumanResources.Shift

EdmGen2 would generate (for the RetrofitModel option) a model for all of the tables in the HumanResources schema for AdventureWorks. For both methods, an empty list will result in all tables in the database being added to the model. The table selection UI will still appear if neither table selection method is called.

UPDATED WIKI: Home

MikeKaufman — Mon, 19 Oct 2009 18:38:44 GMT

    /RetrofitModel "connection string" "provider" "entitiy name"

For instance, the following will connect to a local AdventureWorks database, and create files and an EDM instance named AVWorks:

    EdmGen2 /RetrofitModel "Server=(local);Integrated Security=true;Initial Catalog=AdventureWorks;" "System.Data.SqlClient" "AVWorks"

UPDATE: As of 10/19/2009, James added an option to select tables to retrofit from a file.

CREATED RELEASE: EdmGen2 with Reverse Engineering, with a no-UI opt (Oct 19, 2009)

Mon, 19 Oct 2009 18:37:42 GMT

NEW POST: where are the generated files?

Anton93 — Mon, 07 Sep 2009 07:46:57 GMT

Hi

Your software sounds very cool, but when I try to run it I get:
Microsoft Windows XP Version 5.1.2600
(C) Copyright 1985-2001 Microsoft Corp.

J:\>C:\temp\Debug\EdmGen2 /RetrofitModel "Server=127.0.0.1;Port=5432;User Id=pos
tgres;Password=xxxxx;Database=xxxx;" "Npgsql" "xxxx"
Warning: The data type 'geometry' is not supported, the column 'thepoint_lonlat'
in table 'airgis2.public.airports' was excluded.
Warning: The data type 'geometry' is not supported, the column 'thepoint_meter'
in table 'airgis2.public.airports' was excluded.
Executing rule: Convert Entities to Many-to-Many Relationships
Executing rule: Search for TPH Pattern Via Foreign Key
Executing rule: Search for TPH Pattern Via Discriminator Column
Executing rule: Search for TPH Pattern Via Null Value Patterns
Executing rule: Specialize Associations
Executing rule: Check if Entities Should be Marked as Abstract
Executing rule: Search for TPT Pattern and Vertical Partitioning Via One-to-One
Relationships
Completed

But I cannot see any created files in the directory that I ran it from. Where can I find them?
Thanks

CREATED ISSUE: hierarchies based off of foreign keys

dsoltesz — Wed, 29 Apr 2009 13:29:56 GMT

It seems that the hierarchies are based off char columns as discriminants for entity hierarchies, we always use integer columns that are foreign keys to other tables that serve as enumerations and think EdmGen2 should support foreignkey and not char columns

COMMENTED ISSUE: Feature Request: The ability to add Database Views to edmx

rami2 — Fri, 10 Apr 2009 22:41:41 GMT

COMMENTED ISSUE: Feature Request: The ability to add Database Views to edmx

rami2 — Fri, 10 Apr 2009 22:37:36 GMT

Hello again.

I would like to request the ability to add database views to the generated edmx from the command line.

For example, to generate an edmx from all the tables (building upon a previous feature request) and views that exist in a database, I expect to be able to just run the following:
EdmGen2.exe /ModelGen "Server=(local);Integrated Security=true;Initial Catalog=AdventureWorks;" "System.Data.SqlClient" "AVWorks" /AllTables /AllViews

Thank you.
Comments: It seems EdmGen2 actually tries to add database views to the model, but I am getting an error when I run EdmGen2.exe /ModelGen "Server=(local);Integrated Security=true;Initial Catalog=MyDatabase;" "System.Data.SqlClient" "MyDatabase" Errors occurred during generation: Warning: The table/view 'MyDatabase.dbo.MyView' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view. Any advice? Thank you.

CREATED ISSUE: Feature Request: The ability to add Database Views to edmx

rami2 — Fri, 10 Apr 2009 22:24:21 GMT

CREATED ISSUE: Feature Request: The ability to suppress displaying the "Select Tables for Consideration" dialog and just consider all existing tables

rami2 — Fri, 10 Apr 2009 11:55:29 GMT

Hi.

I would like to request a new feature where EdmGen2.exe does not display the "Select Tables for Consideration" dialog and just considers all the existing tables.

For example, I would expect an additional command line parameter similar to the following:
EdmGen2.exe /RetrofitModel "Server=(local);Integrated Security=true;Initial Catalog=AdventureWorks;" "System.Data.SqlClient" "AVWorks" /AllTables

Thank you.

CREATED ISSUE: Feature Request: add the ability to specify the namespace the generated code is given

rami2 — Fri, 10 Apr 2009 11:45:01 GMT

If I were to run:
EdmGen2.exe /ModelGen "Server=(local);Integrated Security=true;Initial Catalog=AdventureWorks;" "System.Data.SqlClient" "AVWorks"
and then
EdmGen2.exe /CodeGen cs "AVWorks.edmx"
The generated AVWorks.cs file will be given a namspace of AVWorksModel.

I would like to request the ability to specify the namespace the generated cs file is given on the command line similar to the following:
EdmGen2.exe /CodeGen cs "AVWorks.edmx" WebApplication.Models

Thank you.

CREATED ISSUE: Feature Request: add the ability to specify the generated code file name

rami2 — Fri, 10 Apr 2009 11:39:40 GMT

Hi.

Thank you for EdmGen2.exe.

I would like to request the ability to specify the generated code file name on the command line.

For example,
If I wanted to generated code to be saved in "Model1.Designer.cs" instead of the default "Model1.cs", I expect to run EdmGen2.exe similar to:
EdmGen2.exe /CodeGen cs Model1.edmx Model1.Designer.cs