{"id":102170,"date":"2024-04-24T15:37:58","date_gmt":"2024-04-24T15:37:58","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=102170"},"modified":"2026-04-15T18:14:48","modified_gmt":"2026-04-15T18:14:48","slug":"working-with-iasyncenumerable-in-c","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/working-with-iasyncenumerable-in-c\/","title":{"rendered":"IAsyncEnumerable in C#: Streaming Data Asynchronously vs IEnumerable"},"content":{"rendered":"\n

Executive Summary<\/h2>\n\n\n\n

IAsyncEnumerable<T>, introduced in C# 8.0, allows you to iterate over a sequence of items asynchronously – processing each item as it arrives rather than waiting for the full sequence to load. This is particularly valuable for data pipelines: a database query that returns a million rows doesn’t need to materialise all rows into a List<T> before processing begins. Instead, each row can be processed (transformed, filtered, written) as it streams from the database, keeping memory consumption constant regardless of dataset size. This article demonstrates IAsyncEnumerable through an ETL pipeline using SQLite and AdventureWorks data, builds the same pipeline with IEnumerable for comparison, and benchmarks the memory difference. Requires .NET 8.0.<\/strong><\/p>\n\n\n\n

IAsyncEnumerable<\/code> is a powerful interface introduced in C# 8.0 that allows you to work with sequences of data asynchronously. It is a great fit for building ETLs that asynchronously stream data to get it ready for transfer. You can think of IAsyncEnumerable<\/code> as the asynchronous counterpart of IEnumerable<\/code> because both interfaces allow you to easily iterate through elements in a collection.<\/p>\n\n\n\n

Since the early days of .NET, the IEnumerable<\/code> interface has been fundamental for many programs. The IEnumerable<\/code> interface provided a way to retrieve elements from a collection one at a time, and the IEnumerable<T><\/code> interface extended this functionality to generic types. However, the IEnumerable<\/code> interface is synchronous, which means that it is not suitable for working with asynchronous data sources.<\/p>\n\n\n\n

Later, the Task<\/code> and Task<T><\/code> classes were introduced to simplify asynchronous programming with the async and await keywords. However, the Task<\/code> and Task<T><\/code> classes are not suitable for working with sequences of data because they represent a single operation that returns a result or an exception.<\/p>\n\n\n\n

The IAsyncEnumerable<\/code> interface was introduced to address the limitations of the IEnumerable<\/code> interface and the Task<\/code> class. This way, you can stream asynchronous data and process it efficiently as soon as it becomes available.<\/p>\n\n\n\n

In this take, you will learn how to work with IAsyncEnumerable<\/code> to asynchronously stream a big table and extract the data in a ETL process. You will also learn the difference between IAsyncEnumerable<\/code> and IEnumerable<\/code> and how to use IAsyncEnumerable<\/code> in your everyday work. Then, you will look at comparisons between the two different approaches and why one is better than the other in certain scenarios.<\/p>\n\n\n\n

The table used for this ETL will be the SalesOrderDetail<\/code> table from the AdventureWorks<\/code> database. This is the biggest table in the AdventureWorks<\/code> database, and it contains 121,317 rows. Don\u2019t worry, you will not need to install SQL Server and set up this whole database. There is a nice CSV file in the GitHub repository<\/a> you can download and unzip.<\/p>\n\n\n\n

The SalesOrderDetail<\/code> table is by far not the biggest table I have ever encountered but it is good enough for illustration purposes. In a real-world scenario, you might need to process a table with millions of rows or perhaps even billions of rows so imagine how this might scale. In such cases, you will need to use IAsyncEnumerable<\/code> to stream the data and process it efficiently.<\/p>\n\n\n\n

At the end of this exercise, you will have a GZIP file with the data extracted from the SalesOrderDetail<\/code> table. Because the load and transform steps are not the focus of this take, you will only extract the data from the table and save it to a GZIP file. You can use the extracted data to load it into a data warehouse, a data lake, or any other data storage system.<\/p>\n\n\n\n

What you will need to get started.<\/h3>\n\n\n\n

I will assume that you have a good understanding of C# 12 and .NET 8.0. I will also assume that you have the latest version of .NET 8.0 installed on your machine. To make this accessible to everyone, I will use the .NET 8.0 SDK and the .NET 8.0 CLI. If you are using Visual Studio, you can follow along by creating a new .NET 8.0 console application.<\/p>\n\n\n\n

Note, in this process, you will also be using Dapper<\/a> and SQLite<\/a>. The Dapper package is a high-performance micro-ORM that allows you to work with SQL databases in a more efficient way. The System.Data.SQLite<\/code> package is a lightweight ADO.NET provider for SQLite databases.<\/p>\n\n\n\n

Getting Started with the ETL<\/h2>\n\n\n\n

The first step is to create a new .NET 8.0 console application. You can do this by running the following command in your terminal:<\/p>\n\n\n\n

mkdir async-enumerable-etl\ncd async-enumerable-etl\ndotnet new console -n AsyncEnumerableEtl\ndotnet new sln -n AsyncEnumerableEtl\ndotnet sln add AsyncEnumerableEtl.csproj<\/pre>\n\n\n\n
The CLI command tool is a bit silly, and it will create a new folder with the same name as the project. You can delete the folder and move the project file to the root of the repository. You can also delete the obj<\/code> and bin<\/code> folders.<\/p>\n\n\n\n
The repository should look like this:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Figure 1. Folder structure<\/p>\n\n\n\n
The next step is to download the SalesOrderDetail.csv.zip<\/code> file from the GitHub repository and place it in the root of the repository. You can download the file by clicking here<\/a>.<\/p>\n\n\n\n
Then install two NuGet packages: Dapper<\/code> and System.Data.SQLite<\/code>. You will use these packages to read the SalesOrderDetail<\/code> table in a SQLite database. You can do this by running the following command in your terminal:<\/p>\n\n\n\n
dotnet add package Dapper\ndotnet add package System.Data.SQLite<\/pre>\n\n\n\n
Open the Program.cs<\/code> file and add the following code:<\/p>\n\n\n\n
using System.Data;\nusing Dapper;\nusing System.Data.SQLite;\nusing System.IO.Compression;\nvar mode = args.Length > 0 ? args[0] : \"IEnumerable\";\nswitch (mode)\n{\n  case \"IEnumerable\":\n    await WriteToCsvFileWithIEnumerable();\n    break;\n  case \"IAsyncEnumerable\":\n    await WriteToCsvFileWithIAsyncEnumerable();\n    break;\n  default:\n    Console.WriteLine(\"Invalid mode\");\n    break;\n}\nreturn;\nstatic async Task WriteToCsvFileWithIEnumerable()\n{\n  throw new NotImplementedException();\n}\nstatic async Task WriteToCsvFileWithIAsyncEnumerable()\n{\n  throw new NotImplementedException();\n}<\/pre>\n\n\n\n
You can now run the build command to make sure everything is working:<\/p>\n\n\n\n
dotnet build<\/pre>\n\n\n\n
Setting Up the SQLite Database<\/h2>\n\n\n\n
Expand the SalesOrderDetail.csv.zip<\/code> file and place the SalesOrderDetail.csv<\/code> file in the root of the repository. You can do this by running the following commands in your terminal:<\/p>\n\n\n\n
Expand-Archive -Path .\\SalesOrderDetail.csv.zip -DestinationPath .\\<\/pre>\n\n\n\n
Create a SQL script to install the AdventureWorks<\/code> database in a SQLite database. You can do this by creating a new file called `installadventureworks.sql<\/code>` in the root of the repository and adding the following code:<\/p>\n\n\n\n
CREATE TABLE [SalesOrderDetail] (\n  [SalesOrderID] INTEGER NOT NULL,\n  [SalesOrderDetailID] INTEGER IDENTITY (1, 1) NOT NULL,\n  [CarrierTrackingNumber] TEXT,\n  [OrderQty] INTEGER NOT NULL,\n  [ProductID] INTEGER NOT NULL,\n  [SpecialOfferId] INTEGER NOT NULL,\n  [UnitPrice] INTEGER NOT NULL,\n  [UnitPriceDiscount] INTEGER NOT NULL,\n  [LineTotal] INTEGER NOT NULL,\n  [rowguid] TEXT UNIQUE NOT NULL,\n  [ModifiedDate] DATETIME NOT NULL,\n  PRIMARY KEY ([SalesOrderID], [SalesOrderDetailID])\n);\n.separator ,\n.import SalesOrderDetail.csv SalesOrderDetail\nVACUUM;<\/pre>\n\n\n\n
Download the SQLite command line tools from the SQLite website and add the sqlite3<\/code> command to your PATH. You can download the SQLite command line tools by clicking here<\/a>.<\/p>\n\n\n\n
Create a new SQLite database by running the following command in your terminal:<\/p>\n\n\n\n
sqlite3 AdventureWorks.db < installadventureworks.sql<\/pre>\n\n\n\n
You can now run the following command to make sure the SalesOrderDetail<\/code> table was created:<\/p>\n\n\n\n
sqlite3 AdventureWorks.db \"SELECT COUNT(*) FROM \nSalesOrderDetail\"<\/pre>\n\n\n\n
Now that the SQL table is ready, you can start preparing the ETL process.<\/p>\n\n\n\n
Add Guid Types to the SalesOrderDetail Table<\/h2>\n\n\n\n
The SalesOrderDetail<\/code> table in the AdventureWorks<\/code> database has a column called rowguid<\/code> that is of type uniqueidentifier<\/code>. The uniqueidentifier<\/code> type in SQL Server is equivalent to the Guid<\/code> type in C#. You will need to add a GuidTypeHandler<\/code> to the Dapper<\/code> library to handle the Guid<\/code> type in SQLite.<\/p>\n\n\n\n
Open the Program.cs<\/code> file and add the following code:<\/p>\n\n\n\n
SqlMapper.AddTypeHandler(new GuidTypeHandler());\nSqlMapper.RemoveTypeMap(typeof(Guid));\nSqlMapper.RemoveTypeMap(typeof(Guid?));\n\/\/ after the return statement\npublic class GuidTypeHandler : SqlMapper.TypeHandler<Guid>\n{\n  public override void SetValue(IDbDataParameter parameter, Guid guid)\n  {\n    parameter.Value = guid.ToString();\n  }\n  public override Guid Parse(object value)\n  {\n    return new Guid((string)value);\n  }\n}<\/pre>\n\n\n\n
With this type handler in place, you can now declare the SalesOrderDetail<\/code> class.<\/p>\n\n\n\n
public class SalesOrderDetail\n{\n  public required int SalesOrderId { get; init; }\n  public required int SalesOrderDetailId { get; init; }\n  public string? CarrierTrackingNumber { get; init; }\n  public required short OrderQty { get; init; }\n  public required int ProductId { get; init; }\n  public required int SpecialOfferId { get; init; }\n  public required decimal UnitPrice { get; init; }\n  public required decimal UnitPriceDiscount { get; init; }\n  public required decimal LineTotal { get; init; }\n  public required Guid RowGuid { get; init; }\n  public required DateTime ModifiedDate { get; init; }\n}<\/pre>\n\n\n\n
To turn a single row of the SalesOrderDetail<\/code> table into a CSV row, you can add the following method to the Program.cs<\/code> file:<\/p>\n\n\n\n
static string GetSalesOrderDetailRow(SalesOrderDetail detail) =>\n  $\"{detail.SalesOrderId},{detail.SalesOrderDetailId},{detail.CarrierTrackingNumber},\n     {detail.OrderQty},{detail.ProductId},{detail.SpecialOfferId},\n     {detail.UnitPrice:F2},{detail.UnitPriceDiscount:F2},{detail.LineTotal:F2},\n     {detail.RowGuid},{detail.ModifiedDate.ToUniversalTime():o}\\r\\n\";<\/pre>\n\n\n\n
Writing the ETL Process with IEnumerable<\/h2>\n\n\n\n
The first step is to write the ETL process using the IEnumerable<\/code> interface. This is the traditional way of working with collections in C#. You can do this by adding the following code to the WriteToCsvFileWithIEnumerable<\/code> method:<\/p>\n\n\n\n
static async Task WriteToCsvFileWithIEnumerable()\n{\n  await using var fileStream = \n          File.Create(\"etl-salesorderdetail.csv.gz\");\n  await using var compressionStream = \n          new GZipStream(fileStream, CompressionLevel.Optimal);\n  await using var writer = new StreamWriter(compressionStream);\n  foreach (var detail in await \n                    GetSalesOrderDetailsWithIEnumerable())\n  {\n    await writer.WriteAsync(GetSalesOrderDetailRow(detail));\n  }\n}\nstatic async Task<IEnumerable<SalesOrderDetail>> \n                GetSalesOrderDetailsWithIEnumerable()\n{\n  const string sql = \"\"\"\n    SELECT\n      SalesOrderId,\n      SalesOrderDetailId,\n      CarrierTrackingNumber,\n      OrderQty,\n      ProductId,\n      SpecialOfferId,\n      UnitPrice,\n      UnitPriceDiscount,\n      LineTotal,\n      RowGuid,\n      ModifiedDate\n    FROM SalesOrderDetail\n  \"\"\";\n  await using var connection =\n      new SQLiteConnection(\"Data Source=AdventureWorks.db\");\n  return await connection.QueryAsync<SalesOrderDetail>(sql);\n}<\/pre>\n\n\n\n
The WriteToCsvFileWithIEnumerable<\/code> method creates a new GZIP file called etl-salesorderdetail.csv.gz<\/code> and writes the SalesOrderDetail<\/code> table to it. The GetSalesOrderDetailsWithIEnumerable<\/code> method reads the SalesOrderDetail<\/code> table from the SQLite database and returns it as an IEnumerable<SalesOrderDetail><\/code>. If you are not familiar with the Dapper<\/code> library, the QueryAsync<\/code> method is an extension method that allows you to execute a SQL query and map the results to a collection of objects.<\/p>\n\n\n\n
You can now run the following command to make sure the etl-salesorderdetail.csv.gz<\/code> file was created:<\/p>\n\n\n\n
dotnet run IEnumerable<\/pre>\n\n\n\n
This is the synchronous way of writing the ETL process in C#. Returning an IEnumerable<\/code> from the GetSalesOrderDetailsWithIEnumerable<\/code> method will dump the entire SalesOrderDetail<\/code> table into memory before returning it. This is not ideal for large tables, as it can use a lot of memory.<\/p>\n\n\n\n
Writing the ETL Process with IAsyncEnumerable<\/h1>\n\n\n\n
The next step is to write the ETL process using the IAsyncEnumerable<\/code> interface. This is the asynchronous way of working with collections in C#. You can do this by adding the following code to the WriteToCsvFileWithIAsyncEnumerable<\/code> method:<\/p>\n\n\n\n
static async Task WriteToCsvFileWithIAsyncEnumerable()\n{\n  await using var fileStream = \n       File.Create(\"etl-salesorderdetail.csv.gz\");\n  await using var compressionStream = \n       new GZipStream(fileStream, CompressionLevel.Optimal);\n  await using var writer = new StreamWriter(compressionStream);\n  await using var extract = \n       GetSalesOrderDetailsWithIAsyncEnumerable();\n  await foreach (var detail in extract.Data)\n  {\n    await writer.WriteAsync(GetSalesOrderDetailRow(detail));\n  }\n}\nstatic StreamedQuery<SalesOrderDetail> \n             GetSalesOrderDetailsWithIAsyncEnumerable()\n{\n  const string sql = \"\"\"\n    SELECT\n      SalesOrderId,\n      SalesOrderDetailId,\n      CarrierTrackingNumber,\n      OrderQty,\n      ProductId,\n      SpecialOfferId,\n      UnitPrice,\n      UnitPriceDiscount,\n      LineTotal,\n      RowGuid,\n      ModifiedDate\n    FROM SalesOrderDetail\n  \"\"\";\n  var connection = \n        new SQLiteConnection(\"Data Source=AdventureWorks.db\");\n  return new StreamedQuery<SalesOrderDetail>(\n    connection,\n    connection.QueryUnbufferedAsync<SalesOrderDetail>(sql));\n}\npublic class StreamedQuery<T>(\n  IAsyncDisposable connection,\n  IAsyncEnumerable<T> data) : IAsyncDisposable\n{\n  public IAsyncEnumerable<T> Data { get; } = data;\n  public ValueTask DisposeAsync() => connection.DisposeAsync();\n}<\/pre>\n\n\n\n
The StreamedQuery<\/code> class is a simple wrapper around the IAsyncEnumerable<\/code> interface. Notice that the connection is not disposed in the GetSalesOrderDetailsWithIAsyncEnumerable<\/code> method. This is because the StreamedQuery<\/code> class is responsible for disposing the connection. The extension method QueryUnbufferedAsync<\/code> is used to execute a SQL query and map the results to an IAsyncEnumerable<T><\/code>. This method is part of the `Dapper` library and is used to stream the results from the database to the application. The `await foreach` statement is used to iterate over the data asynchronously.<\/p>\n\n\n\n
When you stream data from the database to the application, it is important to leave the connection open until you are done with it. If you close the connection prematurely, you will get an error. This is why the StreamedQuery<\/code> class is responsible for disposing the connection.<\/p>\n\n\n\n
If you examine the code, you will notice that the connection<\/em> declaration lacks a using<\/em> statement. This statement is now in the consuming method where the stream is being used.<\/p>\n\n\n\n
This technique is ideal for large tables, as it does not load the entire table into memory. You can now run the following command to make sure the etl-salesorderdetail.csv.gz<\/code> file was created:<\/p>\n\n\n\n
dotnet run IAsyncEnumerable<\/pre>\n\n\n\n
Comparing the Performance of IEnumerable and IAsyncEnumerable<\/h2>\n\n\n\n
The memory consumption between the two approaches is the big concern. The IAsyncEnumerable<\/code> will stream the table and load only one row at a time. The IEnumerable<\/code> will load the entire table into memory before returning it.<\/p>\n\n\n\n
This is what memory consumption looks like when using IEnumerable<\/code>:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
This is what memory consumption looks like when using IAsyncEnumerable<\/code>:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
As you can see, the memory consumption in IEnumerable<\/code> is much higher at 25MB. Because it must load the entire table and more data ends up in heap generation 2. This puts more pressure on the garbage collector, which can lead to performance issues. The memory consumption in IAsyncEnumerable<\/code> is much lower at 8MB, and memory consumption remains relatively flat because most objects die young, which is ideal for performance.<\/p>\n\n\n\n
Depending on the size of the table, the IEnumerable<\/code> approach can cause memory and performance issues. Ideally, you should only use IEnumerable<\/code> for small tables that can easily fit into memory. For large tables, you should use IAsyncEnumerable<\/code> to asynchronously stream the data from the database to the application.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
In this article, you learned how to write an ETL process using the IEnumerable<\/code> and IAsyncEnumerable<\/code> interfaces. You also learned how to compare the performance of the two approaches. Each approach can work depending on the size of the table. By using the right approach, you can avoid memory and performance issues in your ETL process.<\/p>\n\n\n\n
\n    
FAQs: Working with IAsyncEnumerable in C#<\/h2>\n\n                        
1. What is IAsyncEnumerable in C# and when should I use it?<\/h3>\n            
\n                
IAsyncEnumerable<T> is an interface introduced in C# 8.0 for asynchronously iterating over sequences of data – using await foreach instead of foreach. Use it when: reading large result sets from a database row-by-row (avoiding materialising a million-row List<T>); streaming data from an API or file where items arrive over time; building ETL pipelines where each item can be processed independently; or when working with async streams from SignalR or ASP.NET Core endpoints. Do not use it when you need random access, LINQ aggregation on the full set, or when the source isn’t natively asynchronous.<\/p>\n            <\/div>\n                    
2. What is the difference between IAsyncEnumerable and IEnumerable in C#?<\/h3>\n            
\n                
IEnumerable<T> is synchronous – iterating it blocks the calling thread until each item is available. When used with database queries, Entity Framework or Dapper typically materialises the entire result set into memory before returning. IAsyncEnumerable<T> is asynchronous – await foreach yields control while waiting for the next item. For database access, this allows streaming: each row is processed as it arrives from the database, keeping memory consumption at O(1) per row rather than O(n) for the full result set. The tradeoff: IAsyncEnumerable requires the data source to support async streaming (EF Core’s AsAsyncEnumerable() or Dapper’s streaming support).<\/p>\n            <\/div>\n                    
3. How do I use IAsyncEnumerable with Entity Framework Core?<\/h3>\n            
\n                
Call AsAsyncEnumerable() on a DbSet or IQueryable to get an IAsyncEnumerable<T>. Use await foreach to iterate: await foreach (var item in context.Orders.Where(o => o.Status == “Pending”).AsAsyncEnumerable()) { Process(item); }. This streams results row-by-row from the database. Ensure the DbContext lifetime covers the entire iteration – do not dispose the context before the foreach completes. For large result sets, this pattern avoids the OOM exceptions or excessive memory pressure from ToListAsync() on million-row queries.<\/p>\n            <\/div>\n                    
4. Can I return IAsyncEnumerable from an ASP.NET Core Minimal API endpoint?<\/h3>\n            
\n                
Yes. ASP.NET Core’s Minimal API and controller endpoints can return IAsyncEnumerable<T> directly – the framework will stream the JSON serialisation to the client as items are produced. The response uses chunked transfer encoding, beginning to send before the source has produced all items. This is particularly useful for endpoints that stream large datasets or perform long-running data transformations, as the client starts receiving data immediately rather than waiting for the complete response. Use: app.MapGet(“\/stream”, () => streamingService.GetDataAsync())<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
IAsyncEnumerable in C# streams data rows one at a time, reducing memory consumption for large datasets compared to IEnumerable. Demonstrated with an ETL pipeline on SQLite\/AdventureWorks data, with benchmarked memory comparisons.…<\/p>\n","protected":false},"author":274017,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143538,53],"tags":[4143],"coauthors":[41241],"class_list":["post-102170","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","category-featured","tag-net"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/274017"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=102170"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102170\/revisions"}],"predecessor-version":[{"id":109770,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102170\/revisions\/109770"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=102170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=102170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=102170"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=102170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}