Linq take returns zero elements from OData collection

Question

I can't wrap my brain around this, I don't get why this behaves likes this. I have made an OData query that returns a collection of 175 items

from c in navClient.Item where c.No != "" && c.Last_Date_Modified > dtfrom select c

navClient.Item is a System.Data.Services.Client.DataServiceQuery

However I want to take first 100 items from the collection using .Take(100) but get 0 items. It isn't until I do .Take(121) I get my first item, which is the first item in the collection, .Take(122) returns the first two items and so on.

Any idea why this behaves like this?

Edit: Doing ToList first then Take(100) returns first 100 as expected. My only theory right know is that the table I'm running my query against is just a temp table that is out of sync with the database.

What if you do a ToList first then do a take 100? Just to see... — Tarik, Mar 04 '21 at 17:59
If I convert to list first then do take(100) that I get 100 items no problem — Tralli, Mar 04 '21 at 18:10
Looks like a bug in DataServiceQuery or in your WCF Data Service — Svyatoslav Danyliv, Mar 04 '21 at 18:53
Is this your API? can you show us the controller implementation? — Chris Schaller, Mar 04 '21 at 22:18
How exactly is your view going to be out of sync with the database? — Chris Schaller, Mar 04 '21 at 22:31
You should also include more of your client code, this expression you have shown does not include the `Take` criteria, so it is plausible that the client is not sending the correct statement to the server at all. — Chris Schaller, Mar 04 '21 at 22:49
A "View" is perhaps not the correct term, what I meant is that it might be a temporary table that contains prefetched data or something. — Tralli, Mar 05 '21 at 09:50

score 0 · Answer 1 · answered Mar 04 '21 at 23:24

You have described what looks like an issue in the server implementation that handles your request. This behaviour would occur if the .Take(100) was evaluated before the filter criteria. This issues can easily occur in the client or the server implementations. The .ToList() works because it brings back the entire collection to the client and then applies the filter criteria with standard linq to objects evaluation.

While not the cause today...
as a general rule, whenever you specify .Take() or .Skip() you should have also specified the explicit .OrderBy(), when the order is ambiguous in a limiting or paging query so too can be the results.

There are 3 common levels at play here:

Client Query Resolution
Your linq query is first resolved into a URL that will be used to make the request to the API, you should first check that the URL is correctly constructed.

You should be expecting a URL similar to:
```
/odata/Item?$top=100&$filter=No ne '' AND Last_Date_Modified gt '2020-01-20T17:24:21.3605918+11:00'
```
You can inspect the URL using the .RequestUri property on the query, try something like the following to capture it:
```
var query = from c in navClient.Item 
            where c.No != "" && c.Last_Date_Modified > dtfrom 
            select c;
query = query.Take(100);       
System.Diagnostics.Debug.WriteLine(((DataServiceQuery<Item>)query).RequestUri);
```
The URL needs to through both the $Top and the $filter query options to the server.
API Controller
The client is expecting the controller to handle or pass through both the $filter criteria and the $top expression to the underlying data store.

Many default EF or Unit of work based OData implementations would simply return a proper IQueryable<T> expression using deferred execution. However if the backend store uses a repository pattern or the controller is otherwise constructing the dataset then that code may need to explicitly handle the $filter criteria before applying the $top

There are many simple OData controller examples out there that have a mocked List<T> or otherwise IEnumerable<T> backend, the red flag is that if in the controller code you see a .ToList() or a .AsQueryable() on the main expression, or it returns an IEnumerable<T> response, then it indicates that the expression is not using deferred execution at all. This means that it most likely needs to manage the query options manually.
OData Query Expression Resolver
Especially in the .Net Implementation, the EnableQueryAttribute by default applies the query options to the final IQueryable output immediately before or effectively as part of the serialization process.

So the $top and $filter (and $orderby,$select,$expand) will be applied again even if the controller method has already evaluated these options. This isn't normally a problem, its a validation fail-safe and means that you do not strictly have to return IQueryable<T> from your controller methods at all.

Due to this, if our backend supports deferred IQueryable<T> linq expressions (like DbSet<T> in an EF DbContext) then in the controller implementation we do not usually do anything with the query options, you let the EnableQueryAttribute process it for you.

However, if the controller were to process the paging expression first .Take(100) and not apply the $filter correctly or at all, then the EnableQueryAttribute would apply the criteria to a list that had already been restricted, in your example, perhaps the first 100 items

Linq take returns zero elements from OData collection

1 Answers1