1

How Do I Use CancellationTokenSource With DbContext.Database.SqlQuery(Sp, Param)?

I'm using EF6.2 and running a stored procedure with a return type.

var task = Db.Database.SqlQuery<ResultType>(sp, params).ToListAsync();

foreach(var result in task.Result)
{
   var row = result;
}

Our current solution has cancellation tokens but doesn't return anything:

var task = Db.Database.ExecuteSqlCommandAsync(sp, tokenSource.Token, param);

SqlQuery only has the one method signiture, and I cant see anything about canceling in the documentation

How do I use the first method AND include a cancellation token? Edit: Or is there another method I should use?

Additional Info: Our current solution ExecuteSqlCommandAsync() returns a task that just has a "completed" int status as the return type. The stored procedures I'm calling was just an INSERT... but I need to change it to a SELECT so we can produce files with the results.

So I need a EF method that returns a task of type "ResultType" so I can foreach through them and produce our files.

SqlQuery<>() does this... but without the ability to add a cancellation token (by the looks of it anyway, hence the question)

Osie J O'Connor
  • 421
  • 7
  • 14
  • *"Our current solution has cancellation tokens but doesn't return anything:"* well, that seems like your problem. You should probably assume that *in fact, it isn't a joke method to put your off the scent*. It **does** return things. You're not using it correctly. Your first step is to examine your variables at runtime when calling both methods to ensure you're not screwing something up simple. Next step is to use Sql Profiler to examine both queries as they hit the database. What are the differences? Why are they different? –  Feb 20 '18 at 17:53
  • 1
    @Will I'm sorry if it wasn't clear - ExecuteSqlCommandAsync() returns a task with a result of int (int for the complete status of the task). Its for UPDATE or INSERT sps, whereas the sp I want to call is a SELECT. – Osie J O'Connor Feb 20 '18 at 17:57
  • @Will I've updated the main description to further explain the situation – Osie J O'Connor Feb 20 '18 at 18:01
  • Aaaah, that makes a lot of sense. Looking at your first call, it struck me that the async part of that method chain isn't the `SqlQuery` part, but `ToListAsync`. There's probably an overload that takes the token. –  Feb 20 '18 at 18:11
  • 1
    @PeterBons that's not the final code, I was more to show that I do want a type returned. We will be using the task correctly in the actual code – Osie J O'Connor Feb 20 '18 at 18:16
  • @Will the first part (before ".ListAsync") returns a task. At that point it seems we are out of EF/SQL and into normal C# tasks. I can't see any method/variable on the task returned to add a token – Osie J O'Connor Feb 20 '18 at 18:19
  • 1
    Maybe, maybe not... You can test it by using Profiler to see if just getting the task hits the database. There's lots of magic in EF land. Anyhow, good luck. –  Feb 20 '18 at 18:20
  • 1
    You have to pass the token to .ToListAsync(). Only when .ToListAsync(cancellationToken) is called the query is materialized and send to the server. But beware that the option to pass a cancellationtoken might not have the effect you are hoping to see, see https://stackoverflow.com/questions/25387636/ef6-tolistasync-cancel-long-query-not-working TL;DR: the query will still run to completion at sql server database level. – Peter Bons Feb 20 '18 at 18:59
  • @PeterBons how did I miss that?! Thank you Peter! I'll do further research tomorrow and test using the tokens – Osie J O'Connor Feb 20 '18 at 19:02
  • @PeterBons I'm happy with your answer, would you like to post your comment as an actual answer so I can mark it? – Osie J O'Connor Feb 21 '18 at 09:51

1 Answers1

2

You have to pass the token to .ToListAsync(). Only when .ToListAsync(cancellationToken) is called the query is materialized and send to the server.

But beware that the option to pass a CancellationToken might not have the effect you are hoping to see, see this Q&A/ TL;DR: the query will still run to completion at sql server database level.

Peter Bons
  • 26,826
  • 4
  • 50
  • 74
  • Is there anything that could help us cancel the query generated by entityframework when we call ToListAsync()? I'm seeing in search that it only cancels the task in program but not the query in SQL – aj go Jun 24 '22 at 06:08