Mapping Rows to Objects

In simple words#

Learning objectives#

• Write reusable mapper methods
• Map to records and DTOs
• Map one-to-many results
• Keep readers inside the data layer

Explanation#

Write one static Map method per type that reads a row into an object. Every query for that type reuses it.

Records are ideal for read models: concise, immutable and with value equality.

For a JOIN that returns a category with its products, use a Dictionary keyed by category Id so each category object is created once.

Return lists or objects from your data layer — never SqlDataReader or DataTable — so callers do not depend on ADO.NET.

Code example: Categories with their products from one JOIN#

1public record ProductDto(int Id, string Name, decimal Price);
2public record CategoryDto(int Id, string Name, List<ProductDto> Products);
3

4static async Task<List<CategoryDto>> GetCatalogAsync(SqlConnection conn)
5{
6    await using var cmd = new SqlCommand("""
7        SELECT c.Id, c.Name, p.Id, p.Name, p.Price
8        FROM Categories c LEFT JOIN Products p ON p.CategoryId = c.Id
9        ORDER BY c.Name, p.Name
10        """, conn);
11    await using var r = await cmd.ExecuteReaderAsync();
12

13    var byId = new Dictionary<int, CategoryDto>();
14    while (await r.ReadAsync())
15    {
16        int catId = r.GetInt32(0);
17        if (!byId.TryGetValue(catId, out var cat))
18            byId[catId] = cat = new CategoryDto(catId, r.GetString(1), new());
19

20        if (!r.IsDBNull(2))                                   // LEFT JOIN: category may have no products
21            cat.Products.Add(new ProductDto(r.GetInt32(2), r.GetString(3), r.GetDecimal(4)));
22    }
23    return byId.Values.ToList();
24}
25

26foreach (var c in await GetCatalogAsync(conn))
27    Console.WriteLine(
quot;{c.Name}: {string.Join(", ", c.Products.Select(p => p.Name))}");

Accessories: Keyboard, Mouse
Computers: Laptop
Gifts:

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

1. The JOIN returns one row per product, repeating category columns.
2. The dictionary ensures each category is created once.
3. LEFT JOIN rows with NULL product columns are skipped.
4. The method returns plain DTOs.

Real-world use case#

Common mistakes#

Best practices#

• One mapper per type
• Return DTOs/records
• Use ordinals for speed
• Avoid N+1 queries

Remember this#

Practice exercise#

1static ProductDto Map(SqlDataReader r) =>
2    new(r.GetInt32(0), r.GetString(1), r.GetDecimal(2));

Mini quiz#

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

Related topics#

My notes#

Saved in this browser.