How can a user cancel a long running query? [duplicate]

Question

I'm in the process of writing a query manager for a WinForms application that, among other things, needs to be able to deliver real-time search results to the user as they're entering a query (think Google's live results, though obviously in a thick client environment rather than the web). Since the results need to start arriving as the user types, the search will get more and more specific, so I'd like to be able to cancel a query if it's still executing while the user has entered more specific information (since the results would simply be discarded, anyway).

If this were ordinary ADO.NET, I could obviously just use the DbCommand.Cancel function and be done with it, but we're using EF4 for our data access and there doesn't appear to be an obvious way to cancel a query. Additionally, opening System.Data.Entity in Reflector and looking at EntityCommand.Cancel shows a discouragingly empty method body, despite the docs claiming that calling this would pass it on to the provider command's corresponding Cancel function.

I have considered simply letting the existing query run and spinning up a new context to execute the new search (and just disposing of the existing query once it finishes), but I don't like the idea of a single client having a multitude of open database connections running parallel queries when I'm only interested in the results of the most recent one.

All of this is leading me to believe that there's simply no way to cancel an EF query once it's been dispatched to the database, but I'm hoping that someone here might be able to point out something I've overlooked.

TL/DR Version: Is it possible to cancel an EF4 query that's currently executing?

Answer 1

Looks like you have found some bug in EF but when you report it to MS it will be considered as bug in documentation. Anyway I don't like the idea of interacting directly with EntityCommand. Here is my example how to kill current query:

var thread = new Thread((param) =>
    {
        var currentString = param as string;

        if (currentString == null)
        {
            // TODO OMG exception
            throw new Exception();
        }

        AdventureWorks2008R2Entities entities = null;
        try // Don't use using because it can cause race condition
        {
            entities = new AdventureWorks2008R2Entities();

            ObjectQuery<Person> query = entities.People
                .Include("Password")
                .Include("PersonPhone")
                .Include("EmailAddress")
                .Include("BusinessEntity")
                .Include("BusinessEntityContact");
            // Improves performance of readonly query where
            // objects do not have to be tracked by context
            // Edit: But it doesn't work for this query because of includes
            // query.MergeOption = MergeOption.NoTracking;

            foreach (var record in query 
                .Where(p => p.LastName.StartsWith(currentString)))
            {
                // TODO fill some buffer and invoke UI update
            }
        }
        finally
        {
            if (entities != null)
            {
                entities.Dispose();
            }
        }
    });

thread.Start("P");
// Just for test
Thread.Sleep(500);
thread.Abort();

It is result of my playing with if after 30 minutes so it is probably not something which should be considered as final solution. I'm posting it to at least get some feedback with possible problems caused by this solution. Main points are:

• Context is handled inside the thread
• Result is not tracked by context
• If you kill the thread query is terminated and context is disposed (connection released)
• If you kill the thread before you start a new thread you should use still one connection.

I checked that query is started and terminated in SQL profiler.

Edit:

Btw. another approach to simply stop current query is inside enumeration:

public IEnumerable<T> ExecuteQuery<T>(IQueryable<T> query)
{
    foreach (T record in query)
    {
        // Handle stop condition somehow
        if (ShouldStop())
        {
            // Once you close enumerator, query is terminated
            yield break;
        }
        yield return record;
    }
}