1. We have the following code in a data repository that uses Dapper's multi recordset feature to return a single order with many related detail lines in a single database call: 

using (var connection = new SqlConnection(_connectionString))
{
  connection.Open();
  
  using (GridReader results = connection.QueryMultiple(
    @"EXEC dbo.Order_GetHeader @OrderId = @OrderId; 
    EXEC dbo.OrderDetails_Get_ByOrderId @OrderId = @OrderId",
    new { OrderId = orderId }))
  {

    // TODO - Read the order and details from the query result

    return order;
  }
}

What are the missing statements that will read the order and its details from the results putting the details in the order model? The order model is of the OrderGetSingleResponse type, which contains a Details property of the IEnumerable<OrderDetailGetResponse> type.

We can use the following:

using (var connection = new SqlConnection(_connectionString))
{
  connection.Open();
  
  using (GridReader results = connection.QueryMultiple(
    @"EXEC dbo.Order_GetHeader @OrderId = @OrderId; 
    EXEC dbo.OrderDetails_Get_ByOrderId @OrderId = @OrderId",
    new { OrderId = orderId }))
  {
    var order = results.Read<OrderGetSingleResponse>().FirstOrDefault();
    if (order != null)
    {
      order.Details = results.Read<OrderDetailGetResponse>().ToList();
    }
    return order;
  }
}

2. What is the downside of using Dapper's multi-mapping feature when reading data from many-to-one related tables in a single database call?

The trade-off is that more data is transferred between the database and web server and then processed on the web server, which can hurt performance.

3. How does data paging help performance?

• The number of the page read I/Os is reduced when SQL Server grabs the data.
• The amount of data transferred from the database server to the web server is reduced.
• The amount of memory used to store the data on the web server in our model is reduced.
• The amount of data transferred from the web server to the client is reduced.

4. Does making code asynchronous make it faster?

No, it makes it more scalable by using the thread pool more efficiently.

5. What is the problem with the following asynchronous method:

public async AnswerGetResponse GetAnswer(int answerId)
{
  using (var connection = new SqlConnection(_connectionString))
  {
    connection.Open();
    return await connection
      .QueryFirstOrDefaultAsync<AnswerGetResponse>(
        "EXEC dbo.Answer_Get_ByAnswerId @AnswerId = @AnswerId", 
        new { AnswerId = answerId });
  }
}

Opening the connection is synchronous, which will mean the thread is blocked and not returned to the thread pool until the connection is opened. So, the whole code will have the same thread pool inefficiency as synchronous code but will have the overhead of asynchronous code as well.

Here is the corrected implementation:

public async AnswerGetResponse GetAnswer(int answerId)
{
  using (var connection = new SqlConnection(_connectionString))
  {
    await connection.OpenAsync();
    return await connection
      .QueryFirstOrDefaultAsync<AnswerGetResponse>(
        "EXEC dbo.Answer_Get_ByAnswerId @AnswerId = @AnswerId", 
        new { AnswerId = answerId });
  }
}

6. Why it is a good idea to set a size limit on a memory cache?

This is to prevent the cache from taking up too much memory on the web server.

7. In our QuestionCache implementation, when adding a question to the cache, how can we invalidate that item in the cache after 30 minutes?

We can do the following:

public void Set(QuestionGetSingleResponse question)
{
  var cacheEntryOptions = 
    new MemoryCacheEntryOptions()
    .SetSize(1)
    .SetSlidingExpiration(TimeSpan.FromMinutes(30));
  _cache.Set(GetCacheKey(question.QuestionId), question, cacheEntryOptions);
}

8. When we registered our QuestionCache class for dependency injection, why did we use the AddSingleton method and not the AddScoped method like in the following? 

services.AddScoped<QuestionCache>();

AddScoped would create a new instance of the cache for every request, which means the cache would be lost after each request. Using AddSingleton means that the cache lasts for the lifetime of the app.