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
|
Hi, We have a number of situations where a parent record has children that are active in specific date ranges. So we often want to retrieve parent records, where their current (i.e. active) children satisfy certain criteria. For instance, find me all the Widgets that we have rented out to customers, where the current location of the widget satisfies some rule. (Each Widget has many locations, only one of which is current). The general pattern is that, in each collection of children, there is one "special" child, and that is the one on which we want to apply our criteria. We have tried several things, as follows:
Using Single, as above, returns a NotSupported error in the current nightly build (and just silently generates the wrong SQL in LS 4.0) We also tried using Any, like this:
That actually worked, but did not generalize to more advanced queries, particularly when the stuff inside the any needs to reach out to related tables. Gives a NotSupported error on current nightly and LS 4.0. Finally, we resorted to this workaround, which actually works
But what we really wanted was this
which is just a cleaner implementation of the first example above. (It fails in LS 4 by just generating the wrong SQL, and fails in the same way in the Nov 5 Nightly Build). This seems like a reasonable thing to do, so the point of this message is just to encourage you busy folks to put this on your to-do list ;-) Perhaps for LightSpeed 5-point-something ;-) In the meantime we'll stick with our workaround (as long as it passes our remaining tests over the next few days). I will attach a sample project that includes all of the above. Plus, for anyone who's interested, it also includes a better version of our workaround. In the better version, you're able to build up the query incrementally/conditionally, by wrapping it with additional criteria before enumerating the results. |
|
|
Here's the sample code |
|
|
One final note to anyone else who tries the same things that we've been trying: there may be another issue, with "Ninja" properties that reference DateTime.Now (which would be needed if "currentness" of child records was based on the date). See http://www.mindscapehq.com/forums/thread/424504 |
|
|
Hi John, Thanks for the detailed overview of this and the attached tests - very useful to get some understanding on whats going on here. Ill pop this on the backlog but I cant make any commitments around this. The guts of what you are running into is that we quickly run into limits on translating uses of LINQ operators within the scope of an existing operator call or within a predicate (say inside the Where or Any calls). The difference you have noted between the 4.0 RTM and the nightlies is that we have tried to add more uses of the NotSupportedException where it isn't actually a supported translation as opposed to just blowing up (e.g. with your original DateTime.Now Ninja Property example) in the guts of the LINQ provider. Ill certainly keep you posted on any developments here though :)
|
|