Mapping of combined primary keys

[Maik]

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

[Damyan Bogoev]

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

[Maik]

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

[Alexander]

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

[Maik]

Hi Alexander,

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

Regards
Maik