Mapping sql results with ado.net

Question

Hi.

What is best way to map sql result for query with joins (one-to many) with ado.net sqlDataReader ?

Answer 1

I'd use linq group by to do the mapping. sample with pubs authors and titles. AuthorModel has a Titles List:

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    var sql = @"
        select a.au_id, a.au_fname, a.au_lname, t.title_id, t.title
        from authors a
        join titleauthor ta on ta.au_id = a.au_id
        join titles t on ta.title_id = t.title_id
    ";
    var cmd = new SqlCommand(sql,conn);
    var reader = await cmd.ExecuteReaderAsync();
    var list = new List<QueryAuthorModel>();

    // map query to query model
    while(await reader.ReadAsync())
    {
        list.Add(new QueryAuthorModel
        {
            au_id = reader.GetString(0),
            au_fname = reader.GetString(1),
            au_lname = reader.GetString(2),
            title_id = reader.GetString(3),
            title = reader.GetString(4)
        });
    }

    // list of authors where each author has a list of titles
    var r = list.GroupBy(
        row => row.au_id,
        row => row,
        (key, rows) => rows.Select(row => new AuthorModel
        {
            Id = key,
            FirstName = row.au_fname,
            LastName = row.au_fname,
            Titles = rows.Select(row => new TitleModel
            {
                Id = row.title_id,
                Title = row.title
            }).ToList()
        }).First()
    ).ToList();
}

Answer 2

You could have a look at using multiple result sets in Dapper.NET:

https://www.learndapper.com/dapper-query/selecting-multiple-results#dapper-querymultiple

Rather than joining all the associations onto the main result set, you could just do multiple selects (filtering by the same primary keys if necessary) & then Dapper will let you just read the results of each result set in your app.

If you want to link the foreign key entities onto your main table entities then you could turn all the foreign key tables into dictionaries, keying off the foreign key.

If you do want to just use plain ADO.NET then you could achieve the same thing with a bit more code:

http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm