Hi,

I'd like to build a query that left outer joins to a group of joined tables.

The LLBLGen equivalent of the follwing SQL Query (note the parantheses used to group the join after the left outer join clause):

select cp.ID              as CloudProviderID
     ....
from CloudProvider                               cp
    inner join dbo.CloudProviderAccount          cpa
        on cpa.CloudProviderID = cp.ID
    inner join CloudProviderAccountLicensingPlan as cpalp
        on cpalp.CloudProviderAccountID = cpa.ID
    left outer join ( dbo.CloudProviderAccountLicensingPlanCloudProviderService as cpalpcps
    inner join dbo.CloudProviderService                                       as cps
        on cps.ID = cpalpcps.CloudProvierServiceID )
        on cpalpcps.CloudProviderAccountLicensingPlanID = cpalp.ID;    

I could not find an API where I can pass in a RelationCollection with a join hint into an existing relation collection. What am I missing?

You have to use a projection, so create an entity query with the inner join and then join with that. However as I don't know what query system you want to use, it's best if you post what you have that fails, so we can extend/correct it.

something like: (QuerySpec)

var qf = new QueryFactory();
var innerJoinQ = qf.CloudProviderAccountLicensingPlanCloudProviderService
                    .From(QueryTarget.InnerJoin(qf.CloudProviderService)
                            .On(CloudProviderAccountLicensingPlanCloudProviderServiceFields.CloudProvierServiceID.Equal(CloudProviderServiceFields.CloudProvierServiceID)))
                    .As("cpalpcps");
var q = qf.CloudProvider
            .From(QueryTarget.CloudProviderAccount
                        .InnerJoin ... 
                        .InnerJoin...
                        .LeftJoin(innerJoinQ)
                            .On(CloudProviderAccountLicensingPlanCloudProviderServiceFields
                                    .CloudProviderAccountLicensingPlanID.Source("cpalpcps").Equal(CloudProviderAccountLicensingPlanFields.Id)))
            .Select(...)

You can also use linq for this, it's the same principle.

(edit) After posting I realized you can just change the inner join between CloudProviderAccountLicensingPlanCloudProviderService and CloudProviderService into a left join because if the CloudProviderAccountLicensingPlanCloudProviderService part is NULL due to the left join with CloudProviderAccountLicensingPlan, no row will be joined from CloudProviderService. I think the runtime even does this for you if you pass in an inner join, but have to check.

(edit2) The notification email bounced with an error: support is not authorized to relay messages through the server that reported this error. This means our notification emails are blocked somewhere. In case you're wondering why they don't arrive, that's the reason

Otis wrote:

(edit) After posting I realized you can just change the inner join between CloudProviderAccountLicensingPlanCloudProviderService and CloudProviderService into a left join because if the CloudProviderAccountLicensingPlanCloudProviderService part is NULL due to the left join with CloudProviderAccountLicensingPlan, no row will be joined from CloudProviderService. I think the runtime even does this for you if you pass in an inner join, but have to check.

Hi Otis, thanks for your quick reply. You are right in that the query can be written using two left outer joins. The query used to be all inner joins and needed to be adjusted since some entities are optional now. With inner joins, the order of the joins was not relevant. I'm using the LLBLGEN self-servicing low level API.

.....
from CloudProvider                                                            cp
    inner join dbo.CloudProviderAccount                                       cpa
        on cpa.CloudProviderID = cp.ID
    inner join CloudProviderAccountLicensingPlan                              as cpalp
        on cpalp.CloudProviderAccountID = cpa.ID
    left outer join dbo.CloudProviderAccountLicensingPlanCloudProviderService as cpalpcps
        on cpalpcps.CloudProviderAccountLicensingPlanID = cpalp.ID
    left outer join dbo.CloudProviderService                                  as cps
        on cps.ID = cpalpcps.CloudProvierServiceID;

Once I realized with your help that I can just use left outer joins (the order of the joins already changed quite a bit from the original code), it's straightforward to write. The code affected is quite old and the real query uses even more relations ... I'm using LLBLGEN ORM Predicate system self-servicing with dynamic lists...

Thanks for your help and kind regards Erwin