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 all, I'm writing a job that synchronizes data externally to Xero. Unfortunately due to the short term OAuth tokens, I'm forced to decorate my Organisation objects with a last synced object. I'm running into some strange issues when querying via 2.2. Here is my object relationship.
Organisation---1----1---XeroAudit
I want to return all records that either do not have a Xero audit record, or who's SyncedDate is less than the Organisation's updated date. Here is my query expression.
IList<Organisation> orgs =
Now, this is the SQL I'm getting
SELECT
As you can see, the last line in my query is not what I'm expecting. I'm getting this. [XeroAudits].[SyncedUpdateDate] < [XeroAudits].[UpdatedDate]
What I expected was this
[XeroAudits].[SyncedUpdateDate] < [Organisations].[UpdatedDate]
since my base query is on an organisation, I'm not clear on why it's using the XeroAudits table instead of Organisations.
Thanks, Todd
|
|
|
This is a known behaviour in 2.x: if Entity.Attribute appears on both sides of a comparison operation, and the LHS includes a traversal, then the RHS will be interpreted relative to the traversal on the LHS, not the original table. Later nightly builds (from build 11855, mid-August) of 2.2 provide the "Nathan's Hat" operator which you can use on the RHS of such an expression to get back to the original table: Entity.Attribute("XeroAudits.SyncedDate") < Entity.Attribute("^.UpdatedDate") // note ^. on RHS Note that you can't use further traversals with Nathan's hat but I believe it will support the specific case you describe here. If not then I'm afraid you may need to move to LightSpeed 3 which provides much greater control over joins. |
|
|
thanks for that Ivan, I just upgraded to LS 3, and it seems that this is painless upgrade from LS 2.2. All our unit tests pass, so I'm keen to upgrade to 3. Is there any other way that is considered "cleaner" to do this in LS3?
thanks, Todd |
|
|
Hi Todd, You can use the Join syntax on the query for this. Excuse any obvious syntax errors in the manually typed code below :) e.g. Query query = new Query() { EntityType = typeof(Organisation) }; query.Join = Join.Outer<Organisation, XeroAudit>("Id", "OrganisationId"); query.QueryExpression = Entity.Attribute<XeroAudit>("Id") == null || Entity.Attribute<XeroAudit>("SyncedDate") < Entity.Attribute<Organisation>("UpdatedDate"); var results = Repository.Find<Organisation>(query);
Jeremy |
|