How do I iteratively assign to a nested list within an object in C#?

Question

I've been asked to replicate a complex xml structure we use within our internal systems, against data retrieved from another system. Unfortunately the xml structure is improvised and we have no specification for it.

I've been mapping out it's structure in C# classes so that I can assign those properties with values from the database and ultimately serialise it as xml.

I've hit a bit of a road block in terms of iteratively adding new list items to a nested list within an object that's already being intialised as a list and being looped through. To make matters more complicated I need to use a value from the iteration to filter down the dataset being used to instantiate the second loop round.

Sorry for the poor explanation - open to rewording... hopefully however the example I've written will demonstrate what I'm trying to do:

using System;
using System.Collections.Generic;
using System.Data;

public class TransactionModel
{

    public int Id { get; set; }
    public string Description { get; set; }
    public DateTime SysDate { get; set; }
    public List<TransactionItemModel> Trade { get; set; } = new List<TransactionItemModel>();

}

public class TransactionItemModel
{

        public int ItemId { get; set; }
        public int TransactionId { get; set; }
        public string ItemDescription { get; set; }
        public decimal ItemNetAmount { get; set; }

    }

public class Program 
{
    public void Main()
    {
        DataTable tranResultSet = MethodToReturnResultsFromTranQuery();
        DataTable itemResultSet = MethodToReturnResultsFromItemQuery();

        var transactions = new List<TransactionModel>();
        foreach (DataRow tran in tranResultSet.Rows)
        {

            transactions.Add(

                new TransactionModel() {
                    Id = (dynamic)tran["Id"],
                    Description = (dynamic)tran["Description"],
                    SysDate = (dynamic)tran["SysDate"],
                    //Trade = <Stuck Here>
                    // Need to iterate through itemResultSet, adding to TransactionModel.Trade
                    // where item["TransactionId"] = tran["Id"]

                }

            );

        }

    }
}

Answer 1

This approach doesn't set the Trade collection initially, but populates it once you go through the Items. There's likely a lot of optimization that can be added, but this might get you started.

public class TransactionModel
{
    public int Id { get; set; }
    public string Description { get; set; }
    public DateTime? SysDate { get; set; }
    public List<TransactionItemModel> Trade { get; set; }

    public TransactionModel(DataRow row)
    {
        if(row == null)
            throw new ArgumentNullException(nameof(row));

        Id = row.Field<int>("Id");
        Description = row.Field<string>("Description");
        SysDate = row.Field<DateTime?>("SysDate");
        Trade = new List<TransactionItemModel>();
    }
}

public class TransactionItemModel
{
    public int ItemId { get; set; }
    public int TransactionId { get; set; }
    public string ItemDescription { get; set; }
    public decimal? ItemNetAmount { get; set; }

    public TransactionItemModel(DataRow row)
    {
        if(row == null)
            throw new ArgumentNullException(nameof(row));

        ItemId = row.Field<int>("Id");
        TransactionId = row.Field<int>("TransactionId");
        ItemDescription = row.Field<string>("ItemDescription");
        ItemNetAmount = row.Field<decimal?>("ItemNetAmount");
    }
}

public static class Program
{
    private static void Main()
    {
        DataTable tranResultSet = MethodToReturnResultsFromTranQuery();
        DataTable itemResultSet = MethodToReturnResultsFromItemQuery();

        var transactions = tranResultSet.AsEnumerable()
                                        .Select(r => new TransactionModel(r));

        foreach(TransactionModel transaction in transactions)
        {
            var items = itemResultSet.AsEnumerable()
                                     .Where(r => r.Field<int>("TransactionId") == transaction.Id)
                                     .Select(r => new TransactionItemModel(r));

            transaction.Trade.AddRange(items);
        }
    }
}

It's likely going to be ideal to query your ItemResultSet based on the current TransactionId instead of grabbing them all up front. You could implement a DataReader, or use Dapper.