Malisa Ncube - C# and .NET Delights

In should say that the first time I looked lambda and linq, making pivot tables was not the main thing I thought about. After helping a few people including one at the msdn (http://is.gd/oG8q) social site, I realized that I also needed to use the lambda in my own EMR project.

The problem was to have the lab data of a patient transformed into a pivot table and some of the data summarized by averaging or counting.

I had the data in the table in the following format.

I then had to write a linq expression to produce the following results.

The linq was as follows.

var labResults = from lab in CoreLabResults
                 where lab.Patient == 8
                 group lab by new { lab.Patient, lab.TestNo, lab.CollectedDate }
                     into labtests
                     select new
                     {
                         labtests.Key.Patient,
                         labtests.Key.TestNo,
                         labtests.Key.CollectedDate,
                         MCHC = labtests.Where(lab => lab.TestVar == "MCHC").FirstOrDefault().Result,
                         LYABS = labtests.Where(lab => lab.TestVar == "LYABS").FirstOrDefault().Result,
                         TotalTests = labtests.Count()
                     }

The lambda expression for the above was be as follows

var labResults = CoreLabResults
    .Where(lab => lab.Patient == 8)
    .GroupBy(lab =>
            new
            {
                Patient = lab.Patient,
                AccessionNo = lab.AccessionNo,
                CollectedDate = lab.CollectedDate
            }
            )
    .Select(labtests =>
            new
            {
                Patient = labtests.Key.Patient,
                AccessionNo = labtests.Key.TestNo,
                CollectedDate = labtests.Key.CollectedDate,
                MCHC = labtests.Where(lab => (lab.TestVar == "MCHC")).FirstOrDefault().Result,
                LYABS = labtests.Where(lab => (lab.TestVar == "LYABS")).FirstOrDefault().Result,
                TotalTests = labtests.Count()
            }
);

I still need to find out how I can replace the column names like “MCHC” by computed ones. I may probably need to use the expression trees to dynamically create lambda expressions that have variable number of columns. E.g. in financial statements - I would not want to hardcode column names of each financial period year/quarter.

Apologies – syntax highlighting using Live writer is hell.