4
Vote

Reference Issue with Production.Document

description

Entity Framework reports the following error when attempting to generate an entity data model:
Unable to generate the model because of the following exception: 'The table 'AdventureWorks2008.Production.Document' is referenced by a relationship, but cannot be found.
 
I'm making the assumption that this is not a problem with Entity Framework, but I really do not know how to verify. I have seen this reported elsewhere on the internet as well, such as here: http://www.sanderstechnology.com/2011/wcf-data-services-walkthrough-part-1-setup/10573/

comments

DerrickVMSFT wrote Oct 17, 2012 at 4:51 PM

I'll take a look at this today.

DerrickVMSFT wrote Oct 23, 2012 at 4:13 AM

Hello,

I will not be able to respond to this issue for about a week. Are you blocked from doing something else by not having more info?

Thanks,
Derrick

SapphireGirl wrote Nov 30, 2012 at 1:03 PM

Hi Derrick, Any word on a fix for using the AdventureWorks2008_Data.mdf in creating an Entity Data Model. I had the same problem as above using Visual Studio 2010, .NET 4.0 (System.Data.Entity.dll), and not related yet, my EntityFramework.dll is 4.3. I am using another database I created with CodeFirst but would like to use this database if possible. Thanks

Justine

SapphireGirl wrote Nov 30, 2012 at 3:45 PM

Hi Derrick, yesterday I tried to use the AdventureWorks2008_Data and the AdventureWorks2008R2_Data and I was not able to generate a model even when I took out all of the Production tables. Not sure why but today I took out just the Production.Document table and was able to generate a model from both. As I recall, yesterday it was a LINQ To SQL error in that table with Entity Framework. I could try and reproduce if you need me to. Moving on though. THX
Justine

rogertranchez wrote Dec 5, 2012 at 2:03 PM

The table Document contains a hierarchyid column "DocumentNode". This type is incompatible with Entity Framework so the table cannot be processed.

I've tried to convert it to NVARCHAR(1000) for the time being, but Management Studio says I cannot do it on an 'online database'...

DerrickVMSFT wrote Dec 6, 2012 at 3:27 PM

You will need to create a view on Production.Document and CAST the DocumentNode as nvarchar(15). Then you can use EF to create an entity for Production.vDocument.
Example:
CREATE VIEW Production.vDocument
AS
SELECT CAST(DocumentNode AS nvarchar(15)) AS DocumentNode, Title FROM Production.Document

I hope this helps,
Derrick

moozzyk wrote Dec 27, 2012 at 6:29 AM

I looked at it for AdventureWorks for Sql Server 2012 but I think it is the same thing you hit for 2008R2/
The issue here is that the Production.Document table has a key that is of HierarchyId type and EF currently does not support HierarchyId type. 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 entire entity from the model. For excluded entities you should be able to find them commented out in the model when you open it with an Xml/Text editor. In this particular case this is what will see:
<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 unable 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 Production.Document in the wizard and you should be good to go. EF will ignore all references to this entity since you excluded it and therefore there should be no error.

Link to a related work item on Entity Framework codeplex site : http://entityframework.codeplex.com/workitem/695

moozzyk wrote Jun 20, 2013 at 9:41 PM

The new EF designer (Beta 1 available here: http://www.microsoft.com/en-us/download/details.aspx?id=39076) will not try creating relationships to non-existing tables so instead of the error and empty model you will get a model where invalid entity types/sets and relationships are commented out.

jaafar_abusair wrote Jan 2 at 10:01 AM

Hi,

This issue because the schemas thing, the table name is Document and belong to Production schema.
but the entity framework read the table name as Production.Document and it's not found.

regards