DevForce Community Forum : How to do equivalent of left join

How to do equivalent of left join : Actually I had tried spans of...

Mon, 16 Jul 2007 15:57:55 -700

Author: Customer
Subject: 308
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

How to do equivalent of left join : To define a span to an Entity...

Mon, 16 Jul 2007 15:57:21 -700

Author: IdeaBlade
Subject: 308
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.

How to do equivalent of left join : Using the same example, our requirement...

Mon, 16 Jul 2007 15:56:18 -700

Author: Customer
Subject: 308
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

How to do equivalent of left join : In looking at your question I'm...

Mon, 16 Jul 2007 15:55:16 -700

Author: IdeaBlade
Subject: 308
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(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

How to do equivalent of left join : This works if all the related...

Mon, 16 Jul 2007 15:53:53 -700

Author: Customer
Subject: 308
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).