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 firstname.lastname@example.org
Some time ago we started to design a flexible model for a CRM system. Below i'll try to explain how we intended the model to work
In this system we have <Contact>s which can act in a <ContactRole>
We can Link a <ContactRole> to another <ContactRole> using a <ContactRelation>.
Example: <SoftwareCompany (inherits from ContactRole)> Mindscape has a <SoftwareCompanyProgrammer (inherits from ContactRelation)> link with <Programmer (inherits from ContactRole)>Jeremy.
By doing this we gave ContactRelation a ParentId and a ChildId (which is always a forgein key to ContactRole, but sometimes a <SoftwareCompany> and other times a <Programmer>. In this case we override Lightspeed not to use SoftwareCompanyId but ParentId, so we can re-use the ParentId column for all the links. The same counts for ChildId.
This all works fine when we have 1 of these links. However Lightspeed starts to give errors when we add a second kind of link
Example: Example: <HardwareCompany (inherits from ContactRole)> Dell has a <HardwareCompanyCustomer (inherits from ContactRelation)> link with <Customer(inherits from ContactRole)> Rolf.
The first thing that starts to fail is the Model Designer, which indicates that <Programmer> and <Customer> both have a foreign key to ChildId on ContactRelation, this is true, and will not cause any problems because <Customer> and <Programmer> are both <ContractRole>, so it's the same foreign key.
The fact that the Model Designer has a hard time to resolve this, I can understand. However when I ask the Model to save anyway, the cs file looks fine. Attributes indicate that ChildId is used in both <SoftwareCompanyProgrammer> and <HardwareCompanyCustomer>.
When I run the code and try to use a through assosiation however it gets a bit weird, because I now get the error:
I didn't expect to get this error, because the cs file of the Model (in my opinion) contains the correct information to do this action. In both classes it states to use the ParentId and The ChildId column.
I understand this a quite a complex explanation, so I included a UnitTest project, which shows both the model designer validation Error and some failing test to show my problem.
I hope you can look at this problem and give me some advice on solving it.
This code was tested using the LightSpeed31Professional-20101112.msi
We don't support mapping differently named fields -- in your case, the foreign key fields -- onto the same database column. You must use the same field name for the foreign key in each part of your hierarchy.
Fortunately, you can do this without compromising your API too much. What you will need to do is:
* Create ParentId and ChildId properties. (You can make these private, or set the Generation to FieldOnly, if you don't want people to see them.)
* In the SoftwareCompanyProgrammer.SoftwareCompany association, set the Key Property Reference to ParentId. This tells the association not to automatically generate a FK property named SoftwareCompanyId (which you would have to map), but instead to use the ParentId (which is already correctly mapped) as the FK. Similarly for your other associations.
In this way you can keep the same field names, ParentId and ChildId, throughout the hierarchy, which enables LightSpeed to map the columns correctly in all parts of the hierarchy, but have meaningful association names.
The one caveat with this is that if you need to query by ParentId or ChildId then you will need to expose the ParentId and ChildId properties and use those names instead of the more meaningful SoftwareCompanyId. You can get around this by hand-coding the implementation and using the QueryPropertyAttribute. Set ParentId's Generation option to None, then implement it in the partial class as follows:
QueryPropertyAttribute enables LightSpeed to map public property names in LINQ queries to the private field names that it needs for SQL generation. So with this in place you have fields consistently named _parentId and _childId throughout the hierarchy (which enables the column mapping to succeed) but properties that are named appropriately to the derived domain class.
Hope this makes sense -- let me know if you need any further info.
Thanks for the fast reply!
I implemented your solution on my sample project and it's works perfectly and for future reference I posted the fixed sample project in this thread.
I do however came across two other things (I think not related to this sample) while writing unittests to verify if my model was correct after the fix. Both of the errors are included in the (new) sample project as failing tests.
When i try to query:
I get an no such column: t0.ContactId
The generated query is:
As can be seen here: t0 is used in the EXISTS but t0 was never assigned (and used in the "main" query) as an alias for the table.
When I try to query:
var test = (from h in _uow.Current.HardwareCompanies
I end up having: test.HardwareCompany and test.Customer being exactly the same Contact record
The following sql is generated:
It does prefix all the columns with the alias, but however skips t1 (which should contain the Contact information of the HardwareCompany.
I hope you could also help me with the other 2 quering issues.
Thanks for the repro tests - I have added in some fixes for these two issues and they will be available in the next nightly build.
We retesten the 2 issues and they are both fixed