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 am building a WCF-based client-server application. In this application, I have 4 different entities that all have very similar properties. I am able to model these properties as Attributes, using the following table definition: CREATE TABLE [dbo].[Attributes]( The idea is to assign each of the 4 perant entities a unique identifier (1 through 4, for example) and then load the appropriate attributes using something similar to the following: SELECT * FROM Attributes a WHERE a.ParentEntityID = [myID] AND a.ParentEntityType = [myType]; My problem is how to get the LightSpeed engine to recognize this arrangement. A naive "Update From Source..." yields the following in Model.cs: [Serializable] |
|
|
Not quite sure what you're trying to achieve here. Are you saying that a single Attribute has four Parent entities? That doesn't seem right because there's only one ParentEntityId. Or is it that an Attribute has one Parent entity, but that Parent entity might be any of four different types? If so, shouldn't the ParentEntityType be on the Parent entity rather than on the Attribute? (Then you can use single table inheritance in the Parent table and class hierarchy.) Or is it that an Attribute has one Parent entity, and that Parent entity is always of the same type, but the *relationship* can be one of four types? That seems to be what your example SQL is saying, but not what your column names and description of the situation are saying. If you could clarify then we'll be happy to advise! |
|
|
OK, so I got ahead of myself a bit. I edited the Model.cs file, and VS still complained, so I changed the properties on the designer for each association, so that ParentEntity becomes ParentDevice, ParentOutlet, etc. Now I have 4 fields named _patentDeviceId, _parentOutletId, etc with corresponding names -- all pointing, supposedly, to ParentEntityID in the table. Is that even going to work?
Dave |
|
|
Oh, is the idea that ParentEntityId should be able to point to one of four different tables depending on the value of ParentEntityType? If that's what you're after, sorry, no, it isn't going to work. You would need to replace the associations with handwritten queries. However, if you organise Device, Outlet etc. into an inheritance hierarchy using Single Table Inheritance or Class Table Inheritance (not Concrete Table Inheritance), and move ParentEntityType into the inheritance hierarchy, then you can have a polymorphic association, which I think would do the same job you're after. |
|
|
There may well be a better way to do this, and if so I'm open to it. What I want to have is an Attribute that can have one of 4 different ParentEntity types, so that a Device has Attributes, an Outlet has Attributes, etc. All the Attributes for each of the 4 different types of entities are of the same structure, and I want to get away from having 4 tables called OutletAttributes, DeviceAttributes, etc. Thus, going from the top down, I have a TestResult that has Attributes, Devices, Outlets and Relays (plus other stuff). Drilling down, each Device has Attributes, each Outlet has Attributes, etc. The SQL SELECT fragment to get the Atributes for a given TestResult t where the TestResult type is assigned the value 1 is what I stated earlier: SELECT * FROM Attributes a WHERE a.ParentEntityID = t.TestResultID AND a.ParentEntityType = 1; That will return all records from the Attributes table associated with this TestResult. For all Attributes for a given Device d where type = 2, it would be: SELECT * FROM Attributes a WHERE a.ParentEntityID = d.DeviceID and a.ParentEntityType = 2; In the TestResult's EntityCollection prior to save, each Attribute in the collection would need to have its EntityType set to 1, and so on for the other entity types (Devices, Outlets, etc). I'm not sure how having the ParentEntityType on the Parent would help at all... Dave |
|
|
We're replying to each other at the same time... I built the model in the database, where I know it does work. I really don't care at all about going back up the hierarchy, I just want to go one direction - down the hierarchy. I guess I don't understand what you are suggesting I do -- AFAIK SQL doesn't support any kind of inheritance... Do I need to build something in the designer and then let LightSpeed create the database? Do you have any examples to illustrate what you are suggesting? Thanks, Dave |
|
|
It works in the database because there's no association in the database: ParentEntityId can't be a foreign key because there's no table for it to be a foreign key to. In which case it works fine in LightSpeed too: instead of having an association, just create a property in the partial class: partial class Device { and similarly for Outlet, etc. I'm not sure how this will interact with your WCF object graph, though -- hopefully Jeremy will chime in on that if you decide to take this route. Regarding inheritance, see: http://www.mindscapehq.com/blog/index.php/2008/11/03/inheritance-in-lightspeed/ http://www.mindscapehq.com/blog/index.php/2009/03/01/getting-started-with-inheritance-in-lightspeed/ http://www.mindscapehq.com/blog/index.php/2010/04/27/class-table-inheritance-in-lightspeed/ |
|
|
Ivan - Looking through the inheritance documentation, I have to say I'm impressed by what you guys can do with the ORM! However, I don't think this is a good fit for what I need to do. It did make me think of a different approach that I think will work, though. How about this - instead of having ParentEntityID and ParentEntityType, I have 4 separate foreign keys - TestResultID, DeviceID, OutletID and RelayID. Each of them models a relationship with a different table, and the unused FK fields for any one given Attribute table get set to 0 (or NULL, if I want to cross over to the DBA dark side). CREATE TABLE [dbo].[Attributes]( I think this will work well with LightSpeed, but I'm not the expert, so I'd like to get your opinion...
|
|
|
Yes, that should work. The downside, of course, is that it allows an Attribute to be associated with, say, both a Device and an Outlet, which is not what you want -- or with no parents at all, which I guess is not what you want either. You can probably prevent that with validation, though. (You may need to override OnValidating rather than using a custom validation since this will be a whole-entity validation rather than a single property validation.) One warning: if you go for the "magic 0 value," then you may need to create dummy entities in your database with the magic ID. Otherwise you could get an error if you inadvertently try to traverse a 'wrong' association. (Alternatively, you can customise the association property getter to check for and handle this case.) My recommendation would be to come over to the DBA dark side and make the FKs nullable, but this will also require a bit of care to get the delete behaviour you'll want -- see this thread, which is written in terms of self-associations but much of it is generally relevant to nullable FKs. |
|
|
Thanks for the feedback. AFAIK I will never be deleting the child records from this dataase, short of a wholesale pre-production wipe of the database to start over. I suppose somewhere down the road someone may want to archive really old records, so I should plan for it... I will take your advice and use nullable FKs. I avoid NULLs as a general rule, as they create headaches and potential hard-to-find bugs in the C# code, but in this case I think the potential for error may be greater if I don't use them. Thanks for the help! You guys rock... Oh, BTW - I usually get emails of all the posts going back and forth and I haven't gotten any for this thread - is that a current problem on your end, or is there a setting somewhere that may have gotten changed? |
|