Using LINQ to SQL to query large table (10M+ rows) results in timeout

I am seeing the timeout problem related to using LINQ to SQL to execute a query. So I make a simple console program, and the problem still happens, not always, but randomly enough number of times for me to be concerned. Here is almost my entire program:

    public static TransactionDataContext DataContext = new TransactionDataContext();

    static void Main(string[] args)
    {
        int year = ...;
        int stateCode = ...;

        DateTime minYear = new DateTime(year, 01, 01);
        DateTime maxYear = new DateTime(year, 12, 31);

        var volumes = DataContext.TRANSACTIONs.Where(p =>
                p.STATE_NO == stateCode &&
                p.TRANS_DATE >= minYear &&
                p.TRANS_DATE <= maxYear).OrderBy(p => p.TRANS_开发者_运维技巧DATE);

    }

The TRANSACTION table in my database has 10M+ records. With a break point set at the last closing curly brace, I run the debugger. Sometimes, expanding the volumes variable and the Results View show the data, and sometimes it says "Function evaluation timed out." Extending the timeout by setting DataContext.CommandTimeout doesn't seem to help and I don't think that's the solution anyway.

My question is why LINQ to SQL have this kind of limitation or if I'm doing wrong.

UPDATE: Running the SQL statement from Management Studio is OK, so I don't think it's an index problem.

---

This could potentially be a SQLServer question more than LINQ. You need a covering index on TRANS_DATE and STATE_NO. If you do not have then, that would explain the problem and it is not a LINQ issue.

---

I don't think the problem is because you have 10M records, since the query is not yet executed at all (since you didn't call ToList or ToArray).

What does take time is the generation of the SQL script.

I think first remove some of your query parameters, for instance, remove the OrderBy, and make the Where to be only p.STATE_NO == stateCode, and check what happens then.

Then, step by step add some of the params and you'll see what causes the problem. It might even be that this kind of date comparison is not supported, or maybe the STATE_NO is a diffferent type from stateCode, I am not sure, I am just thinking loudly.

This will help you get to to the problem.

You would also want to test working with Entity-SQL or programmatically (or even hard-coded query) shaping your query (no linq) this will help you get to the problem.

Another think you'd want to consider is using ToTraceString to see what EF generates for your linq.

However, if the above didn't help you with anything, you're gonna have to provide us with your exception details/stack trace to give us a clue of what this is all about.