SQL Statement to Query Expression

Question

I want to convert SQL statement to Query Expression.

   SELECT q.EMailAddress, fe.torecipients, fe.subject, fe.createdon, fe.actualend ,fe.ActivityId
    FROM QueueItem qi WITH (NOLOCK)
    inner join Email fe WITH (NOLOCK) ON fe.ActivityId = qi.ObjectId and fe.RegardingObjectId is null
    inner join Queue q WITH (NOLOCK) ON q.QueueId= qi.QueueId and q.EMailAddress is not null
    WHERE PATINDEX(CONCAT('%', q.emailaddress, '%'), fe.torecipients ) >= 1 
    ORDER BY fe.createdon DESC

I could do this. How can I convert 'PATINDEX(CONCAT('%', q.emailaddress, '%'), fe.torecipients ) >= 1'. Can you help me?

QueryExpression queueQuery = new QueryExpression(Constants.QueryQueueItem);
queueQuery.ColumnSet.AddColumns(Constants.QueryIncidentId);

LinkEntity emailLink = new LinkEntity();
emailLink.LinkFromEntityName = Constants.QueryQueueItem;
emailLink.LinkToAttributeName = Constants.QueryEmail;
emailLink.LinkFromAttributeName = "ObjectId";
emailLink.LinkToAttributeName = "ActivityId";
emailLink.JoinOperator = JoinOperator.Inner;
emailLink.LinkCriteria.AddCondition(new ConditionExpression("RegardingObjectId", ConditionOperator.Null, true))

LinkEntity queueLink = new LinkEntity();
queueLink.LinkFromEntityName = "QueueItem";
queueLink.LinkToAttributeName = "Queue";
queueLink.LinkFromAttributeName = "QueueId";
queueLink.LinkToAttributeName = "QueueId";
queueLink.JoinOperator = JoinOperator.Inner;
queueLink.LinkCriteria.AddCondition(new ConditionExpression("EMailAddress", ConditionOperator.NotNull, true));

queueQuery.LinkEntities.Add(emailLink);
queueQuery.LinkEntities.Add(queueLink);

ConditionExpression emailCondition = new ConditionExpression();
emailCondition.AttributeName = Constants.QueryIncidentId;
emailCondition.Operator = ConditionOperator.NotNull;

FilterExpression emailFilter = new FilterExpression();
emailFilter.Conditions.Add(emailCondition);
queueQuery.Criteria = emailFilter;

I've never worked with CRM, but perhaps you can use something like `SqlFunctions.PatIndex` as shown in [this answer](https://stackoverflow.com/questions/22455782/linq-sqlfunctions-patindex-vs-string-contains-for-string-comparisson). — Alberto Solano, Nov 15 '17 at 08:29
Do you want to retrieve the QueueItem where your Queue email address is part of Email To list? — Arun Vinoth-Precog Tech - MVP, Nov 16 '17 at 03:31

score 0 · Answer 1 · answered Nov 16 '17 at 13:39

FetchXML does not support the PATINDEX function, so quick option would be to use the Like operator instead:

new ConditionExpression(“torecipients”, ConditionOperator.Like, "%"+q.emailaddress+"%");

A more complex option that would allow to you match the email address would be to join to the email's Activity Parties and filter based on matching the activity party type and AddressUsed.

SQL Statement to Query Expression

1 Answers1