LINQ to entities execution order/timing question

I have two long queries, each of type "typeViewRequest"

   results1 = ..... (timeout)

   results2=..... (timeout)

Now.....

Case 1-----------------------------------------------------------------------------

If I do

   results1 = ..... .Take(countRecordsToShow/2)
   results2 = ..... .Take(countRecordsToShow/2)

and 开发者_开发技巧then I say

   results = results1.Union<typeViewRequest>(results2);

it works fine. The grid, using paging, shows the countRecordsToShow records, no problem here.

Case 2-----------------------------------------------------------------------------

If I do

   results1 = ..... 
   results2 = .....

and then I say

   results = results1.Union<typeViewRequest>(results2).Take(countRecordsToShow)

then I have a timeout. Why ? Because my gridview, obviously, needs to get the count of records, to set its paging. My SelectCountMethod, called getPreviousRequestsSelectCount, simply says

public int getPreviousRequestsCountFromDB(String name, DateTime dtStart, DateTime dtEnd, ReportedBy_Filter reportedBy, Status_Filter status, ReportType_Filter type, int countRecordsToShow, int userID)
{
return getPreviousRequests(companyNameLike: name, dtStart: dtStart, dtEnd: dtEnd, reportedBy: reportedBy, status: status, type: type, sortExpression: null, userID: userID, countRecordsToShow: countRecordsToShow).Count();
}

Inside getPreviousRequests, in Case1, TAKE is applied (as described in "case 2"):

results = results1.Union<typeViewRequest>(results2).Take(countRecordsToShow)

while countRecordsToShow is only 20, but it times out ! I cache that number until the filtering criteria changes, yes, but still... :-((

Additional problem: I also have some filtering to do. This filtering should happen on BOTH branches, like so:

   results1 = .....  Where (something1)

   results2 = ..... .Where (something2)

but if I say, like in case 1,

   results1 = .....  Where (something1).Take(countRecordsToShow/2)

   results2 = ..... .Where (something2).Take(countRecordsToShow/2)

this will CUT my filtered datasets, I'm afraid - let's say that countRecordsToShow = 20. If results1 (filtered by something1) had, say, 15 records, I would take only 10. If results2 (filtered by something2) had, say, 5 records, I would take 5. Then the UNION would have 15 instead of 20 records.

Normally I should do this, like in Case2:

   results1 = .....  Where (something1)

   results2 = ..... .Where (something2)

AND then say

results = results1.Union<typeViewRequest>(results2).Take(countRecordsToShow)

If the filtered results1.Union(results2) had 25 records but countRecordsToShow were 20, then so be it. I would just say that the dataset is not complete, and additional filtering is necessary.

But since I have to trim the dataset BEFORE the UNION, this will affect my filtering BADLY !

I expected that the resulted query will first pull each branch, then execute the UNION, then filter, and then trim, right at the end.

Well, apparently if I don't trim each branch before doing anything else, I get a timeout.

How does this all work ? I'm VERY confused. I cannot even use a COUNT to find out how many records I should expect, and to behave in consequence, because COUNT enumerates the dataset, thus giving me a timeout.

What are my options, please ? (short of creating sprocs, which I am not allowed to do). Right now the only "solution" I have found is case1 - to apply a TAKE clause to each of the branches, BEFORE the Union, but, as I've mentioned, that would be wrong with regard to filtering.

What am I doing wrong here ? Do I really need to go with the "logically handicapped" case1 ? Please help !

Thank you

Alex

Note:

I have tested this: If after executing the line

results = results1.Union<typeViewRequest>(results2).Take(20)

I try to check

results.count()

in the immediate window, it times out ! Does it not see that it has a .Take(20) clause ? Why does it have to enumerate ALL the records first ??? I should enumerate up to the limit, 20, and then stop. Apparently it enumerates all the way to about 250.000, and then trims the resultset to 20.

Note: I read here: Exploring LINQ Query Operators and Differed Execution Plan that both TAKE and UNION are deffered. So then.... why is not TAKE integrated into the query, but applied AFTER the fact ?

I cannot say I understand your whole post, but I had a recent experience that might apply here . . . and since you have zero posts . . . I'll give it a try.

I had something like this:

var results = from ... ;
foreach(results)
{
    do something
}

Worked great, but it took four minutes to run because the results contained 4 Million records. Since I am a LINQ/EF4 newb, I changed it to this:

var results = from<blah>;
int count = 0;
foreach(results)
{
    if (count++ > 100)
        break;
    do something
}

Upon doing this, it timed out at the foreach statement after the 100th record and while terminating the foreach loop.

After much reading and experimenting the conclusion I came to is that the Query Will Complete!!!

Like it or not, the Query will run to completion.

So in its original format, I ran the Query out in the foreach loop itself by getting each little piece one at a time. Doing it that way would never timeout since resolving the next item in the results is really quick.

However, once I put in the break statement, after the 100th record, the query tried to complete itself in a single gulp and so it would timeout because the 4 million records could not be accepted in the timeout that a single call gets.

That fact that results is about to go out of scope and be destroyed didn't matter. The Query had to be completed . . . used or not . . . the query has to be completed.

It was a very frustrating, but enlightening exercise.

Frank