Access Association Details of NavigationProperty

Question

I'm creating a C# T4 template to scaffold some classes based on .edmx file, so far so good. What I now need though, is a way to access the name of the columns it connects in the database, through a NavigationProperty.

I realized, not long ago, that you can access this information in the .edmx visual designer, under mapping details for an specific NavigationProperty: enter image description here

So basically, if in the T4 template; I already have an instance of the NavigationProperty I want... How can I get the names of the fields it connects? (WeatherOnMondays in this case)

this could help you: http://stackoverflow.com/questions/5365708/ef4-get-the-linked-column-names-from-navigationproperty-of-an-edmx — MiBu, Jul 30 '12 at 16:52
Even though that question has a much simpler approach than mine, the answer indeed works for me, if you be kind enough to answer so I can award you the points :). — PedroC88, Jul 30 '12 at 20:33
Had to enter the code so it is not automatically converted to comment. Thanks, and good luck with t4, I had plenty of fun with it. — MiBu, Jul 30 '12 at 23:30
You can check source from a project I'm working on http://t4csla.codeplex.com/, maybe you will find something useful. — MiBu, Jul 31 '12 at 12:20

score 3 · Accepted Answer · edited May 23 '17 at 11:53

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

2 ways of achieving this:

// 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));

2nd approach:

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);
   }
}

Just noting that this does not work in the case of EF using cross-reference tables behind the scenes to create a many-to-many relationship between two DBSets. The `navProp.RelationshipType.Name` will resolve to the name of the cross-reference table, and not the FK that contains the names of the required fields, so the `AssociationType` object will have no `ReferentialConstraints`. — MCattle, Jul 07 '15 at 21:34
@MCattle yes, I don't like the way EF is handling many-to-many, wish there was option to include table as well. I used to add them without FK and handle them manually, or I added another column to cross-reference table to have it generated inside EF. Third option is to edit T4, but then you are losing updates. Also this code will be a lot simpler in EF7 since it has API. — MiBu, Jul 20 '15 at 16:31

score 0 · Answer 2 · answered Jul 30 '12 at 08:03

0

If you have a NavigationProperty, the fields it connects are represented by the properties ToEndMember and FromEndMember

answered Jul 30 '12 at 08:03

podiluska

50,950
7
98
104

1

But I don't know how to get the Name of the database column it's connecting (the one shown on the picture). – PedroC88 Jul 30 '12 at 12:35

score 0 · Answer 3 · answered Apr 09 '13 at 03:34

This code is simpler. It works fine on my Visual Studio 2012. The AssociationType class details can be found at http://msdn.microsoft.com/en-us/library/system.data.metadata.edm.associationtype.aspx and http://msdn.microsoft.com/en-us/library/system.data.metadata.edm.referentialconstraint.aspx .

<#@ 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 0 · Answer 4 · answered Sep 23 '13 at 01:27

See http://brewdawg.github.io/Tiraggo.Edmx/ and you can install it from NuGet. It serves up ALL of the metadata in your edmx files, including all the mappings, low level SQL data types per column, all that kind of stuff, look at the sample on the page and you'll see how easy it is.

Access Association Details of NavigationProperty

4 Answers4

Linked