2

I have 3 simple tables

tblQual (ID, QualName)

tblPerson (ID, PersonName)

tblPersonQual (ID, PersonID, QualID, ExpiryDate)

I would like to display a matrix with PersonName down the left, QualName at the top and ExpiryDate in the middle. How do I go about doing this?

I've tried this but no joy.

var QualMatrix = from c in db.tblPersonQual
    join q in db.tblQual on c.QualID equals q.ID
    join p in db.tblPerson on c.PersonID equals p.ID
                         group c by c.ID into g
                         select new
                         {
                             rowKey = g.Key,
                             rowData = g.Select(c => new { Qual = q.QualName, Expiry = c.Expiry })
                         };

In terms of output view something similar to this

enter image description here

SliderUK
  • 167
  • 14
  • LINQ Pivot query, this might be helpful for you https://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq – N Subedi Dec 27 '19 at 16:03

1 Answers1

0

As my understanding, you want to display a matrix with QualName, ExpiryDate and QualName. We can use the same query by just modifying group by clause.

var QualMatrix = from c in db.tblPersonQual
                 join q in db.tblQual on c.QualID equals q.ID
                 join p in db.tblPerson on c.PersonID equals p.ID
                 group new { q.QualName,p.PersonName} by new { c.ID,c.ExpiryDate } into g
                 select new
                 {
                       QualName=g.Select(e=>e.QualName).FirstOrDefault(),
                       ExpirtyDate=g.Key.Expiry,
                       PersonName=g.Select(e=>e.PersonName).FirstOrDefault(),
                 };

Hopefully, This will fulfil your requirement.

habib
  • 2,366
  • 5
  • 25
  • 41
  • Hi thanks for replying, so how would I display this data output in a matrix on view (razor)? So having each person listed on the left hand side, all the quals listed across the top and expiry date as the value in the middle? – SliderUK Dec 28 '19 at 11:10
  • Can you share your matrix mockup design so that I understand well? – habib Dec 28 '19 at 14:17