Query Expression with multiple joins ?

(0) Share

Report

Posted on by Community Member

Hi Experts,

I have tried to convert my SQL query to Query Expression but its giving me error while executing. Can you please suggest me what will be the equivalent Query Expression for the following sql query .

select su.systemuserid,su.FullName from systemuser as su
left join licence lic on su.systemuserid = lic.agentid
left join profile pr on pr.profileId = lic.profile
where pr.mode= 1

following query Query I wrote works fine but when I add one more linked entity to it its giving me error. :

 var expression = new FilterExpression();
               expression.AddCondition(new ConditionExpression("isdisabled",
                ConditionOperator.NotEqual, true));
                QueryExpression query = new QueryExpression()
                {
                    EntityName = "systemuser",
                    ColumnSet = new ColumnSet("systemuserid", "fullname"),
                    Criteria = expression,
                };

            
                var linkedLicenses = new LinkEntity
                {
                    LinkFromEntityName = "systemuser",
                    LinkToEntityName = "licence",
                    LinkFromAttributeName = "systemuserid",
                    LinkToAttributeName = "agentid",
                    JoinOperator = Microsoft.Xrm.Sdk.Query.JoinOperator.Inner
                };  

               var linkedProfile = new LinkEntity
                {
                    LinkFromEntityName = "profile",
                    LinkToEntityName = "licence",
                    LinkFromAttributeName = "profileid",
                    LinkToAttributeName = "profile",
                    JoinOperator = Microsoft.Xrm.Sdk.Query.JoinOperator.Inner
                };
               
                linkedProfile.LinkCriteria.AddCondition(new ConditionExpression("mode",ConditionOperator.Equal,true));
                
                linkedLicenses.LinkEntities.Add(linkedProfile);

                query.LinkEntities.Add(linkedLicenses);


Error : 'license' entity doesn't contain attribute with Name = 'profileid'.

Please educate me how combine link Entity in case of multiple inner joins.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Gopalan Bhuvanesh 11,401 on at

Like (0)

Report
Hi

Change the order of the second linkentity and try.

var linkedProfile = new LinkEntity { LinkFromEntityName = "licence", LinkToEntityName = "profile", LinkFromAttributeName = "profile", LinkToAttributeName = "profileid", JoinOperator = Microsoft.Xrm.Sdk.Query.JoinOperator.Inner };

Was this reply helpful? Yes No
Thomas David Dayman 11,323 on at

Like (0)

Report
Hi,

Please feel free to reference my working code below. Hopefully it helps

QueryExpression query = new QueryExpression(); query.EntityName = "invoicedetail"; query.ColumnSet.Columns.Add("f1_agreementinvoiceproduct"); query.LinkEntities.Add(new LinkEntity("invoicedetail", "invoice", "invoiceid", "invoiceid", JoinOperator.Inner)); //query.LinkEntities[0].Columns.AddColumn("duedate"); query.LinkEntities[0].EntityAlias = "invoice"; query.LinkEntities[0].LinkCriteria = new FilterExpression(); query.LinkEntities[0].LinkCriteria.FilterOperator = LogicalOperator.And; query.LinkEntities[0].LinkCriteria.Conditions.Add ( new ConditionExpression("invoiceid", ConditionOperator.Equal, InvoiceId) );

Was this reply helpful? Yes No
Suggested answer

Nithya Gopinath 17,078 on at

Like (0)

Report
Hi,

Try the code below.

QueryExpression query = new QueryExpression("systemuser"); query.LinkEntities.Add(new LinkEntity("systemuser", "license", "systemuserid", "agentid", JoinOperator.Inner)); query.LinkEntities[0].AddLink("profile", "profile", "profileId", JoinOperator.Inner); FilterExpression expression = new FilterExpression(); expression.Conditions.Add(new ConditionExpression("mode", ConditionOperator.Equal, 1); query.LinkEntities[0].LinkCriteria = expression;

Hope this helps.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Gopalan Bhuvanesh (GeeBee) for quick response, I have tried as you suggested but giving me following error message :

'profile' entity doesn't contain attribute with Name = 'mode'.

Although profile entity contains 'mode' attribute.

Was this reply helpful? Yes No
Suggested answer

Gopalan Bhuvanesh 11,401 on at

Like (0)

Report

Hi

You can create a query just for this entity and find out whether you are able to get this attribute (mode).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

[deleted]

Was this reply helpful? Yes No