3

Closed

Cannot generate the entity model diagram from the AdventureWorks sample database

description

repro steps:
  1. Download the AdventureWork sample database from AdventureWorks2012 Data File
  2. Create a Class Library project.
  3. Add a new item of ADO.NET Entity Data Model.
  4. Select Generate from Database
  5. Select New Connection
  6. Select Microsoft SQL Server Database File (SqlClient)
  7. Select the sample database that you download for Database file name
  8. Select all the tables, views, SP, and Functions to include the model.
  9. Check the Output window, and it will show
    Unable to generate the model because of the following exception: 'The table 'C:\USERS\YJHONG\DOCUMENTS\VISUAL STUDIO 11\PROJECTS\CLASSLIBRARY1\CLASSLIBRARY1\ADVENTUREWORKS2012_DATA.MDF.Production.Document' is referenced by a relationship, but cannot be found.
    '.
    Loading metadata from the database took 00:00:06.4616896.
    Generating the model took 00:00:13.3010350.
    Added the connection string to the App.Config file.
    Writing the .edmx file took 00:00:00.0025093.
  10. Check the generated edmx file, and it should not have any entities.
Closed Mar 5 at 5:43 PM by moozzyk
EF does not support HierarchyId type at the moment and therefore some columns/tables are excluded when reverse engineering Db with the designer. You can get more details by opening the created edmx file with the editor - the types that could not be created are commented out and have additional comments.
This is also a duplicate of work item 172

comments

moozzyk wrote Dec 27, 2012 at 1:59 AM

The issue here is that the Production.Document table has a key that is of HierarchyId type and EF currently does not support HierarchyId. EF ignores columns of types it does not understand when creating the model however if it does not understand a key column it will exclude the entity from the model. You will actually be able to find it commented out in the model when you open it with an Xml/Text editor. You will actually see this:

<EntityContainer Name="AdventureWorksModelStoreContainer" />
    <!--Errors Found During Generation:
  warning 6005: The data type 'hierarchyid' is currently not supported for the target .NET Framework version; the column 'DocumentNode' in table 'AdventureWorks.Production.Document' was excluded.
  warning 6031: The column 'DocumentNode' on the table/view 'AdventureWorks.Production.Document' was excluded, and is a key column.  The table/view has been excluded.  Please fix the entity in the schema file, and uncomment.

  <EntityType Name="Document">
    <Property Name="DocumentLevel" Type="smallint" StoreGeneratedPattern="Computed" />
    <Property Name="Title" Type="nvarchar" Nullable="false" MaxLength="50" />
    <Property Name="Owner" Type="int" Nullable="false" />
    <Property Name="FolderFlag" Type="bit" Nullable="false" />
    <Property Name="FileName" Type="nvarchar" Nullable="false" MaxLength="400" />
    <Property Name="FileExtension" Type="nvarchar" Nullable="false" MaxLength="8" />
    <Property Name="Revision" Type="nchar" Nullable="false" MaxLength="5" />
    <Property Name="ChangeNumber" Type="int" Nullable="false" />
    <Property Name="Status" Type="tinyint" Nullable="false" />
    <Property Name="DocumentSummary" Type="nvarchar(max)" />
    <Property Name="Document" Type="varbinary(max)" />
    <Property Name="rowguid" Type="uniqueidentifier" Nullable="false" />
    <Property Name="ModifiedDate" Type="datetime" Nullable="false" />
  </EntityType>-->
  </Schema>
Notice this warning:
warning 6031: The column 'DocumentNode' on the table/view 'AdventureWorks.Production.Document' was excluded, and is a key column. The table/view has been excluded. Please fix the entity in the schema file, and uncomment.

Now in the AdventureWorks database the Production.Document table is referenced by Production.ProductDocument table. Since no entity for Production.Document table was created EF is not possible to create the reference from Production.ProductDocument entity and hence the "Production.Document' is referenced by a relationship, but cannot be found." error.

Since the Production.Document table cannot be really used "as is" by EF the easiest workaround is to exclude this entity when generating the model from entity - check all tables but this in the wizard and you should be good to go. Because you will exclude this entity EF will ignore all references to this entity and therefore there should be no error.

moozzyk wrote Dec 27, 2012 at 5:36 AM

A related (or duplicate) work item: http://entityframework.codeplex.com/workitem/172

Sign in to add a comment