TableConstraintName and Table.isJoin

6 posts, 0 answers
  1. Richard Harrigan
    Richard Harrigan avatar
    235 posts
    Member since:
    Nov 2009

    Posted 13 Feb 2013 Link to this post

    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
  2. Richard Harrigan
    Richard Harrigan avatar
    235 posts
    Member since:
    Nov 2009

    Posted 13 Feb 2013 Link to this post

    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
  3. DevCraft banner
  4. Dimitar Tachev
    Admin
    Dimitar Tachev avatar
    67 posts

    Posted 18 Feb 2013 Link to this post

    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
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
  5. Richard Harrigan
    Richard Harrigan avatar
    235 posts
    Member since:
    Nov 2009

    Posted 18 Feb 2013 Link to this post

    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);
                }
  6. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 19 Feb 2013 Link to this post

    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
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
  7. Richard Harrigan
    Richard Harrigan avatar
    235 posts
    Member since:
    Nov 2009

    Posted 19 Feb 2013 Link to this post

    Hi Damyan

    Outstanding job!

    Many Thanks
    Rich
Back to Top
DevCraft banner