OData “contains” vs Dynamics 365 Web API “contains”

3
votes

When querying Dynamics 365 via the Web API there are several operators to choose from to filter the queried data. One of those operators is contains which actually appears twice.

One is the OData contains function (you'll find it under the heading 'Standard query functions'):

https://msdn.microsoft.com/en-us/library/gg334767.aspx#Filter%20results

Example:

$filter=contains(name,'(sample)')

The other one is an implementation of the Dynamics 365 Web API itself:

https://msdn.microsoft.com/en-us/library/mt608053.aspx

I tried with this one, but only got a Generic SQL error:

$filter=Microsoft.Dynamics.CRM.Contains(PropertyName='name',PropertyValue='(sample)')

What's the difference? And maybe someone can even tell me how to call the Web API version of contains correctly?

2
dynamics-crmodatadynamics-365dynamics-crm-365dynamics-crm-webapi

2 Answers

2
votes

I think both methods are equivalent. The Web API filter function is added to support a standard OData filter function. The Contains function is a result of exposing all condition operators supported within CRM as OData functions. Please search ConditionOperator enumerations in D365 documentation for information about the Contains operator. It mentions the reason behind SQL error. Copying the same below:

You must use the Contains operator for only those attributes that are enabled for full-text indexing. Otherwise, you will receive a generic SQL error message while retrieving data. In a Microsoft Dynamics 365 default installation, only the attributes of the KBArticle (article) entity are enabled for full-text indexing.

It is preferable to use Web API filter function since it's more idiomatic. What error do you encounter using it? I suppose the root cause for both might be same since the OData filter query gets converted to D365 query expression in the back end and contains filter should be getting translated to the Contains condition operators.

5
votes

Latest documentation confirms $filter=contains(name,'(sample)') is the only working syntax with web api. As Jatin says, OData filter is converted into Query Expression behind the scenes, some articles (this & this) conveys possible solution is using Like operator directly in C# Query Expression.

We don't have Like operator equivalent functions in web api.

My attempts & observations:

Microsoft.Dynamics.CRM.Contains - Generic SQL error.

$filter=like(name,'%test%') - An unknown function with name 'like' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.

$filter=contains(name, 'test') - working