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

Thread is closed for posting
7 posts, 0 answers
  1. Myth
    Myth avatar
    54 posts
    Member since:
    Jan 2010

    Posted 22 Feb 2010 Link to this post

    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)

    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.
  2. Myth
    Myth avatar
    54 posts
    Member since:
    Jan 2010

    Posted 22 Feb 2010 Link to this post

    More info:

    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" />



    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

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

    Error that is generated:
    Telerik.OpenAccess.Exceptions.DataStoreException was unhandled
      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() "
           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

  3. Myth
    Myth avatar
    54 posts
    Member since:
    Jan 2010

    Posted 25 Feb 2010 Link to this post

    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:


            Dim unitloadResult As Linq.IQueryable(Of UnitLoad) = _  
                    From u In _scope.Extent(Of UnitLoad)() _  
                Select u Where u.LocationName = locationName  
            Return unitloadResult.Single 

    This generates the following error:



    Telerik.OpenAccess.Exceptions.DataStoreException was unhandled  
      Message="Error reading field ....UnitLoad._storageLocation from ResultSet: System.FormatException: Input string was not in a correct format.    at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.getInt(Int32 columnIndex)    at OpenAccessRuntime.Relational.ResultSetParams.ReadIntNullable(ResultSetParams& p)    at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSetParams& p)    at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSet rs, Int32 firstCol)    at OpenAccessRuntime.Relational.RelationalGenericState.getFieldData(ResultSetParams& para)    at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol) System.FormatException: Input string was not in a correct format.    at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.getInt(Int32 columnIndex)    at OpenAccessRuntime.Relational.ResultSetParams.ReadIntNullable(ResultSetParams& p)    at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSetParams& p)    at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSet rs, Int32 firstCol)    at OpenAccessRuntime.Relational.RelationalGenericState.getFieldData(ResultSetParams& para)    at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol)" 
           at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)  
           at Telerik.OpenAccess.RT.ExceptionWrapper.Throw()  
           at OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.executeQueryAll(ApplicationContext context, ImmutableQueryDetails query, CompiledQuery compiledQuery, Object[] para, Int32 skip, Int32 take)  
           at OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.getAllQueryResults(CompiledQuery cq, Object[] para, Int32 skip, Int32 take)  
           at OpenAccessRuntime.DataObjects.ForwardQueryResult.resolve()  
           at OpenAccessRuntime.DataObjects.ForwardQueryResult.get_Count()  
           at Telerik.OpenAccess.RT.ListEnumerator.get_Count()  
           at Telerik.OpenAccess.Query.QueryContext.PerformDatabaseQuerySingle[T](Expression expression, Boolean& wasNull, String aggregateMethod, Int32 elemAt)  
           at Telerik.OpenAccess.Query.QueryContext.ExecuteDynamic[TResult,T](Expression expressionParam, IQueryable`1 query)  
           at Telerik.OpenAccess.Query.ObjectScopeQuery`2.Execute[TResult](Expression expressionParam)  
           at System.Linq.Queryable.Single[TSource](IQueryable`1 source)  
           at ....DatabaseManager.getUnitLoadAtLocation(String locationName) in C:\Users\...\DatabaseManager.vb:line 64  
           at ....SetStorageLocation(StorageLocation location) in C:\Users\...\frmLocationDetails.vb:line 57  
           at ....stockPanel_DoubleClick(Object sender, EventArgs e) in C:\Users\...\frmStockview.vb:line 211  
           at System.Windows.Forms.Control.OnDoubleClick(EventArgs e)  
           at Telerik.WinControls.RadControl.OnDoubleClick(EventArgs e)  
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)  
           at System.Windows.Forms.Control.WndProc(Message& m)  
           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)  
           at Telerik.WinControls.RadControl.WndProc(Message& m)  
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)  
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)  
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)  
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)  
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)  
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)  
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)  
           at System.Windows.Forms.Application.Run(ApplicationContext context)  
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()  
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()  
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)  
           at ....My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81  
           at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)  
           at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)  
           at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)  
           at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()  
           at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)  
           at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)  
           at System.Activator.CreateInstance(ActivationContext activationContext)  
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()  
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)  
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)  
           at System.Threading.ThreadHelper.ThreadStart()  
      InnerException: System.FormatException  
           Message="Input string was not in a correct format." 
                at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.getInt(Int32 columnIndex)  
                at OpenAccessRuntime.Relational.ResultSetParams.ReadIntNullable(ResultSetParams& p)  
                at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSetParams& p)  
                at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSet rs, Int32 firstCol)  
                at OpenAccessRuntime.Relational.RelationalGenericState.getFieldData(ResultSetParams& para)  
                at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol)  


  4. Zoran
    Zoran avatar
    534 posts

    Posted 26 Feb 2010 Link to this post

    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,
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  5. Myth
    Myth avatar
    54 posts
    Member since:
    Jan 2010

    Posted 26 Feb 2010 Link to this post

    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.
  6. Roger
    Roger avatar
    32 posts
    Member since:
    Feb 2009

    Posted 14 Dec 2010 Link to this post

    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.
  7. Myth
    Myth avatar
    54 posts
    Member since:
    Jan 2010

    Posted 15 Dec 2010 Link to this 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.
Back to Top