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

Have you tried an ICollection<CommentDTO> instead of IQueryable<CommandDTO> in FortuneDTO? — Nikon the Third
when I try ICollection<CommentDTO>, It gives cast error in the FilterBy method in service layer. I don't know what I should return from here 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() — emreturan
Ok I see. Try returning an IEnumerable<CommentDTO> and instead of .AsQueryable() use .AsEnumerable(). Then both sides should be happy... — Nikon the Third
unfortunately, when I changed to IEnumerable<CommentDTO> and AsQueryable, it gives me that "Cannot compare elements of type 'System.Collections.Generic.IEnumerable1'. Only primitive types, enumeration types and entity types are supported.**" instead of "**Cannot compare elements of type 'System.Linq.IQueryable1'. Only primitive types, enumeration types and entity types are supported." — emreturan
Hm. When I try it filtering works. pastebin.com/bnNQ2FXj - Maybe it has something to do with EF proxies? Have you tried setting context.Configuration.ProxyCreationEnabled = false; on your EF database context? - EDIT: no wait, that can't be a problem, since you convert your model objects to DTO's, doh... — Nikon the Third

RaghuRam Nadiminti RaghuRam Nadiminti · Accepted Answer · 2013-09-23T17:31:12

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.

Advanced filter on Asp.net Wep Api OData

1 Answers