Q:

Async Tasks Seem to Run Synchronously

Within Run, I'm trying to queue 10,000 tasks.   (This is just an experiment so I can better understand async await.)   But the first loop takes over three seconds to complete.   I would expect this to be faster since I'm just trying to queue the tasks and await them a few lines later.   Still within Run, alreadyCompleted is true because the tasks seem to have run synchronously.   Finally, still within Run, the second loop takes 1 millisecond to complete, again showing the tasks have already run. 
How can I queue tasks to run while still allowing execution to progress through the Run method until I use await?   I would understand if some of my tasks had completed by the time alreadyCompleted gets checked, but it seems weird that all of them have completed.   This behavior is consistent between ASP.NET Core 3, .NET Core 3 Console, and .NET Framework Console.   Thank you. 
using System. Collections. Generic;
using System. Data;
using System. Data. SqlClient;
using System. Diagnostics;
using System. Linq;
using System. Runtime. CompilerServices;
using System. Threading;
using System. Threading. Tasks;

public static class SqlUtility
{
    public static async Task Run()
    {
        const int max = 10000;
        Stopwatch sw1 = Stopwatch. StartNew();
        Task<int>[] tasks = new Task<int>[max];
        for(int i=0;i<max;i++)
        {
            tasks[i] = GetVideoID();
        }
        sw1.Stop();//ElapsedMilliseconds:  3169
        bool alreadyCompleted = tasks. All(t => t.IsCompletedSuccessfully);//true
        Stopwatch sw2 = Stopwatch. StartNew();
        for (int i = 0; i < max; i++)
        {
            await tasks[i].ConfigureAwait(false);
        }
        sw2.Stop();//ElapsedMilliseconds:  1
    }

    public static async Task<int> GetVideoID()
    {
        const string connectionString =
            "Server=localhost;Database=[Redacted];Integrated Security=true";
        SqlParameter[] parameters = new SqlParameter[]
        {
            new SqlParameter("VideoID", SqlDbType. Int) { Value = 1000 }
        };
        const string commandText = "select * from Video where VideoID=@VideoID";
        IAsyncEnumerable<object> values = GetValuesAsync(connectionString, parameters, commandText,
            CancellationToken. None);
        object videoID = await values. FirstAsync().ConfigureAwait(false);//1000
        return (int)videoID;
    }

    public static async IAsyncEnumerable<object> GetValuesAsync(
        string connectionString, SqlParameter[] parameters, string commandText,
        [EnumeratorCancellation]CancellationToken cancellationToken)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            await connection. OpenAsync(cancellationToken).ConfigureAwait(false);
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                command. Parameters. AddRange(parameters);
                using (var reader = await command. ExecuteReaderAsync()
                    .ConfigureAwait(false))
                {
                    while (await reader. ReadAsync().ConfigureAwait(false))
                    {
                        yield return reader[0];
                    }
                }
            }
        }
    }
}

Update
While running a .NET Core application and using a max of 1,000, alreadyCompleted is false.   In fact, looking at IsCompleted of each task, none of the tasks are completed where alreadyCompleted is currently.   This is what I would have expected to happen in my original scenario. 
But if max is set to 10,000 (as it was originally), alreadyCompleted is false, just as above.   Interestingly, checking tasks[max - 1].IsCompleted results in false (checking the last task first).   tasks. All(t => t.IsCompletedSuccessfully) checks the last result last, at which point IsCompleted is true. 

A:

It's important to understand that async methods start running synchronously.  The magic happens when await is given an incomplete Task. ( if it's given a completed Task, execution continues synchronously)
So when you call GetVideoID, this is what it's doing:

GetVideoID runs until GetValuesAsync()
GetValuesAsync runs until await connection. OpenAsync
OpenAsync runs until it returns a Task. 
The await in GetValuesAsync sees the incomplete Task and returns its own incomplete Task. 
Execution of GetVideoID resumes until records. FirstAsync() is called. 
FirstAsync() runs until it returns an incomplete Task. 
The await in GetVideoID sees the incomplete Task and returns its own incomplete Task. 
Execution of Run resumes. 

All of that happens synchronously.  Three seconds to do that 10,000 times is pretty reasonable. 
So why is every Task already completed when you check them?  The short answer is that the continuations of those tasks are running on other threads. 
If this is not a web app (or if it was .NET Framework), then it's the ConfigureAwait(false) that is making that happen.  Without that, the continuations would have to wait until execution of Run() pauses.  But with ConfigureAwait(false), the continuations can run in any context, so they're run on a ThreadPool thread. 
If this is ASP.NET Core, which does not have any synchronization context, it would happen anyway even without the ConfigureAwait(false).  That article talks about this under the heading Beware Implicit Parallelism:

ASP.NET Core does not have a SynchronizationContext, so await defaults to the thread pool context.  So, in the ASP.NET Core world, asynchronous continuations may run on any thread, and they may all run in parallel. 

In summary:

It's taking 3 seconds to run the initial, synchronous part of those 10,000 tasks. 
The continuations of those tasks all run in parallel on other threads. 
By the time you check, they're all done.  Hence, "awaiting" doesn't need to wait at all. 

Update: If you want to avoid the initial 3-second delay, then you can start the task on another thread by using Task. Run, which will allow your method to continue on almost immediately.  Just replace this:
tasks[i] = GetVideoID();

With this:
tasks[i] = Task. Run(() => GetVideoID());

However, there is overhead in that, so you should time how long the entire operation takes to complete and see if it actually helps you. 
Although that might not be a good idea in ASP.NET due to the limited number of threads available.