How do I correctly filter my DataSet by GUID using OData?

Question

How do I correctly filter my DataSet by GUID?

I'm exposing an OData endpoint, and trying to navigate to the URL:

http://localhost:5001/mystuf/api/v2/AccountSet?$filter=AccountId%20eq%20guid%2703a0a47b-e3a2-e311-9402-00155d104c22%27

When my OData endpoint tries to filter the DataSet on that GUID, I am getting:

"message": "Invalid 'where' condition. An entity member is invoking
an invalid property or method.", "type": "System.NotSupportedException"
> 
{
  "odata.error": {
    "code": "",
    "message": {
      "lang": "en-US",
      "value": "An error has occurred."
    },
    "innererror": {
      "message": "Invalid 'where' condition. An entity member is invoking an invalid property or method.",
      "type": "System.NotSupportedException",
      "stacktrace": "   at Microsoft.Xrm.Sdk.Linq.QueryProvider.ThrowException(Exception exception)
   at Microsoft.Xrm.Sdk.Linq.QueryProvider.FindValidEntityExpression(Expression exp, String operation)
   at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhereCondition(BinaryExpression be, FilterExpressionWrapper parentFilter, Func`2 getFilter, Boolean negate)
   at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhere(String parameterName, BinaryExpression be, FilterExpressionWrapper parentFilter, Func`2 getFilter, List`1 linkLookups, Boolean negate)
   at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhereBoolean(String parameterName, Expression exp, FilterExpressionWrapper parentFilter, Func`2 getFilter, List`1 linkLookups, BinaryExpression parent, Boolean negate)
   at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhere(QueryExpression qe, String parameterName, Expression exp, List`1 linkLookups)
   at Microsoft.Xrm.Sdk.Linq.QueryProvider.GetQueryExpression(Expression expression, Boolean& throwIfSequenceIsEmpty, Boolean& throwIfSequenceNotSingle, Projection& projection, NavigationSource& source, List`1& linkLookups)
   at Microsoft.Xrm.Sdk.Linq.QueryProvider.Execute[TElement](Expression expression)
   at Microsoft.Xrm.Sdk.Linq.QueryProvider.GetEnumerator[TElement](Expression expression)
   at Microsoft.Xrm.Sdk.Linq.Query`1.GetEnumerator()
   at Microsoft.Xrm.Sdk.Linq.Query`1.System.Collections.IEnumerable.GetEnumerator()
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)
   at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)
   at Newtonsoft.Json.JsonSerializer.Serialize(JsonWriter jsonWriter, Object value)
   at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)
   at System.Net.Http.Formatting.JsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)
   at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content)
   at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)
   --- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at System.Web.Http.Tracing.ITraceWriterExtensions.<TraceBeginEndAsyncCore>d__24.MoveNext()
   --- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at System.Web.Http.Owin.HttpMessageHandlerAdapter.<BufferResponseContentAsync>d__13.MoveNext()"
    }
  }
}

The start of the CSDL file looks something like this:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
    <Schema Namespace="Xrm" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://schemas.microsoft.com/ado/2007/05/edm">

      <EntityType Name="Account">
        <Key>
          <PropertyRef Name="AccountId" />
        </Key>
        <Property Name="TerritoryCode" Type="Microsoft.Crm.Sdk.Data.Services.OptionSetValue" Nullable="false" />
        <Property Name="LastUsedInCampaign" Type="Edm.DateTime" Nullable="true" />
…

The controller that is getting triggered is:

public IHttpActionResult Get(ODataQueryOptions<Account> options)
{
    var retval = options.ApplyTo(_accountService.GetAccountSet());

    return Ok(retval);

}

And the above Get() method applies the filter to:

public IQueryable<Account> GetAccountSet()
{
    return _xrmServiceContext.AccountSet;
}

How do I correctly filter my DataSet by GUID?

Please note that when I return this instead:

return _xrmServiceContext.AccountSet.Take(2);

Then data is returned correctly to the client (serialization/deserialization correctly works); however, the filter is not applied at all.

Answer 1

According to updated documentation solution depends on type of AccountId field.

If AccountId type is Guid (most probably your case) than comparison with guid literal should be written without guid keyword or putting it into quotes. So valid query should look like this:

AccountId eq 03a0a47b-e3a2-e311-9402-00155d104c22

And result url will be this:

http://localhost:5001/mystuf/api/v2/AccountSet?$filter=AccountId%20eq%2003a0a47b-e3a2-e311-9402-00155d104c22

But if AccountId type is String and it contains string representation of guid, you should use rules for string literal and put it in single quotes, like this:

AccountId eq '03a0a47b-e3a2-e311-9402-00155d104c22'

And result url will be this:

http://localhost:5001/mystuf/api/v2/AccountSet?$filter=AccountId%20eq%20%2703a0a47b-e3a2-e311-9402-00155d104c22%27

You can just test both url to see which one will work for you :)