Print Page | Close Window

How to do equivalent of left join

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=308
Printed Date: 12-Jun-2026 at 1:26am


Topic: How to do equivalent of left join
Posted By: Customer
Subject: How to do equivalent of left join
Date 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).


Replies:
Posted By: IdeaBlade
Date 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.

Posted By: Customer
Date 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.


Posted By: IdeaBlade
Date 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.
 

Posted By: Customer
Date 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


Print Page | Close Window