Subqueries across multiple datasource keys

Since cross-datasource spans (and subqueries) are not allowed, you can get around this by ensuring all the relevant entities are in the cache. Although this brings more data in than if all the tables were in one db, this is the only concession to cross-data source queries that is necessary. You can then perform your query using a query strategy of CacheOnly.

Your query will work with the following slight modifications:

Pre-load the cache with the entities associated with the query, e.g. Contractor and AddressContractor.

You can do this in a variety of ways, the simplest (but also the one that retrieves the most records) is:

mPersMgr.GetEntities(Of Contractor)()

mPersMgr.GetEntities(Of AddressContractor)()

Modify your original query to perform a cache-only operation:

aContractor = mPersMgr.GetEntity(pQuery, QueryStrategy.CacheOnly)

Now, if you had a case where you were only filtering by either the parent or the child (in your original case you are filtering by both so this wouldn’t work), you could use a GetParents or GetChildren call across the data sources.

For example (removed filter on Contractor):

select * from tblContractor

left join tblAddressContractor ac on ContractorID = ac.ContractorID

left join tblAddress a on ac.AddressID = a.AddressID

where ac.TypeID = 3

The corresponding query in code:

Dim pQuery As New RdbQuery(GetType(AddressContractor))

      'Get filtered AddressContractors

pQuery.AddClause(AddressContractor.TypeIDEntityColumn, EntityQueryOp.EQ, 3)

      Dim addressContractors As EntityList(Of AddressContractor) = mPersMgr.GetEntities(Of AddressContractor)(pQuery)

'Call GetParent(s), passing in the intermediate result, “addressContractors”, because there is no other constraint on “Contractor”

      Dim contractors As EntityList(Of Contractor) = mPersMgr.GetParents(addressContractors, EntityRelations.Contractor_AddressContractor)