Linq2Entities Include with Take - load issue

Question

Note: I know there are a number of questions around for issues with Linq's .Include(table) not loading data, I believe I have exhausted the options people have listed, and still had problems.

I have a large Linq2Entities query on an application I'm maintaining. The query is built up as such:

IQueryable<Results> query = context.MyTable
    .Where(r =>
    r.RelatedTable.ID == 2 &&
    r.AnotherRelatedTable.ID == someId);

Then predicates are built up depending on various business logic, such as:

if (sortColumn.Contains("dob "))
{
    if (orderByAscending)
        query = query.OrderBy(p => p.RelatedTable.OrderByDescending(q => q.ID).FirstOrDefault().FieldName);
    else
        query = query.OrderByDescending(p => p.RelatedTable.OrderByDescending(q => q.ID).FirstOrDefault().FieldName);
}

Note - there is always a sort order provided.

Originally the included tables were set at the beginning, after reading articles such as the famous Tip 22, so now they are done at the end (which didn't fix the problem):

var resultsList = (query.Select(r => r) as ObjectQuery<Results>)
    .Include("RelatedTable")
    .Include("AnotherRelatedTable")
    .Skip((page - 1) * rowsPerPage)
    .Take(rowsPerPage);

Seemingly at random (approximately for every 5000 users of the site, this issue happens once) the RelatedTable data won't load. It can be brute forced by calling load on the table. But even the failure to load isn't consistent, I've run the query in testing and it's worked, but most of the time hasn't, without changing any code or data.

It is fine, when the skip and take aren't included, and the whole dataset is returned, but I would expect the skip and take to be done on the complete dataset - it certainly appears to be from profiling the SQL...

Update: Just a minor one. As I'm lucky enough that it's never a huge dataset I'm now enumerating the query, and doing my skip / take on the resulting list, which works fine. I'm looking to capture the good and bad generated SQL in due course and debug my data...

Answer 1

The sole effect of Take() is to change the generated SQL. Other than that, the Entity Framework does not care about it at all. Same for .Skip(). It's hard to believe that this would have an effect on query materialization (although stranger things have happened).

So what could be causing this behavior? Off the top of my head:

1. A bug in your application or mapping which is causing an incorrect query to be generated.
2. A bug in the Entity Framework which would cause returned data to be materialized into objects incorrectly in certain circumstances.
3. Bad data in your database.
4. A bug in your database's SQL parser.

I don't think you're going to get a lot further with this until you can capture the generated SQL and run it yourself. This is actually not terribly hard, as you can set up a SQL profiler with an appropriate filter. If you find that the generated SQL is different in the buggy case, you can work backwards from there. If you find that the generated SQL is identical in the buggy case, the next step would be to look at the rows returned, preferably in the same context as the application ran it.

In short, I think you just have to keep tweaking your SQL profiling until you have the information you need.