New Posts New Posts RSS Feed: NavigationEntityProperty and foreign key relationships
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

NavigationEntityProperty and foreign key relationships
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
chuckc View Drop Down
Groupie
Groupie


Joined: 27-Feb-2010
Posts: 54 		Post Options Post Options   Quote chuckc Quote  Post ReplyReply Direct Link To This Post Topic: NavigationEntityProperty and foreign key relationships
    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
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
chuckc View Drop Down
Groupie
Groupie


Joined: 27-Feb-2010
Posts: 54 		Post Options Post Options   Quote chuckc Quote  Post ReplyReply Direct Link To This Post 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!
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
chuckc View Drop Down
Groupie
Groupie


Joined: 27-Feb-2010
Posts: 54 		Post Options Post Options   Quote chuckc Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
chuckc View Drop Down
Groupie
Groupie


Joined: 27-Feb-2010
Posts: 54 		Post Options Post Options   Quote chuckc Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
chuckc View Drop Down
Groupie
Groupie


Joined: 27-Feb-2010
Posts: 54 		Post Options Post Options   Quote chuckc Quote  Post ReplyReply Direct Link To This Post Posted: 28-Sep-2011 at 7:43pm
That works nicely - Thanks!
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down