CODEWITHSUNDEEP
vb.netlinq
Jimsan
Jimsan

Reputation: 305

Grouping by multiple columns

I am not sure if the title is misleading but I wasn't sure how to summarise this one.

I have a table in an SQL DB where a record exists as below:

enter image description here

I would like to display the measurement values of this item in a gridview as below:

enter image description here

I thought about selecting the target values to a list (and the same for the actual values) as below:

Dim cdc As New InternalCalibrationDataContext
Dim allTargetvalues = (From i In cdc.int_calibration_records
                       Where i.calibration_no = Request.QueryString(0) And
                       i.calibration_date = Request.QueryString(1)
                       Select i.measure1_target, i.measure2_target, i.measure3_target).ToList()

Then joining the lists together in some way although I am unsure of how I could join the lists or even if this is the correct approach to be taking?

Upvotes: 0

Views: 56

Answers (1)

Douglas Barbin
Douglas Barbin

Reputation: 3615

Well, let me first say that measure1_target, measure2_target, etc. is almost always indicative of bad database design. These should probably be in another table as the "many" end of a 1-to-many relationship with the table you posted. So to answer one of your questions: No, this is not the correct approach to be taking.

With the structure of your table in its current state, your best option is probably something like this:

Dim cdc As New InternalCalibrationDataContext

Dim allTargetValues As New List(Of Whatever)
For Each targetValue In (From i In cdc.int_calibration_records
                         Where i.calibration_no = Request.QueryString(0) AndAlso
                               i.calibration_date = Request.QueryString(1)
                         Select i)

    allTargetValues.Add(New Whatever With {.MeasureNumber = 1,
                                           .Target = targetValue.measure1_target,
                                           .Actual = targetValue.measure1_actual })


    allTargetValues.Add(New Whatever With {.MeasureNumber = 2,
                                           .Target = targetValue.measure2_target,
                                           .Actual = targetValue.measure2_actual })

    allTargetValues.Add(New Whatever With {.MeasureNumber = 3,
                                           .Target = targetValue.measure3_target,
                                           .Actual = targetValue.measure3_actual })

Next

The Whatever class would look like this: 

Public Class Whatever
    Public Property MeasureNumber As Integer
    Public Property Target As Integer
    Public Property Actual As Integer
End Class

Upvotes: 1

Related Questions