Mapping of combined primary keys

Thread is closed for posting
5 posts, 1 answers
  1. Maik
    Maik avatar
    5 posts
    Member since:
    Sep 2010

    Posted 02 May 2011 Link to this post

    I would like to know if it is possible to cover a specific scenario with OpenAccess as I was unable to map it appropiately with Entity Framework:

    I have a couple tables that store unique Identities of different types like Computers, Roles, etc. Each of that tables has an "ID" field that holds an incrementing Integer as Primary Key plus some additional columns for some properties that are specific to each type like Name, Description, etc.

    -----------
    Example
    Table A

    • ID (Int) - PK
    • Description
    • SerialNumber

    Table B

    • ID (Int) - PK
    • Name
    • Location

    -----

    Now I have some additional tables, that contain related information for each of that types. So I have e.g. a table that contains settings for all of those types. This table has a combined primary key that consist of an Integer field, containing the ID of the referencing Type, and a Column with a CHAR as Type Identifier (e.g. 'C' for Computer, 'R' for Role).

    -----
    Table X

    • ID (int) - PK
    • Type (char) - PK
    • ValueA
    • ValueB
    • ValueC

    -----

    Now the mapping would consist of the ID column in both tables plus a static Type Identifier per Identity Type.

    Would be great if I could get a hint on how to get this done.
    Regards
    Maik



  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 05 May 2011 Link to this post

    Hello Maik,

    You could create one-to-many relationship between both types of tables. This help article demonstrates how to achieve that goal. Later on when you insert a new record in the information table you can obtain the ID from the persistent capable object and the corresponding type identifier and persist them to the database.
    Hope that helps.

    Kind regards,
    Damyan Bogoev
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  3. Maik
    Maik avatar
    5 posts
    Member since:
    Sep 2010

    Posted 05 May 2011 Link to this post

    Hi Damyan,

    thanks for the answer but it doesn't really correpsond to what I ask for. Maybe an example helps

    Table "ComputerIdentity"    

    • ID (int) - PK
    • MacAddress (nvarchar)
    • SerialNumber (nvarchar)
    • AssetTag (nvarchar)

    Table "LocationIdentity"

    • ID (int) - PK
    • Location

    Table "Packages"

    • ID (int) - PK
    • Type (char) - PK
    • Package (nvarchar)

    Now "Packages" contains rows like

    1 | 'C' | "Some Text"
    1 | 'L' | "Some Text"
    2 | 'C' | "Some other Text"
    3 | 'C' | "blablabla"
    3 | 'L' | "dummdidumm"

    So three lines map to the ComputersIdentity Table and 2 lines to the LocationIdentity table. There are 4 different Identity Tables and 5 different tables containing additional information for each of those identities. So I have a mixed combination of all of them.
    I can query for each of them and assemble the information together. But what I would like to use is something like

    ComputerIdentities.Packages
    or
    LocationIdentities.Packages

    und just get the associated entries back plus being able to update them and add new ones.

    So a simple One-To-Many relationship doesn't really fit or am I missing something here?

    Regards
    Maik

  4. Answer
    Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 11 May 2011 Link to this post

    Hi Maik,

    Thank you for the clarification. I am afraid that you will not be able to create this kind of reference with OpenAccess because there is no way to define it on database level as well. Such operations are normally done by executing a query and matching the Type column to particular char value, for example 'C'. A relationship cannot be defined unless you add a join table which associates uniquely (for example) the ComputerIdentity entries to the records from the Packages table.

    What you can do to achieve similar functionality is add an endpoint to your OpenAccessContext class which returns the Package objects filtered by type. To do this you will need to create a new partial class with the name of your context and include the following property:
    public partial class DomainModel
    {
        public IQueryable<Package> ComputerIdentityPackages
        {
            get
            {
                return this.GetAll<Package>().Where(p => p.Type == 'C');
            }
        }
    }
    I hope that helps.

    Regards,
    Alexander
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  5. Maik
    Maik avatar
    5 posts
    Member since:
    Sep 2010

    Posted 07 Jun 2011 Link to this post

    Hi Alexander,

    thanks for this tip. Not really the answer I was hoping for but that should work as well.

    Regards
    Maik
Back to Top