One to many on other Field name

Hi Guys, We've been using Lightspeed 2.0 successfully, but are now running into a problem.

We have the following setup: We have the need for several Groups in our Lightspeed model, but for normalization, we have one table [Group] in our SQL Server database. The table is simple, Just the Id, a Name and a Description and a GroupTableId field, which is a field to distinguish between the different types of Groups (RegisterGroup, DeviceGroup, etc.)

Next we have a GroupLink table, which is a link table for many-to-many relationships between the [Group] table and some other table. This table has the Id field, the GroupId field, a LinkedTableId, which holds the Id of a Record describing what table to link to and an ObjectId field, which is meant to hold the Id of the record in the table that we link to.

On the database side, this works great.

Now, in our Lightspeed model, we want to use the actual implementations, instead of the generalized [Group] and GroupLink tables.

We have implemented the [Group] and GroupLink tables in the lightspeed model and are using the Discriminator attribute on added Entities to build a hierarchy (I hope my explanation is clear enough).

Through this, we have added a DeviceGroup entity and a DeviceGroupDevice entity. The latter is meant to link to the Device entity (ie. The Device table).

We are then trying to add a one-to-many relationship from Device to DeviceGroupDevice, but this does not work. We then set the Column property of the relationship to ObjectId (as this is the field in the DeviceGroupDevice table holding the Id of the Device table).

While this brings us closer, we now get a 'Unable to materialize field [DeviceId] on type [GenericGroupTester.DeviceGroupDevice].  exception. The InnerException states that the Index is out of range.

I've pulled the relationship from the model and added it in two partial classes (to be able to play around with it) and I've forwarded the DeviceId property to the base Object Id property, but am still receiving the same exception. The weird thing is that the emitted SQL is correct and provides the correct result.

The one-to-many relationship is set as follows:

Hi Robert,

Would it be possible for you to send us the following please?

- your LightSpeed model (as C# or VB code)
- your database schema (as a SQL create script)
- some of your sample data (as a SQL insert script)
- the query you are trying to execute (as C# or VB code)

I am finding it a bit hard to visualise your exact scenario (sorry) and this will mean we can help you more quickly and more accurately.  You can attach files via the Options tab, or if you don't want to post it publicly, email to ivan at the obvious company address.  Thanks!

Hi Ivan,

 A prompt answer as always. Please find scripts to create the database, the schema and the data as requested. Also find enclosed a very basic solution, containing the model. I'm trying to get a One-to-many relationship working from DeviceGroup to DeviceGroupDevice and from Device to DeviceGroupDevice. Also, ideally when (if?) we get this to work, we would want a ThroughAssociation between DeviceGroup and Device.

 I trust this will give you enough info. If not, please let me know.

Kind regards,

Robert

Hmmm, looks like it didn't pick up my second attachment, so here it is (again).

Kind Regards,

Robert

Hi Robert,

I haven't been able to find an elegant solution for this, but here is something that works in the limited sample scenario -- but it may not scale well enough to be practical in your presumably more complex real model.

The fix is to have the relationships only in the derived classes, i.e.:

- Remove _objectId and _groupId (and the associated properties and the Group relationship) from the GroupLink class.

- Remove _groupLinks and the associated property from the Group class.

- Keep DeviceGroup and DeviceGroupDevice the way they are.

The impact of this is threefold:

- You need to declare relationships in every derived class, which I realise is a bit tedious if you have a lot of derived classes.  But since you were declaring strong-typed relationships in the derived classes anyway it shouldn't be any worse.

- You can't work with the relationships polymorphically through the base class.  You can get around this by adding abstract properties to the base class (via a partial) and implementing these in the derived classes (e.g. have an abstract ObjectId property in the GroupLink class, and implement it in DeviceGroupDevice as backing onto the DeviceId property).  Again I realise this is a bit tedious and repetitious, though on the positive side it gives you somewhere to catch category errors such as using the wrong kind of GroupLink for a given Group.

- You can't have "plain old Groups" (or GroupLinks) -- that is, a [Group] or GroupLink record whose discriminator doesn't match any derived class won't have relationships (or, if you make the base class abstract, won't be loadable at all).  Every Group or GroupLink will have to be of a specific type with a specific discriminator.  I don't know whether this will be an issue for you.

I hope this makes sense (I can provide my modified version of your code if you want) and I would welcome your comments if it isn't practical for the real model.

Hi Ivan,

 Yes, I would like to see your solution, as I have been trying to create something as you describe, but have been unable to get it just quite right. Moving the relationships into a partial class is, while a bit less convenient, no problem and since we're not looking to a) have huge numbers of groups (initially we're looking at 3 groups and 4 links) and b) having the Group and GroupLink tables directly accessible, this sounds to be what we're looking for.

 Thanks again for your help!

 Robert

Attached.  I made the model changes in the C# file rather than the graphical model, but that's just because I don't happen to have the graphical tool installed on this machine -- deleting the base class properties and relationships through the graphical tool should have the same effect, and you should still be able to represent the derived classes and their relationships graphically.

Note that, as you will see from the attached, you do not need to move the "real" relationships into partial classes -- this is only required if you are implementing polymorphic access and then only for the polymorphic bits.

Let me know if you run into any problems getting this working, or if it's not clear what I've done or how it relates to the graphical model.

Brilliant and definitely elegant enough. With this we should be able to figure out how to make it work as we want!

 Thanks, Ivan.

 Regards,
Robert