Re: C# Driver OData filter issue ""Where with predicate after a projection is not supported"

[Олег Космаков]

I suggest adding ToArray() or ToList() invocation between GetAll() and Select() method calls. Otherwise it tries to transform your Select statement to mongodb query, and this is not supported, especially if uncommon methods are used inside.

четверг, 10 января 2013 г., 10:06:46 UTC+2 пользователь Vishal написал:

Request: /api/person?$filter=Name eq 'John'

Server method: return repo.GetAll().Select(o => Mapper.Map<PersonDTO>(o));

Above request, results in an error like "Where with predicate after a projection is not supported".  Other OData queries for #top / $skip / $orderby work fine.

The links here and here show the code and the tests. 

However I think in this case the query generated by Mongo C# driver is incorrect. That is it should apply the filter first and then project and not the other way around. 

Any leads in resolving this is much appreciated. I am using C# driver 1.7 & OData 5.2.0-rc1.

Thanks ...

[craiggwilson]

Not to contradict Asya, but I definitely wouldn't add ToArray or ToList() there.  It would fix your current problem.  However, the effect would be that it would pull back the entire collection, do the projection, and then filter locally on your box.  It would be a performance killer.

I've answered your question at StackOverflow as you posted it in two places.  http://stackoverflow.com/questions/14253699/mongo-c-sharp-driver-odata-issue-where-with-predicate-after-a-project-is-not-su

Response: 

That is a limitation in the current implementation of Linq. We are working to correct that with this ticket:https://jira.mongodb.org/browse/CSHARP-601.

However, I'd encourage you to figure out what you are actually attempting to do. Projecting prior to a filter could mean you are filtering on computed expressions, such as adding 2 columns together. MongoDB queries do not support this type of behavior, which is why this is currently disallowed by our linq provider. Aggregation framework allows this to a point, but there are a different set of limitations imposed by the Aggregation framework.

[Vishal]

Thanks Craig. I posted in both places just to get a higher probability of reply. 

I am not doing anything special processing to handle expressions etc. It's just WebAPI OData over Mongo Linq provider. 

The method on server side is something similar to

[Queryable]

public IQueryable<PersonDTO> GetAll()

{

    repo.GetAll().Select(o => Mapper.Map<PersonDTO>(o));

}
I guess WebAPI / OData generates the required query which Mongo Linq provider doesn't support. 

I see from the ticket that its priority is Major but is still TBD. Is there a work around till then?

Thanks ...

[craiggwilson]

We monitor both sites so...

It's a Major feature, not a Major bug.  Hence, we are working on it, but as it's truly a major feature, we want to make sure we get it right.  

The issue with your code is that you are projecting to the PersonDTO.  While I agree this is correct architecturally, what you'll find is that if you don't do the Select there, it works just fine.  What you are asking us to do in your code is know how you have mapped from Person to a PersonDTO in AutoMapper.  You didn't map a PersonDTO in MongoDB, you mapped a Person.  Your linq query is asking us to query based on PersonDTO.

So, either remove projection or do what Asya suggested, put a ToList or ToArray in there (which completely defeats the point).

[Vishal]

I didn't want to give up the AutoMapper goodness & didn't want to grab all the records using ToList(), so here is what I came up with (comments / unrelated code taken out)

[HttpGet]

[Queryable] 

public HttpResponseMessage Get(ODataQueryOptions<Person> options)

{

var list = new List<PersonDTO>();

var dataEnumerator = options.ApplyTo(repo.GetAll()).GetEnumerator(); //Mongo shouldn't have any issues now

while (dataEnumerator.MoveNext())

{

list.Add(Mapper.Map<PersonDTO>(dataEnumerator.Current));

}

var result = Request.CreateResponse<IQueryable<PersonDTO>>(HttpStatusCode.OK, list.AsQueryable<PersonDTO>());

return result;           

}

Now $filter works fine and so do $top / $orderby. But there seems to be an issue with $skip. It's getting applied twice! So if I give $top=3&$skip=1 on lets say a dataset of (p1, p2, p3, p4,p5) the result is (p3, p4) instead of (p2, p3, p4). I am not sure where the problem lies WebAPI or Mongo.  Do let me know if you have come across this kind of behavior. I am also writing additional unit tests that involve filtering on int / DateTime & will share the results on this thread.  

[Vishal]

I took out the [Queryable] attribute on top & that seems to do the trick. Only down side is I can not use ResultLimit etc which can be set thru the attribute.

$filter doesn't seem to work with int values (for operators eq gt lt etc). It errors out with "The binary operator Equal is not defined for the types 'System.Int32' and 'Syste.Nullable[System.Int32]'. This I am pretty sure is an issue with Mongo driver. Need to look into that. Again any inputs would help...

[craiggwilson]

Your issue with Nullable<int> and int has been resolved in current master and will be fixed with the next release of the driver.

We have not come across the skip behavior.  I'll see if I can reproduce.