WHERE IN clause using VB.NET in Entity Framework

Question

I have two CheckboxLists. Based on the items checked, the checked values are concatenated into a comma separated string and passed to my entity framework method. The result would be a List(Of Entity).

I want to convert

SELECT * 
FROM dbo.Findings /*Below criteria is added only if any of the values are checked*/
WHERE FindingCategoryId IN (<Checked Values>) 
  AND FindingSeverityId IN (<CheckBoxValues>)

I am unable to find an equivalent for IN for VB.Net in EF.

I looked at the C# post here and came up with the below code. I get an error as

Unable to create a constant value of type 'System.Object'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

How can I use the IN clause in Vb.Net? Any help is appreciated.

My code:

Public Function Findings(ByVal findingSeverity As String, ByVal findingCategory As String) As List(Of Finding)
    Dim aTypeLoanId As Integer = If(Not IsNothing(AuditTypeLoanId), AuditTypeLoanId, -1)
    Dim findingTargetId = CInt(FindingTarget.LoanHeader)
    Using e As New LQCEntities()
        Dim result = (From f In e.FindingEntities _
                From hmd In e.HeaderMetaDataEntities.Where(Function(h) h.MetaDataId = f.TargetId).DefaultIfEmpty() _
                From cl In e.CheckListEntities.Where(Function(c) c.CheckListId = f.TargetId).DefaultIfEmpty() _
                Where f.AuditTypeLoanId = aTypeLoanId _
                Select New Finding With _
                       {
                            .FindingId = f.FindingId, _
                            .FindingCategoryId = f.FindingCategoryId, _
                            .FindingSeverityId = f.FindingSeverityId, _
                            .EnteredBy = f.ADUser.DisplayName, _
                            .EnteredDate = f.EnteredDate _
                        })
        Dim fsArray() = Nothing
        Dim fcArray() = Nothing
        If (Not String.IsNullOrEmpty(findingSeverity)) Then
            Dim fs = findingSeverity.Split(",")
            For i As Integer = 0 To fs.Count - 1
                Dim j As Integer = 0
                If (Integer.TryParse(fs(i), j)) Then
                    ReDim Preserve fsArray(i)
                    fsArray(i) = j
                End If
            Next
            If (fsArray.Count > 0) Then
                result = result.Where(Function(i) fsArray.Contains(i.FindingSeverityId))
            End If
        End If
        If (Not String.IsNullOrEmpty(findingCategory)) Then
            Dim fc = findingCategory.Split(",")
            For i As Integer = 0 To fc.Count - 1
                Dim j As Integer = 0
                If (Integer.TryParse(fc(i), j)) Then
                    ReDim Preserve fcArray(i)
                    fcArray(i) = j
                End If
            Next
            If (fcArray.Count > 0) Then
                result = result.Where(Function(i) fcArray.Contains(i.FindingCategoryId))
            End If
        End If
        Return result.ToList()
    End Using
End Function

user007 · Accepted Answer · 2013-07-17 21:31:46Z

I changed the fsArray and fcArray to a List(Of Integer) and it worked. Code is as below:

Public Function Findings(ByVal findingSeverity As String, ByVal findingCategory As String) As List(Of Finding)
        Dim aTypeLoanId As Integer = If(Not IsNothing(AuditTypeLoanId), AuditTypeLoanId, -1)
        Dim findingTargetId = CInt(FindingTarget.LoanHeader)
        Using e As New LQCEntities()
            Dim result = (From f In e.FindingEntities _
                    From hmd In e.HeaderMetaDataEntities.Where(Function(h) h.MetaDataId = f.TargetId).DefaultIfEmpty() _
                    From cl In e.CheckListEntities.Where(Function(c) c.CheckListId = f.TargetId).DefaultIfEmpty() _
                    Where f.AuditTypeLoanId = aTypeLoanId _
                    Select New Finding With _
                           {
                                .FindingId = f.FindingId, _
                                .AuditTypeLoanId = f.AuditTypeLoanId, _
                                .FindingCategoryId = f.FindingCategoryId, _
                                .CategoryDescription = f.FindingCategory.CategoryDescription, _
                                .FindingSeverityId = f.FindingSeverityId, _
                                .SeverityDescription = f.FindingSeverity.SeverityDescription, _
                                .TargetId = f.TargetId, _
                                .UserResponse = f.UserResponse, _
                                .Field = If(f.FindingTargetId = findingTargetId, hmd.ColumnDescription, cl.CheckListDesc), _
                                .OldValue = f.OldValue, _
                                .NewValue = f.NewValue, _
                                .Comments = f.Comments, _
                                .EnteredBy = f.ADUser.DisplayName, _
                                .EnteredDate = f.EnteredDate _
                            })


            If (Not String.IsNullOrEmpty(findingSeverity)) Then
                Dim fsList As New List(Of Integer)
                Dim fs = findingSeverity.Split(",")
                For i As Integer = 0 To fs.Count - 1
                    Dim j As Integer = 0
                    If (Integer.TryParse(fs(i), j)) Then
                        fsList.Add(j)
                    End If
                Next
                If (fsList.Count > 0) Then
                    result = result.Where(Function(i) fsList.Contains(i.FindingSeverityId))
                End If
            End If
            If (Not String.IsNullOrEmpty(findingCategory)) Then
                Dim fc = findingCategory.Split(",")
                Dim fcList As New List(Of Integer)
                For i As Integer = 0 To fc.Count - 1
                    Dim j As Integer = 0
                    If (Integer.TryParse(fc(i), j)) Then
                        fcList.Add(j)
                    End If
                Next
                If (fcList.Count > 0) Then
                    result = result.Where(Function(i) fcList.Contains(i.FindingCategoryId))
                End If
            End If
            Return result.ToList()
        End Using
    End Function

Collectives™ on Stack Overflow

WHERE IN clause using VB.NET in Entity Framework

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related