New Posts New Posts RSS Feed: How to do equivalent of left join
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

How to do equivalent of left join
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
Customer View Drop Down
Senior Member
Senior Member
Avatar
User Submitted Questions to Support

Joined: 30-May-2007
Location: United States
Posts: 260 		Post Options Post Options   Quote Customer Quote  Post ReplyReply Direct Link To This Post Topic: How to do equivalent of left join
    Posted: 16-Jul-2007 at 3:57pm
Actually I had tried spans of that kind but always got an “Entity Relations do not connect” message. Going through the tutorial, I see why. I wasn’t connecting the object graph correctly. Thanks for all your help
Back to Top
IdeaBlade View Drop Down
Moderator Group
Moderator Group
Avatar

Joined: 30-May-2007
Location: United States
Posts: 353 		Post Options Post Options   Quote IdeaBlade Quote  Post ReplyReply Direct Link To This Post Posted: 16-Jul-2007 at 3:57pm
To define a span to an Entity type that is more than one level below the entity you are querying, add successive EntityRelations parameters to the AddSpan call.
 
Here is an example from our IdeaBladeTutorial database of defining a span from the Employee entity class to the Supplier Entity class:
 
     aEmployeeSpanQuery = new IdeaBlade.Persistence.EntityQuery(typeof(Employee));
      aEmployeeSpanQuery.AddSpan(EntityRelations.Employee_Order,
          EntityRelations.Order_OrderDetail,
          EntityRelations.Product_OrderDetail,
          EntityRelations.Supplier_Product);
 
This example is taken from the tutorial "Supporting Disconnected Users" in the Fundamentals section of the Tutorials.  If you open the completed solution, and then open the EmployeeForm.cs file in Code View, and look in the region "Connect/Disconnect", this code is found in the GetEmployeeGraphs method.  This method shows several spans that go to different end points starting from the Employee entity.
 
There is also a Tech Tip on Span Queries that has an example of a multi-level span (Order to Product).  This Tech Tip is in level 200.  Tech Tips can be viewed on our Web site, and, for more recent versions of DevForce, can also be accessed from the DevForce menu.
 
Back to Top
Customer View Drop Down
Senior Member
Senior Member
Avatar
User Submitted Questions to Support

Joined: 30-May-2007
Location: United States
Posts: 260 		Post Options Post Options   Quote Customer Quote  Post ReplyReply Direct Link To This Post Posted: 16-Jul-2007 at 3:56pm
Using the same example, our requirement is

      Given a moduleId, get all the modules and all the related Category , ReportMaster and UserReport objects in one database trip. The AddSpan seems to work for 1 level deep (Module à Category) but we would like to go 3 or 4 levels deep.

b)      I think we are looking for a span because it seems like the subquery method works good if we need to implement a where clause on one of the children.

c)       The dot notation is inefficient in certain situations where we are applying this because there are too many database trips being made.

 (It is  1 to many starting from Module à Category à ReportMaster à UserReport)

 If you need more details, please let me know.



Edited by Customer - 16-Jul-2007 at 3:56pm
Back to Top
IdeaBlade View Drop Down
Moderator Group
Moderator Group
Avatar

Joined: 30-May-2007
Location: United States
Posts: 353 		Post Options Post Options   Quote IdeaBlade Quote  Post ReplyReply Direct Link To This Post Posted: 16-Jul-2007 at 3:55pm
In looking at your question I'm a little embarrassed in that the code for the EntityQuery that I sent was more complicated than it had to be.  In fact that probably is the right code if you have a requirement that there be at least one child entity for the parent entity.  If you do not have that requirement, then it would be sufficient to simply define and execute the topmost EntityQuery, i. e.
 

EntityQuery aModuleQuery = new EntityQuery(typeof (Module),"Id",EntityQueryOp.EQ, SomeModuleId);

Module aModule  = mPm.GetEntity<Module>(aModuleQuery);

 

You then access any entities that are descended from the parent entity, such as Category, ReportMaster, and UserReport, using what we call dot notation as shown in the for each statement.



Edited by IdeaBlade - 16-Jul-2007 at 3:55pm
Back to Top
Customer View Drop Down
Senior Member
Senior Member
Avatar
User Submitted Questions to Support

Joined: 30-May-2007
Location: United States
Posts: 260 		Post Options Post Options   Quote Customer Quote  Post ReplyReply Direct Link To This Post Posted: 16-Jul-2007 at 3:53pm

This works if all the related tables have data in them. In the example below, if a child table (say Category) doesn’t have records, then aModuleQuery wouldn’t return any records even though the Module table has records. I guess that is because DevForce doesn’t understand the concept of a left join as we know it in standard t-sql.  Would this be correct? (Referring to pseudocode for EntityQuery sent in response to task 2030).
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down