Using Menu Buttons to modify LINQ search terms

Question

In VBS 2015, I have created a form that uses a LINQ query to import a CSV file of names, locations, ages, ect., sorted using a unique record. The data is input into a Data Grid and then may be filtered and sorted by the selection of buttons from the MenuStrip. The procedure for the sorting and filtering using LINQ queries is very straightforward; the array may be sorted in ascending or descending order, and also by one of the fields. If the field matches the query, the entire record (five fields) is displayed in the data grid.

All is fine and dandy to this point, but this is my issue:

How do I apply two different criteria at the same time without writing an entire query for each combination of criteria?

For example, to make my issue clear, I can sort the array in descending order using my original query, but when I select the filter, the results are displayed in ascending order. Vice versa, if the results are already filtered using one of five field possibilities, how do I apply the sort buttons to the prefiltered results?

I came up with an extremely long and convoluted way of doing this by writing a separate query for each filter/sort combination. Every time a button is selected, the other menu is evaluated for it's checked value using an If block based on the mnuButton.Checked = True/False Boolean, and then the corresponding query is run and displayed.

Here is a sample from what I've been running as code executed when one of the filter buttons is pressed. This sub is supposed to check if one of the sort buttons is already selected, then, based on that, to run one of two queries that return the same filtered results, just in either ascending or descending order.

Private Sub mnuFilterEurope_Click(sender As Object, e As EventArgs) Handles mnuFilterEurope.Click

Dim memberinfo() As String = File.ReadAllLines("easterndiv_mission.txt")
Dim n = memberinfo.Count - 1
ReDim Missionaries(n) 'this is based on a structure defined outside this sub
Dim line As String
Dim data(5) As String
For i As Integer = 0 To n
  line = memberinfo(i)
  data = line.Split(","c)
  Missionaries(i).Last = data(0)
  Missionaries(i).First = data(1)
  Missionaries(i).State = data(2)
  Missionaries(i).Age = CInt(data(3))
  Missionaries(i).ServiceYears = CInt(data(4))
  Missionaries(i).Location = data(5) 'this is the field the 'filter' query will be evaluating.
Next
'if the 'ascending' button is selected, format the results in ascending order
If mnuSortAscending.Selected = True Then
  checkEurope() ' this references a sub procedure that selects the pressed button and deselects the other four in the menu.
  Dim populationQuery = From entry In Missionaries
                        Where entry.Location = "Europe"
                        Order By entry.Last
                        Select entry.Last, entry.First, entry.State, entry.Age, entry.ServiceYears, entry.Location
  If populationQuery.Count() > 0 Then 'check that results will be returned
    dgvOutput.DataSource = populationQuery.ToList
    dgvOutput.CurrentCell = Nothing
  Else
    MessageBox.Show("No results found")
    Exit Sub
  End If
Else
  'if the 'descending' button is selected, format the results in descending order
  checkEurope()
  Dim populationQuery = From entry In Missionaries
                        Where entry.Location = "Europe"
                        Order By entry.Last Descending
                        Select entry.Last, entry.First, entry.State, entry.Age, entry.ServiceYears, entry.Location
  If populationQuery.Count() > 0 Then
    dgvOutput.DataSource = populationQuery.ToList
    dgvOutput.CurrentCell = Nothing
  End If
End If
End Sub

This is extremely long, unwieldy, and time-consuming for just one button, and with ten different possibilities...? I'm sure there's a better way to write either a reusable query or to sort only the results displayed in the Data Grid...not what is drawn from the original file.

Since menu items used to sort a query result are extremely common, I'm sure there's a streamlined way to accomplish my end goal here.

Thanks in advance for any help, and I apologize in advance for any unclearness and for the n00bishness of this question...I've only been using VB for a few weeks.

Answer 1

Your goal is to have nothing inside the If mnuSortAscending.Selected branches that isn't specifically related to applying Order By entry.Last or Order By entry.Last Descending. This is a necessary goal to keep the code maintainable, like you said. So the task is to take each thing that is duplicated and move it to a single place, either before or after the If:

checkEurope() ' this references a sub procedure that selects the pressed button and deselects the other four in the menu.
Dim populationQuery = From entry In Missionaries
                      Where entry.Location = "Europe"

'if the 'ascending' button is selected, format the results in ascending order
If mnuSortAscending.Selected = True Then
   populationQuery = From entry In populationQuery
                     Order By entry.Last
Else
   populationQuery = From entry In populationQuery
                     Order By entry.Last Descending
End If

populationQuery = From entry In populationQuery
                  Select entry.Last, entry.First, entry.State, entry.Age, entry.ServiceYears, entry.Location

If populationQuery.Count() > 0 Then 'check that results will be returned
  dgvOutput.DataSource = populationQuery.ToList
  dgvOutput.CurrentCell = Nothing
Else
  MessageBox.Show("No results found")
  Exit Sub
End If

Does that help?

If you want to be even more concise, = True is a no-op (on things that are already boolean) so you can leave it out. Also, LINQ is just syntax sugar for regular VB method calls. From entry In populationQuery Order By entry.Last is identical to populationQuery.OrderBy(Function (entry) entry.Last).

If mnuSortAscending.Selected Then
   populationQuery = populationQuery.OrderBy(Function (entry) entry.Last)
Else
   populationQuery = populationQuery.OrderByDescending(Function (entry) entry.Last)
End If

Finally you can reduce duplication to an even further extreme by converting the If from its statement form to its expression form (the ternary operator), deduplicating the populationQuery =:

populationQuery = If(mnuSortAscending.Selected,
    populationQuery.OrderBy(Function (entry) entry.Last),
    populationQuery.OrderByDescending(Function (entry) entry.Last))