TableConstraintName and Table.isJoin

[Richard Harrigan]

Hi

I am looking for the columns associated with a primary key.  I can test isPrimaryKey in Tables.Columns but what if the primary key is multiple columns.  I then need the column order to build a join statement.  I'm sure its there I just haven't found it.

Can you tell me what Table.isJoin is used for?  It sounds like a join table used for many-many.  If so how does it recognize it.

Thanks
Rich

[Richard Harrigan]

Hi,

Just a follow-up to my question.  I found an example of a multi-column key primary key in the SqlServer AdventureWorks database ([Sales],SpecialOfferProduct). This would have worked for me but only because the Table.isPrimary columns were in the correct order.  It could easily have be in the wrong order. The OpenAccess Table class has a constraint class that has a SourceColumns property that shows both columns that need to be joined to the primary key.  It seems to me there should be a DestinationColumns property to allow correct joining.  I know sql server keeps track of this and I can access it with SMO.  I am using OpenAccess metadata to access multiple database types to generate end-user Hieratical as-hoc queries.

Thanks
Rich

[Dimitar Tachev]

Hello Rich,

 
In order to get a collection of the columns associated with a primary key for any of your persistent types you could use the GetIdentityMembers method of our MetadataWorker which returns a collection of the primary keys of the given persistent type.

For your convenience I prepared the following code snipped demonstrating that approach over the  [Sales].SpecialOfferProduct table of the AdventureWorks database that you've mentioned:

using (EntitiesModel context = new EntitiesModel())
{
    MetaPersistentType type =
       context.Metadata.PersistentTypes.FirstOrDefault(t => t.Name == "SpecialOfferProduct");

    var identityMembers = MetadataWorker.GetIdentityMembers(type);

    Console.WriteLine("Identities of " + type.Table.Name + " table:");

    foreach (MetaPrimitiveMember id in identityMembers)
    {
        Console.WriteLine(id.Name); // the identity field
        Console.WriteLine(id.Column.Name); // the identity column
    }
}

Regarding to your question about the IsJoin property of our MetaTable class - it indicates whether the table is a join table in the database as described in this documentation section.

Note that in case you would like to use the OpenAccess LINQ querying capabilities, it will be enough to know the property names of the Identity members. On the other hand, if you need to build custom SQL statements, once you retrieve the column names using the above-mentioned approach, you can use our ADO API to create, execute and materialize the results of your queries.

I hope this helps. Do not hesitate to contact us back if you need any further assistance.

Regards,
Dimitar Tachev
the Telerik team

[Richard Harrigan]

Hi,

I think using an Entity Model requires me to build one in Visual Studio??  What I am doing is using OpenAccess to query any supported OpenAccess database dynamically to get the database metadata and that is working great for me with the exception of the PK constraint.  I can tell the columns that belong to the primary key but not the column order in the case of a multi-column key.  If I have to build an  EntityModel for a database then I can't do what I need to do unless I can build it on the fly from within my program.  

See my code snippit below to see how I get metadata. 

 I tried MetaPersistentType type = container.PersistentTypes.FirstOrDefault(t => t.Name == "Customers") but there were no PersistentTypes.  

I looked at the documentation for isJoin and yes it says it is a join table but nothing else.  What I would like to know is how is a isJoin table detected by OpenAccess or is it user supplied metadata?

Thanks
Rich

 ISchemaReader reader = GetSchemaReader(connInfo);
            String[] schemas = reader.GetSchemas();
            SchemaReadParameters readParameters = new SchemaReadParameters(schemas);
            readParameters.Indexes = Indexes;
            readParameters.StoredProcedures = StoredProcedures;
            readParameters.TablesAndViews = TablesAndViews;

            MetadataContainer container = reader.GetSchema(readParameters);

            foreach (MetaTable table in container.Tables)
            {
                Console.WriteLine(table.Name);
            }

[Damyan Bogoev]

Hello Richard,

I have prepared a sample application which demonstrates how to retrieve the primary key columns for a given database. 

During schema reading Telerik OpenAccess ORM does not detect if a table is a join table, this is being calculated in the mapping. The application shows how to handle this scenario.

Hope that helps.

Regards,
Damyan Bogoev
the Telerik team

[Richard Harrigan]

Hi Damyan

Outstanding job!

Many Thanks
Rich