Expression Clauses in a query

There is no sample as far as I know. But if you try to join over a foreign key column you do not need an ExpressionClause.

The following sample loads a collection of business roles where the org type (Org.UID_OrgType.Ident_OrgType) is "Business Roles".

Dim f As ISqlFormatter = Session.SqlFormatter()
Dim strOrgType As String = String.Empty

strOrgType = "Business roles"

Dim qOrgQuery As Query
qOrgQuery = Query.From(Table.Org).Join(Table.OrgType, "ot").On(Table.Org.UID_OrgType).Select(Table.OrgType.Ident_OrgType).Where( _
    f.Comparison(String.Format("ot.{0}", Table.OrgType.Ident_OrgType), strOrgType, ValType.String, CompareOperator.Equal))

Dim mycollection As TypedEntityCollection(Of VI.DB.Model.Org) = Session.Source.GetCollection(Of VI.DB.Model.Org)(qOrgQuery, EntityCollectionLoadType.Bulk)


Return mycollection.Count.ToString

Please be aware that the join is just usable to identify the data to load. A collection always loads entities of one specific type. You cannot join additional columns to it.

Hello Markus,

We tried this solution but we got an error in the exectuion of the query.

Our Query in 1IM:

Dim qApprover = Query.From("PWOHelperPWO","php").Join("PersonWantsOrg","pwo").On(Table.PWOHelperPWO.UID_PersonWantsOrg) _
.Select("*").Where("php.UID_PersonHead = 'e94fd1f7-ecf2-4d5f-9955-f2311a110c39' AND php.LevelNumber = pwo.DecisionLevel")

The error we get:

Error during execution of statement: select php.Decision, php.IsFromDelegation, [...] XUserInserted, XUserUpdated from PersonWantsOrg) as pwo on php.UID_PersonWantsOrg = pwo.UID0 where (php.UID_PersonHead = 'e94fd1f7-ecf2-4d5f-9955-f2311a110c39' AND php.LevelNumber = pwo.DecisionLevel) order by UID_PersonWantsOrg, LevelNumber, UID_PWODecisionRule

Database error 209: Ambiguous column name 'UID_PersonWantsOrg'.
Ambiguous column name 'UID_PersonWantsOrg'.

We think that the error may is produced because of the order by clause that we don't add or because of the .On clause.

As it was a bit difficult to solve, we nested the query in the where clause using UID_PWOHelperPWO IN (SELECT ....).

Thanks!

You should select only the columns needed in your where clause and not by a wildcard (.Select("*") or .SelectAll) as you did. The query object struggles in that case with duplicate (ambiguous) columns as you have run into. Please keep in mind that the Select is used for the Join when placed in the fluent interface at the position as in the sample code.

The correct code would be

Dim qApprover = Query.From(Table.PWOHelperPWO, "php").Join(Table.PersonWantsOrg, "pwo").On(Table.PWOHelperPWO.UID_PersonWantsOrg).
    Select(Table.PersonWantsOrg.DecisionLevel).
    Where("php.UID_PersonHead = 'e94fd1f7-ecf2-4d5f-9955-f2311a110c39' AND php.LevelNumber = pwo.DecisionLevel")

You should select only the columns needed in your where clause and not by a wildcard (.Select("*") or .SelectAll) as you did. The query object struggles in that case with duplicate (ambiguous) columns as you have run into. Please keep in mind that the Select is used for the Join when placed in the fluent interface at the position as in the sample code.

The correct code would be

Dim qApprover = Query.From(Table.PWOHelperPWO, "php").Join(Table.PersonWantsOrg, "pwo").On(Table.PWOHelperPWO.UID_PersonWantsOrg).
    Select(Table.PersonWantsOrg.DecisionLevel).
    Where("php.UID_PersonHead = 'e94fd1f7-ecf2-4d5f-9955-f2311a110c39' AND php.LevelNumber = pwo.DecisionLevel")

Ok, I have been used the select clause wrongly but it is always good to know. 

Another question related with queries. How can I execute correctly a query of the form Query.Update? Is there any function in Session.Source for it?

EDIT: without using:

Using uow As IUnitOfWork = Session.StartUnitOfWork()
uow.Put()
uow.Commit()
End Using

Thanks,

Adri

Sorry. But can you explain in more detail what a Query.Update has to do with a unit of work? You can save an entity by calling the method Save(Session) if that is what you are seeking. But we encourage you to use a unit of work if you deal with more than one object.

I was trying to build a query using:

Query.Update(Table.ADSAccount).Set(Table.ADSAccount.ExtensionAttribute1,"0").Where("UID_ADSAccount = 'UID_PersonHead = ''")

Normally I use queries to get data and I use Session.Source.Getcollection for "execute" them. How could I execute this query?

The query object will also be used internally to generate SQL statements. But I hardly think that there is a use case for Query.Update in the context of working with collections and entities.

Please refer to the script samples in the SDK on the product delivery for more sample about working with collections and updating entities using the object layer.

In short, load the collection of ADSAccount objects with the query containing your where-clause, open a unit of work, iterate over the entities, set the ExtensionAttribute1 and commit the unit of work.