New Posts New Posts RSS Feed: Subqueries across multiple datasource keys
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Subqueries across multiple datasource keys

 Post Reply Post Reply
Author
joshpainter View Drop Down
Newbie
Newbie
Avatar

Joined: 27-Aug-2007
Location: United States
Posts: 5
Post Options Post Options   Quote joshpainter Quote  Post ReplyReply Direct Link To This Post Topic: Subqueries across multiple datasource keys
    Posted: 28-Aug-2007 at 2:43pm
I just found out that you can't write subqueries for entities generated from different datasource keys.  I want to explain my work-around and see if anybody can think of a better way.
 
My screen has a simple search box and then a grid underneath that displays the search results.  One of the fields a user can search on is "Customer Name." 
 
My model looks like this:
 
VisitReport.CrmAccount.Name
 
A VisitReport exists in our "main" database, and then CrmAccount represents an account in our CRM database.  Name is a property on CrmAccount.
 
It would have been nice if subqueries worked across datasources, cause then I could have done this query easily.  However, what I ended up with is using the ListManager.  So when the user first pulls up the screen, we get all VisitReports and attach a ListManager to that EntityList.  The Predicate defined for the ListManager checks to see that searchTextbox.Text is contained in VisitReport.CrmAccount.Name.
 
Now as the user types, I've wired up the searchTextbox.Changed event to simply reset the resultsList.ListManager to itself, like so:
 
resultsList.ListManager = resultsList.ListManager;
 
It seems that simply setting this propery forces the ListManager to re-evaluate the list.
 
So, the question - is there an easier/better way to do what I'm trying to do?
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715
Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 28-Aug-2007 at 4:57pm

The standard and recommended solution to the problem is to use two different span queries to get the data from the two databases, then use a cache-only query to get all of the data into the PersistenceManager cache.  In the example below, the Contractor and AddressContractor tables are in two different databases.

 

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)

 

 

 

Back to Top
joshpainter View Drop Down
Newbie
Newbie
Avatar

Joined: 27-Aug-2007
Location: United States
Posts: 5
Post Options Post Options   Quote joshpainter Quote  Post ReplyReply Direct Link To This Post Posted: 28-Aug-2007 at 8:52pm
Works great David, thanks!  Much cleaner...
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down