Composite keys and eager loading

I am currently looking to switch an existing site over to LightSpeed. The database exists and uses several composite keys which will not be possible to change. I am experimenting with eager loading of relationships, both through the use of the EagerLoad attribute using named aggregates.

The Situation

An entity with a composite key, has a reference to another entity with a composite key. The relationships are defined in a partial class and specify the ReverseAssociation and AssociationResolver on the field. The resolver is a custom implementation of IAssociationResolver and appears to be working.

The problem

When adding the attribute EagerLoad to the relationship, it is ignored. The same is true when you specify any Aggregates. This only seems to be a problem when referencing entities with a composite key. Entities without a composite key seem to load correctly.

LightSpeed version: 4.0.887.18671 with MS SQL 2008

Any help would be greatly appreciated!

Cheers,
Brett

EagerLoad is not compatible with custom association resolvers, because a custom association resolver requires entity data in order to compose the association load query -- and of course that entity data is not available until LightSpeed has loaded the entity, by which time it is too late to perform an eager load!

(EagerLoad should work normally with composite keys that do not require custom resolution, i.e. when the foreign key columns do not overlap the primary key.)

Thanks for your quick response Ivan! I'm wondering if the relationship could be set up without a custom resolver then. Given the scenario below, does this seem possible? All Id's are guids. Any workarounds would be appreciated as well.

[Contact]
Id
- ItemId
- OrgId

[Phone] (entity collection we're trying to eager load)
Id
- ItemId
- OrgId
ContactId <- FK to Contact.Id.ItemId

Unfortunately I don't think it can. I'm assuming that Phone.Id.OrgId needs to be a foreign key to Contact.Id.OrgId, and right now, you can't have foreign keys being part of the Id -- you have to use a custom resolver instead.

There's no workaround within LightSpeed: it's an area we'd like to improve but we've not yet found a solution that we can readily implement.

You might be able to work around it by creating a view of the Phone table which duplicates the OrgId, and performing the query through the view, along the lines of:

Table             View                 LightSpeed
ItemId     -----> ItemId        -----> Id.ItemId
OrgId      -----> OrgId         -----> Id.OrgId
           -----> ContactOrgId  -----> Contact.OrgId
ContactId  -----> ContactId     -----> Contact.Id

so that the LightSpeed FK composite no longer overlaps the LightSpeed Id composite and no custom resolver is required.

Obviously however this would create problems for creating entities (need to make sure both 'copies' of OrgId are correct) and for saving entities (probably not possible for such a view to be updateable).

I'll try to take another look at implementing better cross-cutting key support within LightSpeed but I can't promise anything -- I've already failed once so I'm not hugely optimistic!

Again, thank you for your quick and comprehensive reply. It's looking like eager loading may not be possible for the time being.

As it stands, we could do a lookup in the Phone table using only the ContactId. It's done the current way, using the OrgId for performance gains; however, those gains might be rendered moot by the extra queries necessary to populate the relationships.

Theoretically, if one didn't need to use the OrgId for the FK lookup, would it be possible to remove the custom association resolver? While still keeping the composite primary keys. The query to retreive the association seems straight forward:

select * from Contact
where ContactId = @p0 and OrgId = @p1

select * from Phone
where ContactId = @p0

I've tried it without the custom resolver and haven't been able to make much progress. I definitely look forward to any improvements with composite keys in the future. Thanks!

If the ContactId is sufficient to identify the contact, then yes, you can get away without a custom resolver -- but the foreign key field in Phone does need to match up with the ID field in Contact. So you would need to change the Contact entity to have a plain (non-composite) ID, mapped to the Contact.ContactId column, and demote Contact.OrgId to be an ordinary field.

However, if Contact really does require both ContactId and OrgId to uniquely identify it, then you need a composite key on Contact, and in that case, the foreign key field in Phone must also be composite (or use a custom resolver).

Basically the rule is that the foreign key field must have the same type as the ID of the entity it points to (whether that type be primitive or composite), and if it doesn't then you must have a custom resolver.

But it is NOT compulsory for the ID to coincide with the primary key, or the foreign key field to coincide with the database FK constraint, provided your application logic makes sure to satisfy those constraints. (Note that designer-database sync will complain if these don't match up, but the model will work and save fine: it's only the designer schema inferrer that cares about database constraints, the runtime doesn't.)

Hope this makes sense -- it can be a little tricky thinking about the LightSpeed model separately from the database schema so I hope I haven't just confused you further!