EF4: Get the linked column names from NavigationProperty of an EDMX

Question

I am generating POCOs (lets say they are subclasses of MyEntityObject) by using a T4 template from an EDMX file.

I have 3 entities, e.g.:

MyTable1 (PrimaryKey: MyTable1ID)
MyTable2 (PrimaryKey: MyTable2ID)
MyTable3 (PrimaryKey: MyTable3ID)

These entities have the following relations:

MyTable1.MyTable1ID <=> MyTable2.MyTable1ID (MyTable1ID is the foreign key to MyTable1)

MyTable2.MyTable2ID <=> MyTable3.MyTable2ID (MyTable2ID is the foreign key to MyTable2)

Or in another view:

MyTable1 <= MyTable2 <= MyTable3

I want to extract all foreign key relations

NavigationProperty[] foreignKeys = entity.NavigationProperties.Where(np => np.DeclaringType == entity && ((AssociationType)np.RelationshipType).IsForeignKey).ToArray();
forewach (NavigationProperty foreignKey in foreignKeys)
{
    // generate code....
}

My Question: How can I extract the column names that are linked between two entities?

Something like this:

void GetLinkedColumns(MyEntityObject table1, MyEntityObject table2, out string fkColumnTable1, out string fkColumnTable2)
{
    // do the job
}

In the example

string myTable1Column;
string myTable2Column;
GetLinkedColumns(myTable1, myTable2, out myTable1Column, out myTable2Column);

the result should be

myTable1Column = "MyTable1ID";
myTable2Column = "MyTable2ID";

Jamie Thomas · Accepted Answer · 2013-12-14T01:53:07.883

The first answer works if your foreign key columns are exposed as properties in your conceptual model. Also, the GetSourceSchemaTypes() method is only available in some of the text templates included with EF, so it is helpful to know what this method does.

If you want to always know the column names, you will need to load the AssociationType from the storage model as follows:

// Obtain a reference to the navigation property you are interested in
var navProp = GetNavigationProperty();

// Load the metadata workspace
MetadataWorkspace metadataWorkspace = null;
bool allMetadataLoaded =loader.TryLoadAllMetadata(inputFile, out metadataWorkspace);

// Get the association type from the storage model
var association = metadataWorkspace
    .GetItems<AssociationType>(DataSpace.SSpace)
    .Single(a => a.Name == navProp.RelationshipType.Name)

// Then look at the referential constraints
var toColumns = String.Join(",", 
    association.ReferentialConstraints.SelectMany(rc => rc.ToProperties));
var fromColumns = String.Join(",", 
    association.ReferentialConstraints.SelectMany(rc => rc.FromProperties));

In this case, loader is a MetadataLoader defined in EF.Utility.CS.ttinclude and inputFile is a standard string variable specifying the name of the .edmx file. These should already be declared in your text template.

score 2 · Answer 2 · answered Apr 09 '13 at 03:30

This code works fine on my Visual Studio 2012

<#@ template language="C#" debug="true" hostspecific="true"#>
<#@ include file="EF.Utility.CS.ttinclude"#>
<#
string inputFile = @"DomainModel.edmx";

MetadataLoader loader = new MetadataLoader(this);

EdmItemCollection ItemCollection = loader.CreateEdmItemCollection(inputFile);

foreach (EntityType entity in ItemCollection.GetItems<EntityType>().OrderBy(e => e.Name))
{
    foreach (NavigationProperty navProperty in entity.NavigationProperties)
    {
        AssociationType association = ItemCollection.GetItems<AssociationType>().Single(a => a.Name == navProperty.RelationshipType.Name);
        string fromEntity = association.ReferentialConstraints[0].FromRole.Name;
        string fromEntityField = association.ReferentialConstraints[0].FromProperties[0].Name;
        string toEntity = association.ReferentialConstraints[0].ToRole.Name;
        string toEntityField = association.ReferentialConstraints[0].ToProperties[0].Name;
    }
}

#>

score 2 · Answer 3 · answered Apr 20 '11 at 14:47

Not sure exactly whether you want to generate code using the columns or not, but this may partly help to answer your question (How can I extract the column names that are linked between two entities?) ...

NavigationProperty[] foreignKeys = entity.NavigationProperties
  .Where(np => np.DeclaringType == entity &&
          ((AssociationType)np.RelationshipType).IsForeignKey).ToArray();

foreach (NavigationProperty foreignKey in foreignKeys)
{
   foreach(var rc in GetSourceSchemaTypes<AssociationType>()
       .Single(x => x.Name == foreignKey.RelationshipType.Name)
       .ReferentialConstraints)
   {
       foreach(var tp in rc.ToProperties)
           WriteLine(tp.Name);
       foreach(var fp in rc.FromProperties)
           WriteLine(fp.Name);
   }
}

EF4: Get the linked column names from NavigationProperty of an EDMX

3 Answers3

Linked