DevForce Community Forum : NavigationEntityProperty and foreign key relationships

NavigationEntityProperty and foreign key relationships : That works nicely - Thanks!

Wed, 28 Sep 2011 19:43:16 -700

Author: chuckc
Subject: 2737
Posted: 28-Sep-2011 at 7:43pm

That works nicely - Thanks!

NavigationEntityProperty and foreign key relationships : Hi Chuck,Now you don't need...

Wed, 28 Sep 2011 18:23:09 -700

Author: sbelini
Subject: 2737
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.

NavigationEntityProperty and foreign key relationships : I see that the ForeignKeyAttribute...

Sun, 25 Sep 2011 19:29:08 -700

Author: chuckc
Subject: 2737
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.

NavigationEntityProperty and foreign key relationships : Thanks, that was just what I needed....

Thu, 02 Jun 2011 23:03:31 -700

Author: chuckc
Subject: 2737
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.

NavigationEntityProperty and foreign key relationships : Hi chuckc, Getting the type...

Thu, 02 Jun 2011 16:22:42 -700

Author: sbelini
Subject: 2737
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.

Edited by sbelini - 02-Jun-2011 at 4:23pm

NavigationEntityProperty and foreign key relationships : Hi Silvio,Thanks for the reply,...

Thu, 02 Jun 2011 11:15:46 -700

Author: chuckc
Subject: 2737
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!

NavigationEntityProperty and foreign key relationships : Hi chuckc,I believe you are...

Wed, 01 Jun 2011 22:04:13 -700

Author: sbelini
Subject: 2737
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.

NavigationEntityProperty and foreign key relationships : Given a NavigationEntityProperty...

Wed, 01 Jun 2011 12:23:57 -700

Author: chuckc
Subject: 2737
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