Id2 Issue ORM

[Soham]

hi,

I have been working in Telerik ORM for about few months now and now I have stumbled upon a problem which I am unable to solve.

The ORM returns and Exception which states that there exists a column in a table in my database called Id2, which it is unable to find.
Unfortunately, I don't have any columns with the name specified above.
I have already gone through some posts with similar issues before, but unfortunately none helped me to solve the problem.

The mappings in my database is absolutely fine as I have triple checked them all.

Can some one please help me out with this issue? I am stuck with this for almost two days now.

The exact exception is mentioned below

Telerik.OpenAccess.Exceptions.DataStoreException was unhandled by user code
  Message=Insert of '547427948-' failed: Telerik.OpenAccess.RT.sql.SQLException: The column name is not valid. [ Node name (if any) = ,Column name = Id2 ]
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
INSERT INTO [Some_table] ([Description], [GlobalId], [Name], [Reference], [Id2], [Foreignkeyd]) VALUES (?, ?, ?, ?, ?, ?)
(set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: The column name is not valid. [ Node name (if any) = ,Column name = Id2 ]
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
  Source=Telerik.OpenAccess
  CanRetry=false
  StackTrace:
       at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
       at OpenAccessRuntime.ExceptionWrapper.Throw()
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x)
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.internalCommit(Boolean phase)
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.commit()
       at OpenAccessRuntime.EnlistableObjectScope.CommitChanges()
       at Telerik.OpenAccess.OpenAccessContextBase.SaveChanges(ConcurrencyConflictsProcessingMode failureMode)
       at Telerik.OpenAccess.OpenAccessContextBase.SaveChanges()
       at ThreatModeler.API.Threat.Add(IThreatElement element) in C:\Users\Sohaam\Documents\Visual Studio 2010\Projects\ThreatModeler.API\ThreatModeler.API\Threat.cs:line 105
  InnerException: Telerik.OpenAccess.RT.sql.SQLException
       Message=The column name is not valid. [ Node name (if any) = ,Column name = Id2 ]
       Source=Telerik.OpenAccess.Runtime
       Description=SQLState=;The column name is not valid. [ Node name (if any) = ,Column name = Id2 ]
       ErrorCode=25503
       Number=25503
       StackTrace:
            at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
            at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
            at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
       InnerException:

Thanks,

Soham,
.NET Technology Specialist

[Soham]

OK Guys,

I got my ORM working. How?

Here it goes,

The rlinq file generated by the orm had some weird issues in my case.
On places where I had foreign keys it was making associations with more than one foreign keys.

For example,

Table ABCD has Attributes( Id - Primary Key, Name, EFGHId - Foreign key mapped to Table EFGH)
Table EFGH has Attributes( Id - Primary key, Description)

Now when the orm generates the rlinq file from the table above, in my case it generated associations mentioned below

Source Class - ABCD Properties EFGHId, Target Class EFGH Properties - Id
Source Class - ABCD Properties EFGHId, Target Class EFGH Properties -
Source Class - ABCD Properties EFGHId, Target Class EFGH Properties -

Now, if you see above properly you will find that the first mapping is fine, but the rest two has been generated automatically which has no connection to the tables we have defined above. Also since, the rest two mappings has no target properties associated with it, telerik automatically generates default names for the mappings which happens to be our Id2, Id3 and so on.

Later when a commit action takes place, our db kicks out the queries since we don't have those columns in our tables.

Now the solution is simple, just remove the extra two mappings automatically generated by the ORM and save the new file.
(In the rlinq file right-click on the association of the table and select "edit mapping"  and perform the step mentioned above)

However, this is a very important issue to look after since when we have large Databases, correcting it for all the classes will prove to be a tedious job.

Thanks,

Soham Gupta,
.NET Technology Specialist

[Soham]

Hello,

I would request some assistance from telerik regarding how to get over with this multiple association issue which eventually results in auto-generated id's. At least let me know why is this multiple associations generated on the first place.

Can some one please help me out on this?? Need it badly.

Thanks,
Soham Gupta

[Soham]

Hello,

I finally got the solution to the problem.

This happens when you have multiple foreign key relationships with the same name.
Example,

StudentMaster (Id, FullName, ClassId) - [ClassId Mapped to Id in ClassMaster with Foreign Key Name - "ClassMap"]
ClassMaster (Id, Capacity, Stream)
TeacherMaster (Id, FullName,Stream, ClassId) - [ClassId Mapped to Id in ClassMaster with Foreign Key Name - "ClassMap"]

The problem occurs due to the Foreign Key Name "ClassMap" which is same for both the Tables.

The solution to this is very simple, while creating the db make sure that all the Foreign Key Names are unique, or else if you already have the db created, then you can go for the solution I have mentioned in my previous post.

Thanks,

Soham Gupta

[Damyan Bogoev]

Hi Soham,

The invalid sql statement is generated because of the not-fully defined association between the classes. Currently it is defined using the Default setting for the Source Class property. This means that it will use as underlying column, column named "ID2". The name of the column is calculated by using the primary key column name (“ID”) of the target class and suffixed by “2”, because there is already column with name “ID” in the source class’ table. 
In order to avoid this issue you should define the association between those two classes by specifying the correct underlying column:

- Create a new property in the corresponding class – YourPropertyID;
- Map this property to the YourPropertyID using the Mapping Details Editor;
- Open the Association Editor and select the new property as source property;
- Close the editor and save the diagram;

Greetings,
Damyan Bogoev
the Telerik team

[Soham]

Hello,

Thank you for your reply.

I have been trying to work the same thing out as you mentioned in your reply.
Unfortunately, I couldn't find any relational problems in the db.
Later, as the db I work in, is updated regularly, we have to generate new orm all the time, and then fix the issues for all tables manually.

Now, as this is not possible, we had to look for a permanent solution to it and what I mentioned in my previous post worked like a charm.

If there is any glitch that I missed in my past post, kindly let me know, I would be happy to implement the same on my side.

Regards,

Soham Gupta

[Damyan Bogoev]

Hi Soham,

The solution to this problem, which you have proposed in your previous post, is valid and can be used in order to avoid the naming issues.

I am sorry for the inconvenience caused.

Regards,
Damyan Bogoev
the Telerik team