Looping with foreach through list, to execute code for distinct values

Question

I am trying to create a function that creates one (1) PDF-invoice for each distinct value of a property in a List I am looping through (using PDFSharp).

I grab the value of the string, and want to use it as header of the invoices (it's the name of the business getting invoiced).

To illustrate: In the following table, I would like to get the value of LicenseHolder1 (once), LicenseHolder2 (once) and LicenseHolder3 (once).

ID	LicenseHolderID	Value1	Value2	Value3
1	LicenseHolder1	66	44	UF-2107
2	LicenseHolder2	22	25	UF-2107
3	LicenseHolder2	62	24	UF-2107
4	LicenseHolder3	82	12	UF-2107
5	LicenseHolder3	6	77	UF-2107
6	LicenseHolder3	15	62	UF-2107

This is the method I am using:

using (SqlConnection con = new(ConnectionString.connectionString))
using (SqlCommand cmd = new("spCreateInvoice", con) {CommandType = CommandType.StoredProcedure})
using (SqlDataAdapter da = new(cmd))
// this is the name of my SQL table
using (DataTable dt = new("tblTripsPerMonth")) 
using (DataSet ds = new()) {

    con.Open();
    da.Fill(dt);
    ds.Tables.Add(dt);

    /* adding the SQL table rows to my ObservableCollection (PaidTrips), 
    which is bound to properties in my holder class (PaidTrip): */

    foreach (DataRow dr in ds.Tables[0].Rows {

       PaidTrips.Add(new PaidTrip {

             LicenseHolderID = dr[0].ToString(),
             Value1 = (decimal)dr[1],
             Value2 = (decimal)dr[2],
             Value3 = dr[3].ToString(),
        });

        // Now I am instantiating a list, so that I can group
        List<PaidTrip> paidTrip = PaidTrips
            .GroupBy(p => new {p.LicenseHolderID})
            .Select(g => g.First())
            .ToList();
            
        // this is copied code from another question, and this is where I fall of
        foreach (PaidTrip PaidTrips in paidTrip) {

            foreach (var LicenseHolderID in PaidTrips.GetType().GetProperties()) {

                string n = LicenseHolderID.GetValue(PaidTrips).ToString();

If I after this grab string n and pass it into, let's say, a MessageBox.Show, it displays LicenseHolder1, as it should. But, if I try to pass it into the PDF, like this... (continued from last code-block)

                System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
                PdfDocument pdf = new();
                PdfPage page = pdf.AddPage();
                XGraphics gfx = XGraphics.FromPdfPage(page);

                // drawing in the string value
                gfx.DrawString(n, new XFont("Arial", 40, XFontStyle.Bold), myColor, new XPoint(40, 250));

                // saving the PDF
                pdf.Save("C:\File\Path\TestPDF.pdf");
            }
        }
    }
}

...the PDF is created, but the string I drew is not filled with LicenseHolder1, but rather, with (seemingly) random values of the other columns (like 66, UF-2107 or 77). How come?

(Oh, and I know that this code only creates one (1) PDF-file, I need several (one for each distinct value in the LicenseHolderID column -- but that's an issue for another day)

Answer 1

Is there an id column in your datatable as shown above? would your column zero not be taking an id there?

foreach (DataRow dr in ds.Tables[0].Rows {

   PaidTrips.Add(new PaidTrip {

         LicenseHolderID = dr[0].ToString(),
         Value1 = (decimal)dr[1],
         Value2 = (decimal)dr[2],
         Value3 = dr[3].ToString(),
    });

If your grouping by LicenseHolderID your selecting the first element of the grouped result. Is that intended?

List<PaidTrip> paidTrip = PaidTrips
        .GroupBy(p => new {p.LicenseHolderID})
        .Select(g => g.First())
        .ToList();

This would essentialy return one new Object Containing multiple Paid Trip objects containing the same LicenseHolderID.

Usually if you want to use the string name of the group you can reference the key value.

paidTrip.Key.ToString();
    

might be better to use,

    var paidTrip = PaidTrips
        .GroupBy(p => new {p.LicenseHolderID})
        .ToList();

    foreach (var tripFound in paidTrip)
    {
        string tripId = tripFound.Key.ToString();
        //Generate your PDF File.
    }

This should give you one file per group. Apologies if i've picked you up wrong.