This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hello,
I have an entity that has a field that is used for both the primary key and foreign key. I'm running into a problem in which the SQL generated to insert a record for this entity contains a duplicate instance of the field which results in the following SQL Exception:
Column name 'DesignObjectId' appears more than once in the result column list.
The entity/table is called "QuestionDesign" and the primary/foreign key field is called "DesignObjectId". Here is the SQL that is generated for an insert (copied from SQL Profiler)
INSERT INTO QuestionDesign
(
DesignObjectId,
DesignObjectId,
LabelHeight,
LabelLeft,
LabelTop,
LabelWidth,
QuestionHelpText,
QuestionTag,
QuestionText
)
VALUES
(
@p4,
@p0,
@p3,
@p3,
@p3,
@p3,
NULL,
NULL,
@p5
);
I'm using Lightspeed v2.2.948.10429 running against SQL 2005.
I noticed that the changelog for v2.2 listed a bug fix that sounded like it might be related:
- Fixed designer error when the primary key column was also a foreign key column
Any ideas how I can get around this problem?
Thanks,
Brett
|
|
|
BTW, when I posted, all of my line breaks were removed. I realize that makes the above post difficult to read. Sorry about that. |
|
|
This scenario is not really supported, because when you do an insert, LightSpeed will allocate the ID itself. You can't (easily) control the ID to force it to be the ID of another entity as you require. In addition, the LightSpeed association mechanism requires a foreign key field to be declared, and (as you have found) you can't have both a user field and the Id property mapping to the same column. To work around this requires a bit of manual effort. You have two problems: first, how to control the ID so that it is a valid foreign key; and second, how to traverse the association. To control the ID, you need to override the GeneratedId() method on your entity. LightSpeed will call this method when you add a new entity to a unit of work. By default, this delegates to the IdentityMethod in force, but by overriding, you can supply your own ID, which you can then choose to be the desired FK value. Be warned that GeneratedId() is called quite early in the lifecycle of an entity and is called only once. You need to make sure that the FK is available for you to return before you add the new entity to any unit of work -- you will not get a second chance later on! To traverse the association, you basically have to bypass the LightSpeed association model, which depends on the presence of a separate foreign key field. So instead of using EntityHolder and EntityCollection properties (or the designer association feature), you will need to implement your own methods or properties using the Id as the key to query on, e.g.: // in the entity where the PK is also a FK // in the entity you are FKing to (e.g. ParentEntity) Regarding the bug fix you mention, this was just to prevent an exception when the user dragged a table whose PK was also a FK. As noted in http://www.mindscape.co.nz/forums/Thread.aspx?ThreadID=1424, it did not attempt to address the underlying runtime issues discussed above. |
|
|
Thanks for your response. I'll give that a try. |
|
|
I will never be inserting a record in my case but I do understand that it is not supported. Legacy work can be a big challenge. Thanks Joe |
|
|
I have also similar type of problem. I have two tables when i insert a new record in a one table then id generated in that table must be used as a primary key in another table. In LightSpeed, we can not assign the ID property because it is read only property. Can you please suggest any solution for that. |
|
|
Hello DHV, See http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=2447&PostID=7155 for a solution and in particular note the GeneratedId() method. See also http://www.mindscape.co.nz/forums/Thread.aspx?ThreadID=1916 and http://www.mindscape.co.nz/forums/Thread.aspx?PostID=2789. |
|