DevForce 2010: NavigationEntityProperty and foreign key relationships

NavigationEntityProperty and foreign key relationships

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2737
Printed Date: 19-Sep-2025 at 11:01am

Topic: NavigationEntityProperty and foreign key relationships

Posted By: chuckc
Subject: NavigationEntityProperty and foreign key relationships
Date Posted: 01-Jun-2011 at 12:23pm

Given a NavigationEntityProperty how can one determine the foreign key relationship between the parent Entity and a child Entity?

For example, assume a Person table/entity with a GenderId field/property that maps to a GenderId field in a Gender table. How can one extract that information from a NavigationEntityProperty? It appears to be there, but the syntax for getting to it eludes me.

The reason I'm looking for this information is to support an audit system that uses "ghost" tables to store audit history.

Thanks

Replies:

Posted By: sbelini
Date Posted: 01-Jun-2011 at 10:04pm

Hi chuckc,

I believe you are looking for the :

var entityRelation = myPerson.EntityAspect.GetNavigationProperty("Gender").RelationLink.EntityRelation;

Let me know if that's what you are looking for.

Silvio.

Posted By: chuckc
Date Posted: 02-Jun-2011 at 11:15am

Hi Silvio,

Thanks for the reply, but I could use a bit more information. To re-state my question:

Example:

Given a Person class/table and a Gender class/table.

The Person class has a GenderId Guid field and a corresponding Gender object property.

The Gender class/table has a Guid primary key of GenderId

Starting from a DataEntityProperty (or maybe a NavigationEntityProperty) from the Person metadata for the Gender object property, how can I find:

1) The Type/table name of the child property ("Gender" in this example)

2) The property of the gender key on the Person object ("GenderId" in this example)

Here is the start of the code I am attempting to get working, with the areas I'm looking for some help with indicated.

        private static Entity Example_GetScalarNavigationEntity(Entity sourceEntity, Entity journalEntity, NavigationEntityProperty np)
        {

            Entity navEntity = null;
            iCatalystEntityManager mgr = iCatalystEntityManager.DefaultManager;
            Type navPropertyType; // =  np.RelationLink.EntityRelation...??            Guid parentPropertyKey; // = np.RelationLink.EntityRelation.ToRole2Link...??
 
            QueryParameter param = new QueryParameter("pkey", parentPropertyKey);
            string querySQL = string.Format("SELECT VALUE jnl FROM {0} as jnl Where jnl.{1} = @pkey",
                navPropertyType.Name, np.Name);
 
            ParameterizedEsql paramEsql = new ParameterizedEsql(querySQL, param);
 
            PassthruEsqlQuery q = new PassthruEsqlQuery(navPropertyType);

            IEnumerator e = mgr.ExecuteQuery(q).GetEnumerator();
            e.MoveNext();
            navEntity = e.Current as Entity;
 
            return navEntity;
        }

The wealth of properties available on EntityRelation are a bit confusing and complex.

Thanks for your help!

Posted By: sbelini
Date Posted: 02-Jun-2011 at 4:22pm

Hi chuckc,

Getting the type of the child entity is fairly simple:

var np = aPerson.EntityAspect.GetNavigationProperty("Gender").RelationLink.ToNavigationEntityProperty;

Type navPropertyEntityType = np.RelationLink.ToRole.EntityType; // type is Gender

As for getting the FK value, you will need to first get the DataEntityProperty that corresponds to the FK and then get its value:

Guid fkId = (Guid)aPerson.EntityAspect.GetValue(GetFkProp(np).Name);

public DataEntityProperty GetFkProp(NavigationEntityProperty np) {

var fkProps = EntityMetadataStore.Instance.GetEntityMetadata(np.EntityType).ForeignKeyProperties;

var result = fkProps.FirstOrDefault(fkProp =>

ReflectionFns.GetAttribute<ForeignKeyAttribute>(fkProp.PropertyInfo).RelatedNavigationPropertyName == np.Name);

return result;

}

I hope this helps.

Could you share with us what your use case is? It's worth knowing it and this might be a good candidate for a feature in the product.

Regards,

Silvio.

Posted By: chuckc
Date Posted: 02-Jun-2011 at 11:03pm

Thanks, that was just what I needed. I would have spent a long time trying to figure out how to duplicate your GetFkProp() method!

As for a use case, I'm using this to build a general purpose display for changes recorded in "ghost" audit tables. The ghost tables are the same structure as their corresponding "live" table with a few extra tracking fields. Displaying changes to data properties from one ghost record to the next is no problem, but for changes of child object properties linked to other table/classes I want to show something other than the foreign key linking Guid. I am now looking up the corresponding record from the child table/object and displaying that instead of the Guid.

If you'd like more info, feel free to contact me via email.

Thanks again.

Posted By: chuckc
Date Posted: 25-Sep-2011 at 7:29pm

I see that the ForeignKeyAttribute has been removed in release 6.1.2 which breaks the solution you had provided above. I am using that technique quite extensively in my live application.

What is the recommended technique for getting a foreign key property value now?

Thanks.

Posted By: sbelini
Date Posted: 28-Sep-2011 at 6:23pm

Hi Chuck,

Now you don't need to use reflection to get RelatedNavigationPropertyName.

So, in the GetFkProp method, instead of using:

var result = fkProps.FirstOrDefault(fkProp =>

ReflectionFns.GetAttribute<ForeignKeyAttribute>(fkProp.PropertyInfo).RelatedNavigationPropertyName == np.Name);

you'd use:

var result = fkProps.FirstOrDefault(fkProp => fkProp.RelatedNavigationPropertyName == np.Name);

Regards,

Silvio.

Posted By: chuckc
Date Posted: 28-Sep-2011 at 7:43pm

That works nicely - Thanks!