Object linking when foreign key refers to a unique field instead of the primary key

Is it possible to link objects to eachother by using a unique field from an object instead of the primary key of the object?

For example:

Table "Location"
id as integer (PRIMARY KEY)
name as string (UNIQUE!)

Table "container"
id as integer
locationName as string (and not locationId as integer)

Reasons:
The reason why i can't use the the id number is because the locationname might not always refer to a record in the table "location", but it might also refer to a record of (for example) a table "machine". All "location" names start with an "L" , so "L10023" would be a location which could have id "3", but will have the name "L10023". If the locationName would be "MWrapper1", it would mean the container is currently on a machine with the name of "MWrapper1", which could also have an id of "3".

I was able to create the database diagrams for this in SQL server, but when mapping it with ORM, OpenAccess keeps binding the "locationname" field from the container table, to the "id" field of the location table.

More info:

1)
It does appear the reversemapping file is configured correctly, as it has this info:

<field name="_storageLocation">

<extension key="db-column" value="mssql">

<extension key="db-type" value="VARCHAR" />

<extension key="db-column-name" value="LocationName" />

<extension key="db-sql-type" value="nvarchar" />

<extension key="db-length" value="50" />

</extension>

</field>

2)
While my "container" has a field "locationName", i also had it create a one-to-many list, so i can access my containers from inside my storagelocation object

3)
Openaccess fails when trying to access the StorageLocation.Containers" property, which is a list of container objects.

4)
Error that is generated:
Telerik.OpenAccess.Exceptions.DataStoreException was unhandled
  CanRetry=False
  Message="Telerik.OpenAccess.RT.sql.SQLException: Conversion failed when converting the nvarchar value 'L111_07_4' to data type int.    at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.next()    at OpenAccessRuntime.Relational.fetch.FetchResultImp.rsNext()    at OpenAccessRuntime.Relational.fetch.FetchResultImp.hasNext() "
  Source="Telerik.OpenAccess"
  StackTrace:
       at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
       at Telerik.OpenAccess.RT.ExceptionWrapper.Throw()
       at OpenAccessRuntime.DataObjects.PCStateMan.handleException(Exception x)
       at OpenAccessRuntime.DataObjects.PCStateMan.getObjectFieldImp(PersistenceCapable pc, FieldMetaData fmd, Object currentValue)
       at OpenAccessRuntime.DataObjects.PCStateMan.getObjectField(PersistenceCapable pc, Int32 field, Object currentValue)
       at ....StorageLocation.OpenAccessEnhancedGet_unitLoads(StorageLocation )
       at ....StorageLocation.get_UnitLoads() in C:\...\StorageLocation.vb:line 136
       .........
       .........

Loading the unitload object itself doesn't seem to work either (unitload = my "container" class in the previous explenations.

The following code generates an error at the unitloadResult.single line:

 

This generates the following error:

 

 

 

Hi Myth,

The reasons behind the reported behavior is that OpenAccess links the objects by their internal Object Id's. The ObjectId used internally by OpenAccess to identify an object is composed of the PK fields of the table it is mapped to. So one of the workarounds for you would be to make the unique name field part of the primary key of the Location table and work with composite id in that table.

Kind regards,
Zoran
the Telerik team

Hello Zoran,

You mentioned this is just one of the posssible work arounds. Would you mind me asking what the alternatives are? I'm not really fond of the idea of changing the primary keys on the table, as the table is also linked to other tables.

Thanks in advance.


ps: I was recommended by the Telerik e-mail support to create a support ticket of this case. Perhaps it would be easier to close either this thread or the ticket (ticket id# 285094) to prevent duplicate efforts by multiple Telerik staff members.

Myth, did you get the answer(s) you were looking for?  I can't seem to get to the PITS ticket number you mentioned in your last post.

Hi Roger.

The number in the post was a support ticket, not a PITS number. As for the solution,  I did link to an id field of a the Location table. To be able to connect my "unitloads" to a machine, I linked the unitload to a location, and the location to a machine.

In the end i'm happy with this solution. Like it more than the original idea.