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
|
Hi Guys, I'm having some problems referencing table B collection from table A where table B has a composite key. I'm getting the following error, Invalid column name <Composite key name> Thanks Again for yuour help, Josh |
|
|
Use the ValueObjectColumnAttribute to map the fields in the A.BId value object property to the FK column names in the database. See http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=2653&PostID=8129 for an example. |
|
|
I think I'm being a little thick; I have a dal table with an int as a primary key, and a property table with PropertNo and a DealNo as the CK. I looked in the Automatically generated code and found that it generated this for me: public partial struct PropertyId ... well that's half the problem; then in the property partial class i added the following: [ValueObject] now i'm getting the following error... {"values must have at least one element\r\nParameter name: values"}
Cheers, Josh |
|
|
Looks like you have an In query (or a Contains method in LINQ) but the collection being passed to In (or on which Contains is being called) is empty. |
|
|
Ahh yes, that was exactly the problem (mybad), but now i'm back to the same error, any thoughts? |
|
|
So what is happening is that Deal table has key DealNo which is the FK to the Property table which has a CK of DealNo and PropertyNo. So i Can't have: [ValueObject]
in the Deal pertial class as there is no propertyId on the deal object only on the property object. |
|
|
You're mapping the columns in the wrong class. The PropertyNo and DealNo mappings in the Property class are already okay. You need to apply the column mapping to the *foreign* key field, i.e. in the ThingThatRefersToAProperty partial class rather than in the Property partial class. Note that because the generated code already contains a foreign key field, you will need to set Generation to None on the association in order to suppress it, and will therefore also need to recreate the association members in the partial classes (at both ends). |
|
|
Ah, now I see the problem. In LightSpeed terms, the DealNo in the Deal table cannot act as a foreign key field, because it doesn't uniquely identify a row in the Property table, because the Property.DealNo column is not a primary key (the composite { Property.DealNo, Property.PropertyNo } is the primary key). However, you can model this from the other direction i.e. a Property has a Deal (because Property.DealNo *does* uniquely identify a row in the Deal table) and a Deal has a Properties collection. To do this you will need a recent nightly build and you'll need to use the ForeignKeyFieldAttribute on the Property._deal EntityHolder. See http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=2990&PostID=9499 for more info. |
|
|
Yes that's what i'm doing, the deal class calls a collection of properties (property class has the CK), the problem is that the deal class does not contain both of the CK feilds for the property. Deal class has dealNo PK Property class has dealNo and PropertyNo CK Deal class does not contain PropertyNo.
|
|
|
Perhaps it's better to just do a search for the properties i need based on the DealNo? |
|
|
So from our point of view that means that Property.DealNo is the effective FK, because it's Property.DealNo that identifies a unique Deal, rather than Deal.DealNo identitying a unique Property. So you need to create an EntityHolder<Deal> in the Property class, and an EntityCollection<Property> in the Deal class. Then you need to set ForeignKeyFieldAttribute on the EntityHolder<Deal> member, as shown in the previous link, with the path "Id.DealNo": partial class Property { This tells LightSpeed that it's the DealNo column of the PK that identifies which Deal is associated with this Property. You'll also need to get rid of the association in the designer, or set it to Generation = None, because the designer doesn't yet support the "association key is part of the ID" scenario. That said, I'm worried this may result in ordering issues when you need to do an insert. If Deal.DealNo is a FK in the database, you'll be wanting to insert the Property before the Deal, otherwise the FK constraint on Deal.DealNo will fail. But LightSpeed will want to insert the Deal before the Property, because it thinks that the Property has a reference to the Deal. Are you able to change the database schema so that the FK runs the other way? |
|
|
Yes, doing a search rather than having an association may be the best plan given the insert ordering issue I mentioned. |
|
|
that is unfortunate :-( |
|