How i can perform left outer join in the following table

Product table - productId (pk) - productTitle - pDescription

Product Status - statusId (pk) - productId (fk) - comment

I need to select everything in Product table

DataAccessAdapter daa = clsMethods.GetNewAdapter();
IPrefetchPath2 pp = new PrefetchPath2(SDL.EntityType.ProductEntity);
pp.Add(ProductEntity.PrefetchPathProductStatus);


RelationPredicateBucket bucketbucket = new RelationPredicateBucket();
bucketbucket.Relations.Add(ProductEntity.Relations.ProductStatusEntityUsingProductId, JoinHint.Left)

Above code only return product which product id record in product status id. How i can perform left outer join which select everything from product table

Please post the code where you actually fetch the collection. It isn't clear because it's a prefetchPath, not the main collection. So I don't know exactly where you want to do that.

Additional information (LLBLGen version, RTL, etc) is also needed: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722

Hi Daelmo,

Thank you

1. Basically i need to display everything in Workplane table where a project can have multiple record. The join is between workplane and project table.

2. Project can have status and also does not have status. So i need to get everything in Project table regardless the project has a status.

 private EntityCollection<ProjectEntity> GetProjectData()
    {
        EntityCollection<ProjectEntity> collection = new EntityCollection<ProjectEntity>();


        //SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.Switch.Level = System.Diagnostics.TraceLevel.Verbose;
        DataAccessAdapter daa = clsDatabaseMethods.GetNewAdapter();
        IPrefetchPath2 pp = new PrefetchPath2(PTRSDL.EntityType.ProjectEntity);
        pp.Add(ProjectEntity.PrefetchPathProjectSubGroup).SubPath.Add(ProjectSubGroupEntity.PrefetchPathProjectGroup);
        pp.Add(ProjectEntity.PrefetchPathProjectWorkplan).SubPath.Add(ProjectWorkplanEntity.PrefetchPathPersonResponsible);
        pp.Add(ProjectEntity.PrefetchPathProjectStatus).SubPath.Add(ProjectStatusEntity.PrefetchPathProjectStatusType);
        pp.Add(ProjectEntity.PrefetchPathPersonResponsible);


        RelationPredicateBucket bucketbucket = new RelationPredicateBucket();
        bucketbucket.Relations.Add(ProjectEntity.Relations.ProjectSubGroupEntityUsingProjectSubGroupId);
        bucketbucket.Relations.Add(ProjectSubGroupEntity.Relations.ProjectGroupEntityUsingProjectGroupId);
    
        bucketbucket.Relations.Add(ProjectEntity.Relations.ProjectWorkplanEntityUsingProjectId, JoinHint.Left);
        bucketbucket.Relations.Add(ProjectEntity.Relations.PersonResponsibleEntityUsingPersonResponsibleId);
        bucketbucket.Relations.Add(ProjectWorkplanEntity.Relations.PersonResponsibleEntityUsingPersonResponsibleId, JoinHint.Left);


        bucketbucket.Relations.Add(ProjectEntity.Relations.ProjectStatusEntityUsingProjectId, JoinHint.Left);
        bucketbucket.Relations.Add(ProjectStatusEntity.Relations.ProjectStatusTypeEntityUsingProjectStatusTypeId);

        bucketbucket.PredicateExpression.Add(ProjectGroupFields.ProjectGroupId != 1);

        
        //Add in predicate from search criteria
        if (bucket != null)
        {
            Int32 count = 0;
            while (count < bucket.Count)
            {
                bucketbucket.PredicateExpression.Add(bucket[count]);
                count += 1;
            }
        }

        SortExpression sort = new SortExpression();
        sort.Add(ProjectFields.ProjectTitle | SortOperator.Descending);
        daa.FetchEntityCollection(collection, bucketbucket, 0, sort, pp);

        return collection;
    }

I see you are already familiar with the Relations.Add() overload that accepts a JoinHint. This is how you specify outer joins.

Please note, that Outer joins have no use, when fetching an EntityCollection, as this is already fetching all entities in the target table, you can make use of iunner joins to limit the returned resultSets, but outerjoins to another entity would have no effect.

You should use outerJoins when you are fetching a flat list of fields from more than one entity (fetching a TypedList or a DynamicList), that's when OuterJoins might get handy.

Hi Walaa

Thank you for your feedback. Unfortunately, i still not get all the record from project table since the project does not have a status. As soon i add a status in status table for a project then it will return the data. Pls advice

Please post the Generated SQL to see what is going wrong. I think the LEFT join is indeed in the SQL but the other INNER joins you have make the resulset to be smaller.

SELECT DISTINCT Project.ProjectSubGroupId, Project.ProjectCodeId, Project.ProjectFrequencyId, Project.FinancialDelegateId, Project.PositionId, Project.ProjectEvaluationTypeId, Project.ProjectTitle, Project.ReferenceNumber, Project.DateRequested, Project.TrimFileNumber, Project.RelatedProjects, Project.IsIncludeIndigenous, Project.IsIncludeCommunications, Project.IsIncludeMedia, Project.IsConfidential, Project.ContactName, Project.ContactPhone, Project.ContactFax, Project.ContactMobile, Project.ContactEmail, Project.DateReleased, Project.Duration, Project.Budget, Project.Objectives, Project.IsUnderCabinetSubmission, Project.IsTargetNTGInternal, Project.IsTargetWOGCoordination, Project.IsTargetDCMInternal, Project.IsTargetExternal, Project.TargetExternalDescription, Project.TeamLeader, Project.DateInitialMeeting, Project.IsCompleteEvaluationReport, Project.IsCompleteFinaliseInvoices, Project.IsCompleteEstimateDetails, Project.IsCompleteFileNoteSuggestion, Project.LanguageOther, Project.RegionOther, Project.DateCompleted, Project.PersonResponsibleId, ProjectStatusType.Name AS ProjectStatusName, ProjectSubGroup.Name AS ProjectGroupName, ProjectWorkplan.Description, ProjectWorkplan.ProjectId FROM PersonResponsible INNER JOIN ProjectSubGroup INNER JOIN ProjectGroup ON ProjectSubGroup.ProjectGroupId = ProjectGroup.ProjectGroupId INNER JOIN Project ON ProjectSubGroup.ProjectSubGroupId = Project.ProjectSubGroupId ON PersonResponsible.PersonResponsibleId = Project.PersonResponsibleId LEFT OUTER JOIN ProjectStatusType INNER JOIN ProjectStatus ON ProjectStatusType.ProjectStatusTypeId = ProjectStatus.ProjectStatusTypeId ON Project.ProjectId = ProjectStatus.ProjectId RIGHT OUTER JOIN ProjectWorkplan ON PersonResponsible.PersonResponsibleId = ProjectWorkplan.PersonResponsibleId AND Project.ProjectId = ProjectWorkplan.ProjectId

 LEFT OUTER JOIN
                     ProjectStatusType INNER JOIN

ON what??

Indeed, the sql has been cleaned up it seems. Our runtime always leaves brackets somewhere in joins or where clauses, your query has none of them, so it would be great if you could post the real query being generated without removing any stuff from it.

Hi Guys,

My apology for the late response. I have modified the sql where i found the issue

SELECT Project.ProjectTitle, Element.Name FROM Project INNER JOIN ProjectServiceElement ON Project.ProjectId = ProjectServiceElement.ProjectId INNER JOIN Element ON ProjectServiceElement.ElementId = Element.ElementId

THis currently will return 7 records in total. Project table contain 6 records where ProjectService element table contain 7 records. Therefore i need to return everything from ProjectServiceElement table (7 records). Please advise

However, using LLBLGEN code below it only return 6 records.

private EntityCollection<ProjectEntity> GetProjectData()
{

EntityCollection<ProjectEntity> collection = new EntityCollection<ProjectEntity>();
DataAccessAdapter daa = clsDatabaseMethods.GetNewAdapter();
        IPrefetchPath2 pp = new PrefetchPath2(PTRSDL.EntityType.ProjectEntity);

pp.Add(ProjectEntity.PrefetchPathProjectServiceElement).SubPath.Add(ProjectServiceElementEntity.PrefetchPathElement);

RelationPredicateBucket bucketbucket = new RelationPredicateBucket();

        
        bucketbucket.Relations.Add(ProjectEntity.Relations.ProjectServiceElementEntityUsingProjectId, JoinHint.Inner);
        bucketbucket.Relations.Add(ProjectServiceElementEntity.Relations.ElementEntityUsingElementId, JoinHint.Inner);
 SortExpression sort = new SortExpression();
daa.FetchEntityCollection(collection, bucketbucket, 0, sort, pp);

return collection;
}

If Project table contains 6 records then your above code will return always 6 entities in the collection because an entity collection is always distinct, so you get what you ask in the main collection.

From what you said, I think you actually want to return a ProjectServiceElement entity collection, prefetching the related entities (project and element). Try something like:

// path
var pp = new PrefetchPath2(PTRSDL.EntityType.ProjectServiceElementEntity);
pp.Add(ProjectServiceElementEntity.PrefetchPathProject);
pp.Add(ProjectServiceElementEntity.PrefetchPathElement);

// fetch
var collection = new EntityCollection<ProjectServiceElementEntity>();
var daa = clsDatabaseMethods.GetNewAdapter();
daa.FetchEntityCollection(collection, null, 0, null, pp);
return collection;

Hi Daelmo,

Thank you

I will try that.

Regards

Hi Daelmo,

SELECT Project.ProjectTitle, Element.Name, ProjectWorkplan.Description, ProjectGroup.Name AS ProjectGroupName FROM Project INNER JOIN ProjectServiceElement ON Project.ProjectId = ProjectServiceElement.ProjectId INNER JOIN Element ON ProjectServiceElement.ElementId = Element.ElementId INNER JOIN ProjectSubGroup ON Project.ProjectSubGroupId = ProjectSubGroup.ProjectSubGroupId INNER JOIN ProjectGroup ON ProjectSubGroup.ProjectGroupId = ProjectGroup.ProjectGroupId INNER JOIN ProjectWorkplan ON Project.ProjectId = ProjectWorkplan.ProjectId

private EntityCollection<ProjectServiceElementEntity> GetProjectData()
    {
        EntityCollection<ProjectServiceElementEntity> collection = new EntityCollection<ProjectServiceElementEntity>();


        /DataAccessAdapter daa = clsDatabaseMethods.GetNewAdapter();
        IPrefetchPath2 pp = new PrefetchPath2(PTRSDL.EntityType.ProjectServiceElementEntity);
        pp.Add(ProjectServiceElementEntity.PrefetchPathProject).SubPath.Add(ProjectEntity.PrefetchPathPersonResponsible);
        pp.Add(ProjectServiceElementEntity.PrefetchPathElement);

        pp.Add(ProjectServiceElementEntity.PrefetchPathProject)
        .SubPath.Add(ProjectEntity.PrefetchPathProjectSubGroup)
                      .SubPath.Add(ProjectSubGroupEntity.PrefetchPathProjectGroup);

        RelationPredicateBucket bucketbucket = new RelationPredicateBucket();
        bucketbucket.Relations.Add(ProjectServiceElementEntity.Relations.ProjectEntityUsingProjectId);
        bucketbucket.Relations.Add(ProjectEntity.Relations.PersonResponsibleEntityUsingPersonResponsibleId);
        bucketbucket.Relations.Add(ProjectEntity.Relations.ProjectSubGroupEntityUsingProjectSubGroupId);
        bucketbucket.Relations.Add(ProjectSubGroupEntity.Relations.ProjectGroupEntityUsingProjectGroupId);
        bucketbucket.Relations.Add(ProjectServiceElementEntity.Relations.ElementEntityUsingElementId);
        bucketbucket.Relations.Add(ProjectEntity.Relations.ProjectWorkplanEntityUsingProjectId);
        
    

    

        SortExpression sort = new SortExpression();
    
        daa.FetchEntityCollection(collection, bucketbucket, 0, sort, pp);

        return collection;
    }

It will throw an error in

pp.Add(ProjectServiceElementEntity.PrefetchPathProject)
       .SubPath.Add(ProjectEntity.PrefetchPathProjectSubGroup)
             .SubPath.Add(ProjectSubGroupEntity.PrefetchPathProjectGroup);

it said it has been using in:

"The PrefetchPathElement you to tried to add is already added to this PrefetchPath. Parameter name: elementToAdd"

True.

pp.Add(ProjectServiceElementEntity.PrefetchPathProject).SubPath.Add(ProjectEntity.PrefetchPathPersonResponsible);
        pp.Add(ProjectServiceElementEntity.PrefetchPathElement);

        pp.Add(ProjectServiceElementEntity.PrefetchPathProject)

The following prefetch was aded twice: ProjectServiceElementEntity.PrefetchPathProject

So how i can add the project group from projectserviceElement

the route is

projectservice element will need to use project and project will use projectsubgroup subpath project group.

Pls advise

Here is an aprox code:

IPrefetchPathElement2 projectNode = ProjectServiceElementEntity.PrefetchPathProject;
projectNode.SubPath.Add(ProjectEntity.PrefetchPathPersonResponsible);
projectNode.SubPath.Add(ProjectEntity.PrefetchPathProjectSubGroup)
     .SubPath.Add(ProjectSubGroupEntity.PrefetchPathProjectGroup);

pp.Add(projectNode);

More info: http://www.llblgening.com/archive/2009/10/prefetchpaths-in-depth/#casec

Thanks daelmo.