LINQ to Entities: Query not working with certain parameter value

Question

I have a very strange problem with a LINQ to Entities query with EF1. I have a method with a simple query:

public DateTime GetLastSuccessfulRun(string job)
{
    var entities = GetEntities();
    var query = from jr in entities.JOBRUNS
                where jr.JOB_NAME == job && jr.JOB_INFO == "SUCCESS"
                orderby jr.JOB_END descending
                select jr.JOB_END;

    var result = query.ToList().FirstOrDefault();
    return result.HasValue ? result.Value : default(DateTime);
}

The method GetEntities returns an instance of a class that is derived from System.Data.Objects.ObjectContext and has automatically been created by the EF designer when I imported the schema of the database.
The query worked just fine for the last 15 or 16 months. And it still runs fine on our test system. In the live system however, there is a strange problem: Depending on the value of the parameter job, it returns the correct results or an empty result set, although there is data it should return.
Anyone ever had a strange case like that? Any ideas what could be the problem?

Some more info:
The database we query against is a Oracle 10g, we are using an enhanced version of the OracleEFProvider v0.2a.
The SQl statement that is returned by ToTraceString works just fine when executed directly via SQL Developer, even with the same parameter that is causing the problem in the LINQ query.
The following also returns the correct result:

entities.JOBRUNS.ToList().Where(x => x.JOB_NAME == job && x.JOB_INFO == "SUCCESS").Count();

The difference here is the call to ToList on the table before applying the where clause. This means two things:

The data is in the database and it is correct.
The problem seems to be the query including the where clause when executed by the EF Provider.

What really stuns me is, that this is a live system and the problem occurred without any changes to the database or the program. One call to that method returned the correct result and the next call five minutes later returned the wrong result. And since then, it only returns the wrong results.

Any hints, suggestions, ideas etc. are welcome, never mind, how far-fetched they seem! Please post them as answers, so I can vote on them, just for the fact for reading my lengthy question and bothering thinking about that strange problem... ;-)

This is most probably not related to your problem but you can run: `var result = query.FirstOrDefault();` It will return single item from DB where your query returns all available results and then in memory it selects first. — Ladislav Mrnka, Feb 28 '11 at 13:50
@Ladislav Mrnka: Indeed, the `ToList` inbetween is not necessary, but you are right: That is not the problem :( — Daniel Hilgarth, Feb 28 '11 at 13:52
@Ladislav Mrnka: Most of the time, I reuse them. I implemented a caching mechanism that caches contexts for 15 minutes. — Daniel Hilgarth, Feb 28 '11 at 13:53

score 2 · Accepted Answer · edited May 23 '17 at 10:33

2

First of all remove ObjectContext caching. Object context internally uses UnitOfWork and IdentityMap patterns. This can have big impact on queries.

edited May 23 '17 at 10:33

Community

1
1

answered Feb 28 '11 at 14:00

Ladislav Mrnka

349,807
56
643
654

Thanks for the suggestion. Unfortunately, I can't change that anymore. I don't remember the reason it was introduced, but I think it was performance related. Do you think, this is the reason for that bug? I can't see, how it should result in a problem like the one I described. – Daniel Hilgarth Feb 28 '11 at 14:08
This don't have to be the reason but it can. You have already validated that query itself (generated SQL) works. So the problem is somewhere in materializing result from SQL to objects. Materialization depends on the current state of `ObjectContext`. You can try to force `ObjectContext` to always load data from DB - use `ObjectQuery` and sets it `MergeOption` to `OverwriteChanges`. – Ladislav Mrnka Feb 28 '11 at 14:26
That's already set. The problem is not that the data is missing. If I request the whole table and then filter in memory in my application, I get the results I want to. Currently, I suppose that the problem is with a wrong parameter type being used. We had similar strange problems in the past with different numerical types. Looked like a bug in Oracle. – Daniel Hilgarth Feb 28 '11 at 14:32
@Daniel: So it can be problem with Oracle provider. If you have any support for it you should contact them. But first you should be sure that when L2E query is incorrectly executed, Oracle will not return any data. – Ladislav Mrnka Feb 28 '11 at 14:36

LINQ to Entities: Query not working with certain parameter value

1 Answers1