When to close memory streams if they are returned in methods and controllers

Question

I have some code that will be generating an excel report and I am planning on using it for some scheduled tasks but always wanted to return it from my API endpoint as well. My code for my controller is as follows:

[HttpGet("Export")]
[EnableRateLimiting("api")]
public async Task<IActionResult> TestExport()
{
    using (var memoryStream = await _reviewSummaryService.GetExcelReportAsync())
    {
        var fileStream = new FileStreamResult( memoryStream , "application/ms-excel" )
        {
            FileDownloadName = "ReviewsExport.xlsx"
        };

        return Ok(new ResponseDTO<FileStreamResult>(){
            Succeeded = true,
            StatusCode = 200,
            Message = "The data has been successfully downloaded.",
            Data = fileStream,
            Pagination = null});
    }
}

Then my service is as follows:

public async Task<MemoryStream> GenerateExcelReportAsync()
{
    var reviewSummaryPOCO = await _reviewSummaryRepository.GetAllAsync();
    var reviewSummaryDTO = _mappers.Map(reviewSummaryPOCO);

    using var wb = new XLWorkbook();
    var ws = wb.AddWorksheet();

    // Inserts the collection to Excel as a table with a header row.
    ws.Cell("A1").InsertTable(reviewSummaryDTO);

    // Adjust column size to contents.
    ws.Columns().AdjustToContents();
        
    var memoryStream = new MemoryStream();
    wb.SaveAs(memoryStream);

    return memoryStream;
}

public async Task ExportExcelReportAsync()
{
    using (var memoryStream = await GenerateExcelReportAsync())
    {
        var boxDocumentTask = await _boxRepository.UploadNewVersionAsync(memoryStream, _configuration["Box:ARMSExtractTrackerFileId"]!, "ReviewsExport.xlsx");
    }
}

public async Task<MemoryStream> GetExcelReportAsync()
{
    return await GenerateExcelReportAsync();
}

My error I get from the controller is cannot access a closed stream. However, I have removed the using statements from my service functions so it does not close the stream so it is unclear how its closed. Also, I do not want to introduce any memory leaks if thats even possible?

Answer 1

You need add the memoryStream.Position = 0; to reset stream position to the beginning

public async Task<MemoryStream> GenerateExcelReportAsync()
{
    //...    
    var memoryStream = new MemoryStream();
    wb.SaveAs(memoryStream);
    memoryStream.Position = 0; // Reset stream position to the beginning
    return memoryStream;
}

Then in your controller should be:

public async Task<IActionResult> TestExport()
{
    var memoryStream = await _reviewSummaryService.GetExcelReportAsync();
            
        var fileStream = new FileStreamResult(memoryStream, "application/ms-excel")
        {
            FileDownloadName = "ReviewsExport.xlsx"
        };
        return fileStream;          
}