How to return Task<Object> using continuation on a child task

Question

I like to minimize the scheduling of tasks that wait on eachother but not show it's one How would I alter my TestOption1 to return a Task the the calling method?

    [TestClass()]
    public class SqlServerTests
    {
        public const string Membership = "Data Source=LocalHost;Initial Catalog=tempdb;Integrated Security=True;";

        [TestMethod()]
        public async Task ContinueWithTest()
        {
            using CancellationTokenSource cts = new CancellationTokenSource();
            //warm up so pooling is enabled on all 3 methods
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "set nocount on";

                con.Open();
                cmd.ExecuteNonQuery();
            }

            var sw2 = System.Diagnostics.Stopwatch.StartNew();
            await TestOption2(cts.Token).ConfigureAwait(false);
            sw2.Stop();

            //allow the benefit of the doubt for the slower and give it cashed plans
            var sw3 = System.Diagnostics.Stopwatch.StartNew();
            await TestOption3(cts.Token).ConfigureAwait(false);
            sw3.Stop();

            Assert.IsTrue(sw2.ElapsedTicks < sw3.ElapsedTicks, "Stopwatch 2 {0} Stopwatch 3 {1}", sw2, sw3);


            var sw1 = System.Diagnostics.Stopwatch.StartNew();
            await TestOption1(cts.Token).ConfigureAwait(false);
            sw1.Stop();
            Console.WriteLine($"TestOption1: No internal awaits {sw1.ElapsedTicks:N0} ticks");
            Console.WriteLine($"TestOption2: 1x internal await {sw2.ElapsedTicks:N0} ticks");
            Console.WriteLine($"TestOption3: 2x internal await {sw3.ElapsedTicks:N0} ticks");

            Assert.IsTrue(sw1.ElapsedTicks < sw2.ElapsedTicks, "Stopwatch 1 {0} Stopwatch 2 {1}", sw1, sw2);
            Assert.IsTrue(sw1.ElapsedTicks < sw3.ElapsedTicks, "Stopwatch 1 {0} Stopwatch 3 {1}", sw1, sw3);
        }

        private static Task TestOption1(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "set nocount on";

                return con.OpenAsync(cancellationToken)//fails as it does not wait for the db to open....
                      .ContinueWith((t) => cmd.ExecuteNonQuery()
                                        , cancellationToken
                                        , continuationOptions: TaskContinuationOptions.ExecuteSynchronously
                                        , scheduler: TaskScheduler.Default);
            }
        }

        private static async Task TestOption2(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "set nocount on";

                await con.OpenAsync(cancellationToken)
                      .ContinueWith((_) => cmd.ExecuteNonQuery(), cancellationToken).ConfigureAwait(false);
            }
        }

        private static async Task TestOption3(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "set nocount on";

                await con.OpenAsync(cancellationToken).ConfigureAwait(false);
                await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
            }
        }
    }

I would like to be able to do something like this

    [TestMethod]
    public async Task TestContinueWithDelegate()
    {
        var data = await TestOptionReturn().ConfigureAwait(false);
        Assert.IsNotNull(data);
    }

    private static Task<object> TestOptionReturn(CancellationToken cancellationToken = default)
    {
        using (var con = new SqlConnection(Membership))
        using (var cmd = con.CreateCommand())
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "Test1";

            return con.OpenAsync(cancellationToken)//fails as it does not wait for the db to open....
                  .ContinueWith(delegate { return cmd.ExecuteScalar(); }
                                    , cancellationToken
                                    , continuationOptions: TaskContinuationOptions.ExecuteSynchronously
                                    , scheduler: TaskScheduler.Default);
        }
    }

The following tests fail as the database doesn't open

[TestMethod] public async Task TestContinueWithDelegate() { using CancellationTokenSource cts = new CancellationTokenSource(); var data = await TestOptioDDL(cts.Token).ConfigureAwait(false); using var reader = await TestOptionOutput(cts.Token).ConfigureAwait(false); Assert.IsNotNull(data); Assert.IsTrue(reader.Read()); }

        private static Task<object> TestOptioDDL(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "TestOutput";
                cmd.Parameters.Add(new SqlParameter("Data", System.Data.SqlDbType.DateTime) { IsNullable = true, Direction = System.Data.ParameterDirection.Output });
                return con.OpenAsync(cancellationToken)//fails as it does not wait for the db to open....
                      .ContinueWith(delegate
                      {
                          cmd.ExecuteScalar();
                          return cmd.Parameters[0].Value;
                      }
                      , cancellationToken
                      , continuationOptions: TaskContinuationOptions.ExecuteSynchronously
                      , scheduler: TaskScheduler.Default);
            }
        }

        private static Task<SqlDataReader> TestOptionOutput(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select * from sys.databases";
                cmd.Parameters.Add(new SqlParameter("Data", System.Data.SqlDbType.DateTime) { IsNullable = true, Direction = System.Data.ParameterDirection.Output });
                return con.OpenAsync(cancellationToken)//fails as it does not wait for the db to open....
                      .ContinueWith(delegate
                      {
                          return cmd.ExecuteReader();
                      }
                      , cancellationToken
                      , continuationOptions: TaskContinuationOptions.ExecuteSynchronously
                      , scheduler: TaskScheduler.Default);
            }
        }

Answer 1

TestOption3 is the best choice. Optimize for maintainability, not for saving a few milliseconds on what will be a highly I/O-bound task.

That said, your connection is closed because your connection is being disposed before the tasks complete (possibly before it's even opened!). If you're going to remove async and await, then you need to handle rewriting your method so that the disposal at the end of the using will be run after the tasks complete.