Now that I know what Linq is.... Filtering datagrid with Linq

[jwcoleman87]

before, I was doing this:

    Private Sub btnApplyFilter_Click(sender As Object, e As EventArgs) Handles btnApplyFilter.Click
        Dim TechFilter As String = TechnicianFilter()
        Dim StatFilter As String = StatusFilter()
        Dim StartFilter As String = StartTimeFilter()
        Dim EndFilter As String = EndTimeFilter()
        Dim SerialFilter As String = SerialNumberFilter()
        Dim FilterExpression As String = String.Join(" AND ", {TechFilter, StatFilter, StartFilter, EndFilter, SerialFilter}.Where(Function(s) s <> String.Empty))
        dgvRepairs.DataSource = RepairBindingSource
        RepairBindingSource.Filter = FilterExpression
    End Sub

This worked really well, and still does. Problem is that I found a new way that doesn't involve generating sql commands all day. I can let Linq do that for me, like this:

            Dim TechnicianQuery = db.Repairs.Where(Function(t) clbTechnicians.CheckedItems.Contains(t.UserID))

That one line of code replaced all of this:

    Private Function TechnicianFilter() As String
        Dim LogicalOperator As String = " OR "
        Dim TechFilter As String = ""
        Dim FilterString As String = ""
        If clbTechnicians.CheckedItems.Count <= 1 Then
            LogicalOperator = ""
        End If
        Dim index As Integer = 0
        For Each itemChecked In clbTechnicians.CheckedItems

            If index = clbTechnicians.CheckedItems.Count - 1 Then
                LogicalOperator = ""
            End If
            TechFilter = String.Format("TechnicianID = '{0}' {1}", itemChecked.ToString(), LogicalOperator)
            FilterString += TechFilter
            index += 1
        Next
        If FilterString <> "" Then
            FilterString = "(" + FilterString + ")"
        End If
        Return FilterString
    End Function

And the same can be done for my StatusFilter, EndFilter, SerialFilter, and so on and so forth. That's awesome, really awesome. The problem I'm having is that I'm not sure how to handle combining variables like TechnicianQuery and StatusQuery and EndQuery into one big FilterQuery, which I can rebind to my data grid on the click of a button. I'm going to do some reading throughout tomorrow, but any tips would be greatly appreciated.

Perhaps a join is in line?

 

[jwcoleman87]

I think I'm off to a good start:

            Dim TechnicianQuery = db.Repairs.Where(Function(t) clbTechnicians.CheckedItems.Contains(t.UserID))
            Dim StatusQuery = db.Repairs.Where(Function(t) clbStatus.CheckedItems.Contains(t.Status))
            Dim FilterQuery = db.Repairs.Union(TechnicianQuery, StatusQuery)
            dgvRepairs.DataSource = FilterQuery

Apparently iqueryable doesn't work as a datasource, that stinks.

I also cannot union more than one query, with that function anyways, or I'm just not seeing it.

Need fresh eyes tomorrow for all this.

 

[JohnH]

A few comments:

Apparently iqueryable doesn't work as a datasource, that stinks.

IQueryable is fine as DGV DataSource, don't know why you say otherwise.

I also cannot union more than one query, with that function anyways, or I'm just not seeing it.

Add more Unions:

Dim x = A.Union(B).Union(C).Union(D)

The code you posted can also be written with expression:

dgvRepairs.DataSource = From row In db.Repairs Where clbTechnicians.CheckedItems.Contains(row.UserID) Or clbStatus.CheckedItems.Contains(row.Status)

(expression is still IQueryable)

 

[jwcoleman87]

It's throwing this error when I try to set the datasource as an IQueryable

An unhandled exception of type 'System.NotSupportedException' occurred in System.Data.Linq.dll

Additional information: Method 'Boolean Contains(System.Object)' has no supported translation to SQL.

Here is the code:

    Private Sub btnApplyFilter_Click(sender As Object, e As EventArgs) Handles btnApplyFilter.Click
        Using db As New ProductionDataModelDataContext
            Dim TechnicianQuery = db.Repairs.Where(Function(t) clbTechnicians.CheckedItems.Contains(t.TechnicianID))
            Dim StatusQuery = db.Repairs.Where(Function(t) clbStatus.CheckedItems.Contains(t.Status))
            Dim FilterQuery = db.Repairs.Union(TechnicianQuery).Union(StatusQuery)
            dgvRepairs.DataSource = FilterQuery
        End Using

It throws that runtime error when I try to call this function. I've done some searching and I keep coming up dry.

 

[jwcoleman87]

I think I see what the problem is,

[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]clbTechnicians.CheckedItems.Contains(t.TechnicianID)
[/SIZE][/FONT][/SIZE][/FONT]

This produces a boolean, and this syntax is not playing nice with sql, visual studio doesn't know what to do with it anyways.

 

[jwcoleman87]

Dim TechnicianQuery = db.Repairs.Where(Function(t) t.TechnicianID.Any(Of String, clbTechnicians.CheckedItems.Contains(t.TechnicianID)))

Although this looks like it could work, it does not allow me to use t in the parameter of checkeditems.contains

 

[jwcoleman87]

Found something that works, althought I do need some help understanding why it works:

            For Each checkedItem As String In clbTechnicians.CheckedItems
                checkedtechlist.Add(checkedItem)
            Next
            Dim TechnicianQuery = From t In db.Repairs _
                                  Where checkedtechlist.Contains(t.TechnicianID)
                                  Select t

            dgvRepairs.DataSource = TechnicianQuery

 

[jwcoleman87]

This works for me:

            Dim FilteredQuery = From t In db.Repairs _
                                Where checkedtechlist.Contains(t.TechnicianID) _
                                And checkedstatuslist.Contains(t.Status) _
                                And t.EndTime > dtpEndTime1.Value.Date _
                                And t.EndTime < dtpEndTime1.Value.Date.AddDays(nmMultipleStartDays.Value) _
                                And t.EndTime.Value.TimeOfDay > dtpEndHour.Value.TimeOfDay _
                                And t.EndTime.Value.TimeOfDay < dtpEndHour.Value.AddHours(nmEndHour2.Value).TimeOfDay _
                                Select t
            dgvRepairs.DataSource = FilteredQuery

The problem is it will ALWAYS filter for ALL of the possible filters. I need to redesign my form so that the parameters change when different selections are made. For instance a select all button for technicians and statuses. And a checkbox for an entire day instead of hourly.

This code has replaced:

    Private Function TechnicianFilter() As String
        Dim LogicalOperator As String = " OR "
        Dim TechFilter As String = ""
        Dim FilterString As String = ""
        If clbTechnicians.CheckedItems.Count <= 1 Then
            LogicalOperator = ""
        End If
        Dim index As Integer = 0
        For Each itemChecked In clbTechnicians.CheckedItems

            If index = clbTechnicians.CheckedItems.Count - 1 Then
                LogicalOperator = ""
            End If
            TechFilter = String.Format("TechnicianID = '{0}' {1}", itemChecked.ToString(), LogicalOperator)
            FilterString += TechFilter
            index += 1
        Next
        If FilterString <> "" Then
            FilterString = "(" + FilterString + ")"
        End If
        Return FilterString
    End Function
    Private Function StatusFilter() As String
        Dim LogicalOperator As String = " OR "
        Dim StatFilter As String = ""
        Dim FilterString As String = ""
        If clbStatus.CheckedItems.Count <= 1 Then
            LogicalOperator = ""
        End If
        Dim index As Integer = 0
        For Each itemChecked In clbStatus.CheckedItems

            If index = clbStatus.CheckedItems.Count - 1 Then
                LogicalOperator = ""
            End If
            StatFilter = String.Format("Status = '{0}' {1}", itemChecked.ToString(), LogicalOperator)
            FilterString += StatFilter
            index += 1
        Next
        If FilterString <> "" Then
            FilterString = "(" + FilterString + ")"
        End If
        Return FilterString
    End Function
    Private Function StartTimeFilter() As String
        Dim FilterString As String = ""
        Dim DateFilter As String = ""
        Dim HourFilter As String = ""
        If dtpStartTime1.Checked = True Then
            DateFilter = String.Format("StartTime > #{0}# AND StartTime < #{1}#", dtpStartTime1.Value.Date(),
                                            dtpStartTime1.Value.Date().AddDays(nmMultipleStartDays.Value))
        End If
        If dtpStartHour.Checked = True Then
            HourFilter = String.Format("StartTime > #{0}# AND StartTime < #{1}#", dtpStartHour.Value.TimeOfDay,
                                          dtpStartHour.Value.AddHours(nmStartHour2.Value).TimeOfDay)
        End If
        FilterString = String.Join(" AND ", {DateFilter, HourFilter}.Where(Function(s) s <> String.Empty))
        Return FilterString
    End Function
    Private Function EndTimeFilter() As String
        Dim FilterString As String = ""
        Dim DateFilter As String = ""
        Dim HourFilter As String = ""
        If dtpEndTime1.Checked = True Then
            DateFilter = String.Format("EndTime > #{0}# AND EndTime < #{1}#", dtpEndTime1.Value.Date(),
                                          dtpEndTime1.Value.Date().AddDays(nmMultipleStartDays.Value))
        End If
        If dtpEndHour.Checked = True Then
            HourFilter = String.Format("EndTime > #{0}# AND EndTime < #{1}#", dtpEndHour.Value.TimeOfDay,
                                          dtpEndHour.Value.AddHours(nmEndHour2.Value).TimeOfDay)
        End If
        FilterString = String.Join(" AND ", {DateFilter, HourFilter}.Where(Function(s) s <> String.Empty))
        Return FilterString
    End Function
    Private Function SerialNumberFilter() As String
        Dim LogicalOperator As String = " OR "
        Dim SerialFilter As String = ""
        Dim FilterString As String = ""
        If listSerialFilter.Items.Count <= 1 Then
            LogicalOperator = ""
        End If
        Dim index As Integer = 0
        For Each Item In listSerialFilter.Items

            If index = listSerialFilter.Items.Count - 1 Then
                LogicalOperator = ""
            End If
            SerialFilter = String.Format("SerialNumber = '{0}' {1}", Item.ToString(), LogicalOperator)
            FilterString += SerialFilter
            index += 1
        Next
        If FilterString <> "" Then
            FilterString = "(" + FilterString + ")"
        End If
        Return FilterString
    End Function

How nifty is that!

 

[JohnH]

Found something that works, althought I do need some help understanding why it works:

            For Each checkedItem As String In clbTechnicians.CheckedItems
                checkedtechlist.Add(checkedItem)
            Next
            Dim TechnicianQuery = From t In db.Repairs _
                                  Where checkedtechlist.Contains(t.TechnicianID)
                                  Select t

            dgvRepairs.DataSource = TechnicianQuery

Also not sure why that throws, but this would also work, specify the item type in clbTechnicians (f.ex Integer) :

... Where Me.clbTechnicians.CheckedItems.Cast(Of Integer).Contains(...)