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
|
Can I eager load some columns on a related table. Say I have an Appointment table that stores the fkeys to a person table and to a staff table. When I load the appointment record I would like to also load Person.FirstName, Person.LastName and Staff.FirstName, Staff.LastName. Appointment <-- Person Appointment <-- Staff I've tried adding aggregates to the associations and or to the required columns, but I either get everything or nothing but never just the columns I require. Is it possible or should I give up now? |
|
|
Yes, it is possible, but you need to think of it in terms of excluding columns you don't want rather than including columns you do. Columns within an entity are always loaded when the entity is loaded, unless they are part of a named aggregate. So make the Person and Staff associations eager loaded, but the unwanted columns on the Person and Staff tables part of a named aggregate -- and then just don't specify that aggregate. The downside of this is that those columns will now be lazy even when you load a Person or Staff entity directly, so you'll need to remember to specify the named aggregate when you load a Person or Staff. This can be a bit tedious (and impossible if you have paths where LightSpeed lazy loads a Person or Staff for you), so it may not be worth it, unless Person or Staff has some really big blobs, in which case you probably want to make them lazy by default anyway! For more info, have a look at this article: http://www.mindscapehq.com/blog/index.php/2011/01/19/controlling-lightspeed-entity-loading-with-named-aggregates/ and let us know if you still have questions! |
|
|
Hi Ivan
Thanks for your reply. We are now having to code several areas that require a collection of entities and each entity has to return related entities but with only one or two fields being populated. We have to keep the results as light as possible so returning the entire child records is out of the question. The prescribed method of adding an aggregate to every field will be impossible for us to implement and maintain so that also is out of the question. There just has to be another way to do this. Is there any way you can make this easier for us? Please, please and more please.
|
|
|
Hmm, so what is the desired behaviour here? You want a way to make an entity's fields lazy by default, right? Or should they be lazy by default only when loaded through an association, but eager when the entity is loaded directly? Or lazy by default when loaded through certain named aggregates, but eager by default when loaded directly or as part of a normal lazy load? Probably the third option is the best one... it seems better to force a query to opt into this specialised lazy-by-default pattern rather than have the laziness at the entity level. Does that fit with your design? |
|
|
Thanks for the reply. Appreciate it.
I’ll just explain our situation a little better and then discuss what would be great to have.
We are writing an Appointment system where each appointment is linked to a rather large client table plus other tables like Invoice and User tables etc. The performance of the appointment system is obviously very important so we don’t want to bring back all of the fields on the linked tables but we do require a few of those columns to be returned when we get our appointment collection. These ‘extra’ columns from the linked tables (like Invoice amount, client bad debtor, clients name) are for display purposes only.
What we would like:
It would be great if we could add an aggregate name at the column level on these linked tables so that when we get the main appointment collection we can specify the columns to include. So, for the example above, it would be great if we could go to the lightspeed model and specify the Invoice table field AMOUNT as having an aggregate called ‘AppointmentExtras’ and do the same for the Client table field BadDebtor. Then when we write our LINQ to grab the appointments we can say with ‘AppointmentExtras’ and these extra fields will be eager loaded and included in the SQL generated and be available to use.
Thanks for your time.
|
|
|
Okay, so what you want is basically what we have at the moment except for the option to make the *other* fields in Invoice and Client lazy-loaded instead of eager loaded. But it sounds like you want this behaviour only when Invoice and Client are being eager loaded through the AppointmentExtras named aggregate: for example, if you do a Find<Invoice> then you want all fields to load normally. Is that right? |
|
|
Yes. That spot on. |
|
|
Hello Brendon, We've got a basic version of this coming in the next nightly build. There are some improvements I want to look into so I can't be sure it's final, and there may well be some bugs, but if you want to take it out and kick the tyres then you're more than welcome. The feature is controlled by a couple of new attributes, which you'll need to apply to the targets of the associations (e.g. Client). There's no designer support yet but you can apply class attributes via the partial class. The attributes you need are: LoadPolicy.DefaultFieldsToLazyDuringAggregateLoad(aggregateName) - when the entity is being eager loaded through an association and the specified named aggregate is in play, then the entity's fields will NOT be eager loaded unless so specified in a field-level EagerLoadAttribute. Note that the lazification is triggered by the aggregate being specified on the query; it doesn't matter whether the association is unconditionally eager or eager only because of the aggregate, if the aggregate matches then the fields will go lazy. LoadPolicy.AllFieldsEagerDuringNormalLoad - the existing behaviour of a LightSpeed entity is that fields marked with an aggregate are lazy loaded unless the aggregate is specified. This attribute overrides that behaviour and forces LightSpeed to load all fields during a normal load (e.g. a Find or FindById for this entity type, a lazy load or an eager load when there is no named aggregate in play). So this basically means you can throw field-level aggregates around and they will only come into play when the entity is loading through a named aggregate. I will probably be changing the behaviour of this (and possibly its name) -- currently it always takes effect when the entity is directly loaded, but I'm now thinking it should be ignored when the entity is directly loaded with a named aggregate. Note you will need to apply both attributes to get your desired behaviour. Open to feedback on these behaviours. Sample usage: [LoadPolicy.DefaultFieldsToLazyDuringAggregateLoad("LoadStyle1")] BIG IMPORTANT NASTY ISSUE (which I want to fix but haven't yet): At the moment if you have a collection eager loading through a named aggregate and the collection entity type is defaulted-lazy, you MUST mark the FK field of the reverse association with the relevant aggregate. Failure to do so will cause terrible things to happen (cats and dogs living together, mass hysteria, etc.). I hope to remove this restriction in the next drop, but for now, be warned! |
|
|
Hi. Thanks for doing the changes. Is it in version 4 or 3.1? Cheers |
|
|
V4 only. |
|
|
Hi,
Thanks for your help with this. Really appreciate that you guys are willing to make changes so quickly to help your clients.
I had a go at what you said but when I try to run the query it gives the following:
“The given key was not present in the dictionary”
So to explain what I did, I added the BackReference Aggregate ‘WithAppointmentResources’ to the Association between the Appointment Entity and the Client Entity. This is marked as EagerLoad=false. I then marked one field only on the Client entity as having the same Aggregate name.
After saving, I added the following in the code to the Client partial class:
When I go to fetch my appointments I could see from the SQL generated that it was just trying to get the Appointment table and the Client Identity field and the field I marked from the Client table (which is great – what I wanted)... But it still blew up with the error above.
Any ideas? Have I missed a step (or many steps!)?
|
|
|
Hi Brendon, Could you provide us with the exception stack trace please? Even better, can you provide us with a stripped-down project that reproduces the error? (Console application with just the two or three entities required to reproduce the problem would be ideal.) That would greatly help us track down what is different between our test case and your model. Thanks! |
|
|
Thanks for the quick response. I have attached the stack trace results along with the SQL code and the setup of the associated partial class. Hope it helps! If not we could try a sample app but that might take a little longer. Thanks! |
|
|
Hi, was the stacktrace helpful at all? |
|
|
Sorry for the delay getting back to you on this. I've had a look at the stack trace and the SQL, but I haven't been able to figure out what the problem is. We would at minimum need to see the other two entities involved (Appointment and AppointmentResourceLink?), and ideally the LightSpeed query. Is there any chance of providing us with a small buildable repro project? It should just be a matter of pulling out the relevant entities and a simplified version of the query and bundling them up in a console application. |
|
|
Hi, thanks for the reply. Have attached a sample project where we are trying to load one field (ANNAME) from the F_Vet_Animal table - this is the one that causes it to crash. Cheers. |
|
|
Hi Brendon, Thanks for this. I've tried entering various combinations of test data into the various tables but unfortunately I'm still not seeing an error. Can you refine your query to the point where it only matches one or two records but still causes the error, and then provide us with the IDs and FKs for the matching Appointment and for the various associated entities? We don't need all the fields, we just need to reconstruct the relationships between the entities (the stack trace tells us that the problem is happening when we try to load a collection by foreign key, so we believe we need only the relationships rather than the field data). |
|
|
Mmmm... must be a difference in our data then. When you entered the records in the db did you enter any appointments that are NOT connected to a Patient? Our Appointment table doesn't enfore that relationship and there are plenty of appointment records that are not patient related (Out to lunch etc and will have a PatientId = 0 on the appointment table). I'm wondering whether this is where the issue is??? |
|
|
That's the puppy! If I enter an invalid PatientId then I get exactly the error you report. This isn't to do with field lazy loading -- you will get the same error if you make the Patient association eager load (and get rid of all the load policy stuff on FVetAnimal). From the strict point of view this is a database integrity error. You are asking us to look up a PatientId of 0 and when we do so we find there is nothing there. However we do have a quirk where if an invalid association is eager-loaded then you get the 'key not present' issue whereas if it is lazy loaded you get a null reference -- so when you lazy load we are forgiving of this 'orphan' value, but we are very unforgiving if one pops up during an eager load.. So we probably need to improve this, but there are some architectural and compatibility issues which may make that a bit tricky. My suggestion would be to see if you can solve the problem in the database. A quick fix would be to insert a dummy patient with ID 0. The only worry with that is if other code checks for .Patient == null rather than .PatientId == 0. A technically better solution would be to make PatientId nullable and amend the 0s to NULLs. This fixes the LightSpeed error, as eager loading correctly handles NULLs, but obviously has profound compatibility implications if other applications are using the same database. Is either of these an option for you? We will look at whether we can make eager loading more forgiving of IDs that point nowhere, but this may not be a quick fix. |
|
|
Ok, we should be able to amend things in our db to use nulls instead of 0 but it is a little surprising that a foreign key ID = 0 would cause it to crash out... so it would be great if this aspect could be taken up a few notches on the old 'friendly and forgiving' meter! I'll change the db, see if we are getting the data back that we need, and if so hopefully you can make a wee change so that this option is then in the designer. Cheers. |
|
|
Hi Ivan, I've been testing the changes to the eager loading and this is working well for us. At this stage this gives us what we need : ) So can you please make the changes so that these settings are available in the designer cos at the mo I keep losing the changes I have made when playing with other things in the designer! Cheers, and thanks for your help in all of this.
|
|
|
Hello Brendon, Sorry for the delay in replying -- we've been away at a conference. You can apply the class-level attributes via a partial class instead of editing the generated code, e.g. // Generated code // Your own Animal.cs file The designer has a helpful Refactor > Create Partial Class command on the right-click menu or of course you can create the partial file manually in the normal way. I think the only other attributes are the eager load/aggregate ones which you should be able to enter through the designer already. We will try to get designer support in place reasonably soon but the partial class solution should mean you can still use the feature without having to re-enter the attributes every time you change the model! Let us know if there is anything that does require you to edit the generated code and we'll bump that up the priority list. |
|