Advanced filter on Asp.net Wep Api OData

Question

I have a asp.net wep api project with odata but I'm having some problems with odata filter mechanism.

when I execute that query

/api/values?$top=1&$filter=Comments/any(c: c/Id eq 64)

it gives me following error

{
"Message": "An error has occurred.",
"ExceptionMessage": "The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; charset=utf-8'.",
"ExceptionType": "System.InvalidOperationException",
"StackTrace": null,
"InnerException": {
    "Message": "An error has occurred.",
    "ExceptionMessage": "Cannot compare elements of type 'System.Linq.IQueryable`1'. Only primitive types, enumeration types and entity types are supported.",
    "ExceptionType": "System.NotSupportedException",
    "StackTrace": "   at System.Data.Objects.ELinq.ExpressionConverter.VerifyTypeSupportedForComparison(Type clrType, TypeUsage edmType, Stack`1 memberPath)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.CreateIsNullExpression(DbExpression operand, Type operandClrType)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.EqualsTranslator.CreateIsNullExpression(ExpressionConverter parent, Expression input)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.EqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.ConditionalTranslator.TypedTranslate(ExpressionConverter parent, ConditionalExpression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.EqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n   at System.Data.Objects.ELinq.ExpressionConverter.Convert()\r\n   at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)\r\n   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)\r\n   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()\r\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   at Newtonsoft.Json.Serialization.JsonArrayContract.CreateWrapper(Object list)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value)\r\n   at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value)\r\n   at Newtonsoft.Json.JsonSerializer.Serialize(JsonWriter jsonWriter, Object value)\r\n   at System.Net.Http.Formatting.JsonMediaTypeFormatter.<>c__DisplayClassd.<WriteToStreamAsync>b__c()\r\n   at System.Threading.Tasks.TaskHelpers.RunSynchronously(Action action, CancellationToken token)"
}

}

But when I execute following controller instead of odata filter, everything is fine,

    [Queryable]
    public IQueryable<FortuneDTO> Get()
    {
        return service.FilterBy().Where(_ => _.Comments.Any(c => c.Id == 64));
    }

I'm using Repository + Service layer pattern in my project and structure of my project is like that

api controller <-> service <-> repository <-> EF

odata api controller

    [Queryable]
    public IQueryable<FortuneDTO> Get()
    {
        return service.FiterBy();
    }

service

    public IQueryable<FortuneDTO> FiterBy()
    {
        return repository.List().Select(_ => new FortuneDTO
        {
            CreatedByFullName = _.aspnet_Users.FullName,
            Id = _.FortuneId,
            Comments = _.tblComment.Select(c => new CommentDTO
            {
                Id=c.CommentId,
                Comment = c.Comment,
                Fortuneteller = new FortunetellerDTO { 
                    FullName=c.aspnet_Users.FullName,
                    Id=c.aspnet_Users.UserId
                }
            }).AsQueryable()
        });
    }

repository

    public virtual IQueryable<TEntity> List()
    {
        return context.CreateObjectSet<TEntity>();
    }

DTO's

public class FortuneDTO
{
    public int Id { get; set; }
    public string CreatedByFullName { get; set; }
    public IQueryable<CommentDTO> Comments { get; set; }
}
public class CommentDTO
{
    public int Id { get; set; }
    public string Comment { get; set; }
    public FortunetellerDTO Fortuneteller { get; set; }
}
public class FortunetellerDTO
{
    public Guid Id { get; set; }
    public string FullName { get; set; }
}

I couldn't find any way to avoid this error

Edit:

Generated and executed expression is

 Convert(value(System.Data.Objects.ObjectSet`1[Axiom.Entities.tblFortune])).MergeAs(AppendOnly).Where(_ => True).Select(_ => new FortuneDTO() {CreatedByFullName = _.aspnet_Users.FullName, Id = _.FortuneId, Comments = _.tblComment.Select(c => new CommentDTO() {Id = c.CommentId, Comment = c.Comment, Fortuneteller = new FortunetellerDTO() {FullName = c.aspnet_Users.FullName, Id = c.aspnet_Users.UserId}}).AsQueryable()}).Where($it => (IIF(($it.Comments == null), null, Convert($it.Comments.Any(c => ($it.Id == value(System.Web.Http.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty)))) == True))

Entity Framework 4 ASP.NET Web API 4.0.30506.0

Answer 1

I think web API failed to detect that you are using entityframework (possible because you are using entityframework 4.0) and is turning on NullPropagation. You can explicitly turn it off by doing this,

[Queryable(HandleNullPropagation=false)]

and see if it works.