CODEWITHSUNDEEP
.netsql-serverdatabasec#-3.0
Elad Benda
Elad Benda

Reputation: 36654

mapping DB data to List of lists

I have a DataModel of the form:

class A:

class B

I invole DB sp that returns dataSet of

A_Id, A_Date, B_Name, B_Id

What would be the most readable way to map this dataSet into object of type List<A> ?

I tried dataSet and DataReader - both seemed complex

Upvotes: 1

Views: 95

Answers (2)

Lee
Lee

Reputation: 144136

Assuming you have a single table, you can use Linq-to-DataTable:

List<A> aList = dataTable.AsEnumerable()
    .GroupBy(row => row.Field<int>("A_Id"))
    .Select(grp => new A {
        Id = grp.Key,
        Name = grp.First().Field<DateTime>("A_Date"),
        Bs = grp.Select(m => new B { Id = m.Field<int>("B_Id"), Name = m.Field<string>("B_Name") }).ToList(),
    })
    .ToList()

Upvotes: 1

David M
David M

Reputation: 72860

I assume you have a reason for not using an ORM tool like NHibernate that does away with this sort of thing for you. If so, then I'd tend to use a data reader for this. It's laborious rather than complex IMHO.

Track the current A.Id in a variable as you iterate through the reader, and keep the "current" instance of A in a variable too. Each row in the reader, if A.Id has changed, set this current A to a new instance, and add it to the list. And each row in the reader, create a B and add it to the current A's List<B> property.

var result = new List<A>();
int currentId = -1;
A currentA;
while (reader.Read())
{
    int id = reader.GetInt32(reader.GetOrdinal("A_Id"));
    if (id != currentId)
    {
        currentId = id;
        currentA = new A
            {
                Id = id,
                Date= reader.GetDateTime(reader.GetOrdinal("A_Date")),
                List = new List<B>()
            };
        result.Add(currentA);
    }
    currentA.List.Add(new B
        {
            Id = reader.GetInt32(reader.GetOrdinal("B_Id")),
            Name = reader.GetString(reader.GetOrdinal("B_Name"))
        };
}

Don't forget to dispose your reader afterwards...

Upvotes: 0

Related Questions