时间戳

首页 > 解决方案 > 过滤Sql中的错误返回直接转换错误

vb.net - 过滤Sql中的错误返回直接转换错误

问题描述

您好我的功能是使用实体框架获取数据，然后基于附加参数进行附加过滤。

这是我的功能：

Public Shared Function GetSstCodeComboList(ByVal fCustomerID As Integer,
                                                   ByVal fEventType As String,
                                                   ByVal fSpringCode As String,
                                                   ByVal fActive As Boolean) As List(Of SstCodeComboList)

Dim sstList = New List(Of SstCodeComboList)

Dim de = New SchedulerEntitiesConn()

Dim sstQuery As IQueryable(Of SstCodeComboList) = Nothing

Try
    'CustomerID 0 is Generic.
    If fCustomerID > 0 Then
        'We have a specific Customer. Get all Customer Codes.
        sstQuery = (From c In de.vw_SSTCodes
                    Where c.sstCustomerFk = fCustomerID And c.sstID > 4
                    Order By c.sstDetail
                    Select New SstCodeComboList With {.sstID = c.sstID,
                                    .sstDetail = c.sstDetail,
                                    .sstProjectCode = c.sstProjectCode,
                                    .sstProjectName = c.sstProjectName,
                                    .sstTaskNo = c.sstTaskNo,
                                    .sstTaskName = c.sstTaskName,
                                    .sstCustomer = c.Customer,
                                    .sstRU = c.sstRU,
                                    .sstRedundant = c.sstRedundant})


        'Now filter the list down where necessary
        If Not IsNothing(sstQuery) Then

            If fActive Then
                'We only need active codes so filter down to where Redundant = False
                sstQuery = sstQuery.Where(Function(f) f.sstRedundant = False)
            End If

            'Do filtering on Spring Codes if setting is set to true and the param fSpringCode is not null or empty
            If Settings.Default.filterSpringCodes = True And Not String.IsNullOrEmpty(fSpringCode) Then
                'Filtering on Spring Codes
                'First check first char - if ! then we need all codes that are NOT spring codes
                'i.e. do not begin with the rest of fSpringCode value
                If Left(fSpringCode, 1) = "!" Then
                    Dim filter As String = fSpringCode.Replace("!", "")
                    sstQuery = sstQuery.Where(Function(f) Left(f.sstTaskNo, 3) <> filter) '#####THIS WORKS
                Else
                    'we have one or more spring codes
                    'create array from fSpringCode
                    Dim sCodeArray() As String = Split(fSpringCode, ",")
                    'find out how many codes we have (MAX is 3)
                    Dim sCodeCount = sCodeArray.Length
                    
                    '##### THE FILTERS BELOW DON'T WORK
                    'get error DirectCast(sstQuery, System.Data.Entity.Infrastructure.DbQuery(Of Scheduler.Models.SstCodeModel.SstCodeComboList)).Sql' threw an exception of type 'System.NotSupportedException'
                    Select Case (sCodeCount)
                        Case 1
                            'sstQuery = sstQuery.Where(Function(f) LEFT(f.sstTaskNo, 5) = sCodeArray(0))
                            'sstQuery = sstQuery.Where(Function(f) f.sstTaskNo.StartsWith(sCodeArray(0))                                        
                            'sstQuery = sstQuery.Where(Function(f) LEFT(f.sstTaskNo, 5) = sCodeArray(0).ToString)
                            sstQuery = sstQuery.Where(Function(f) f.sstTaskNo.StartsWith(sCodeArray(0).ToString))
                        Case 2
                            sstQuery = sstQuery.Where(Function(f) f.sstTaskNo.StartsWith(sCodeArray(0).ToString) Or f.sstTaskNo.StartsWith(sCodeArray(1).ToString))
                        Case 3
                            sstQuery = sstQuery.Where(Function(f) f.sstTaskNo.StartsWith(sCodeArray(0).ToString) Or f.sstTaskNo.StartsWith(sCodeArray(1).ToString) Or f.sstTaskNo.StartsWith(sCodeArray(2).ToString))
                    End Select

                        'If there were more than 3 spring codes then no filtering will be done.
                    
                End If

            End If
            'Transfere each SSTCode left in query to sstList.
            For Each s In sstQuery
                sstList.Add(New SstCodeComboList() With {.sstID = s.sstID,
                            .sstDetail = s.sstDetail,
                            .sstProjectCode = s.sstProjectCode,
                            .sstProjectName = s.sstProjectName,
                            .sstTaskNo = s.sstTaskNo,
                            .sstTaskName = s.sstTaskName,
                            .sstRU = IIf((s.sstRU = "U"), "Unrecoverable", IIf((s.sstRU = "RBAU"), "BAU UT", IIf((s.sstRU = "RPROJECT"), "Project", "-"))),
                            .sstClass = IIf((s.sstRU = "U"), "btn-danger", IIf((s.sstRU = "RBAU"), "btn-success", IIf((s.sstRU = "RPROJECT"), "btn-warning", "btn-info"))),
                            .sstCustomer = s.sstCustomer})
            Next

        End If

    End If
    'clear query
    sstQuery = Nothing

    'Now get all Generic customer sstcodes
    sstQuery = (From c In de.vw_SSTCodes
                Where c.sstCustomerFk = 0 And c.sstID > 4
                Order By c.sstDetail
                Select New SstCodeComboList With {.sstID = c.sstID,
                                    .sstDetail = c.sstDetail,
                                    .sstProjectCode = c.sstProjectCode,
                                    .sstProjectName = c.sstProjectName,
                                    .sstTaskNo = c.sstTaskNo,
                                    .sstTaskName = c.sstTaskName,
                                    .sstCustomer = c.Customer,
                                    .sstRU = c.sstRU,
                                    .sstRedundant = c.sstRedundant})


    'Now add all GENERIC customer sst code from query to list
    If Not IsNothing(sstQuery) Then

        For Each s In sstQuery
            sstList.Add(New SstCodeComboList() With {.sstID = s.sstID,
                        .sstDetail = s.sstDetail,
                        .sstProjectCode = s.sstProjectCode,
                        .sstProjectName = s.sstProjectName,
                        .sstTaskNo = s.sstTaskNo,
                        .sstTaskName = s.sstTaskName,
                        .sstRU = IIf((s.sstRU = "U"), "Unrecoverable", IIf((s.sstRU = "RBAU"), "BAU UT", IIf((s.sstRU = "RPROJECT"), "Project", "-"))),
                            .sstClass = IIf((s.sstRU = "U"), "btn-danger", IIf((s.sstRU = "RBAU"), "btn-success", IIf((s.sstRU = "btn-warning"), "Project", "btn-info"))),
                        .sstCustomer = s.sstCustomer})
        Next
    End If


Catch ex As Exception

    sstList.Add(New SstCodeComboList() With {.sstID = 0, .sstDetail = ex.Message})

    Helpers.Helpers.LogException("GetSstCodeComboList", "SstCodeComboList", ex)

Finally

    de.Dispose()

End Try


Return sstList

结束功能

fSpringCode 的典型值为：

!ABS 作为过滤器起作用。所有其他人都没有。我收到错误：DirectCast(sstQuery, System.Data.Entity.Infrastructure.DbQuery(Of Scheduler.Models.SstCodeModel.SstCodeComboList)).Sql' 抛出了“System.NotSupportedException”类型的异常

如果您查看 Select Case 代码...在案例 1 中，我尝试过的过滤器有各种变体...所有 4 个变体都给出相同的错误。

任何帮助表示赞赏..或考虑不同的方法

标签： vb.netentity-frameworklinq

解决方案

这个问题与数组 sCodeArray 有关。Linq 不喜欢数组索引，因此我不得不更改代码以便不使用数组。这解释了为什么 !ABS 值在我使用 Linq 中的字符串变量时起作用。

简而言之：

var filter = "ABS";
var filterArray = ["ABS03", "ABS07", "ABS13"];

sstQuery = sstQuery.Where(Function(f) Left(f.sstTaskNo, 3) <> filter); 'THIS WORKS
sstQuery = sstQuery.Where(Function(f) Left(f.sstTaskNo, 5) = filterArray(0)); 'THIS DOES NOT

推荐阅读