c# - How to populate objects with relationship from datatable?

Question

I am having trouble designing an approach for taking data from a CSV into business objects. I'm starting by parsing the CSV and getting each row into a DataTable and that is where my mental block starts.

I've got the following classes where APDistribution is considered a child of Voucher with a 1:Many relationship:

public class Voucher
{
    public string GPVoucherNumber { get; set; }
    public string VendorID { get; set; }
    public string TransactionDescription { get; set; }
    public string Title { get; set; }
    public string DocNumber { get; set; }
    public DateTime DocDate { get; set; }
    public decimal PurchaseAmount { get; set; }

    public IEnumerable<APDistribution> Distributions { get; set; }
}

public class APDistribution
{
    public string AccountNumber { get; set; }
    public decimal Debit { get; set; }
    public decimal Credit { get; set; }
    public string DistributionReference { get; set; }
}

My CSV looks like this. Several fields can repeat representing the Voucher transaction (Vendor, Title Invoice Number, Invoice Amount, etc), and some fields are the Distribution detail (Journal Account Code, Journal Amount).

I began by thinking I could use Linq to project onto my business objects but I'm failing to see how I can structure the query to do that in one pass. I find myself wondering if I can do one query to project into a Voucher collection, one to project into an APDistribution collection, and then some sort of code to properly associate them.

I started with the following where I am grouping by the fields that should uniquely define a Voucher, but that doesn't work because the projection is dealing with an anonymous type instead of the DataRow.

var vouchers =
            from row in invoicesTable.AsEnumerable()
            group row by new { vendor = row.Field<string>("Vendor Code"), invoice = row.Field<string>("Vendor Invoice Number") } into rowGroup
            select new Voucher
            { VendorID = rowGroup.Field<string>("Vendor Code") };

Is this achievable without introducing complex Linq that a future developer (myself included) could have difficulty understanding/maintaining? Is there a simpler approach without Linq that I'm overlooking?

标签： c#linq

Answer 1

The general idea is:

     invoicesTable
        .AsEnumerable()
        .GroupBy(x=> new { row.Field<string>("Vendor Code"), row.Field<string>("Vendor Invoice Number")})
        .Select(grouping =>
           new Voucher 
           {
              VendorID = grouping.First().Field<string>("VendorId") /* and so on */
              Distributions = grouping.Select(somerow => new redistribution {AccountNumber = somerow.Field<string>("AccountNumber")  /* and so on */}
        }

But this is not the most elegant way.