none
Using LIKE with wildchar search in LINQ query RRS feed

  • Question

  • Hi everyone,

    I have an sql query:

    "SELECT [GL Transactions].Debit AS Amount
    FROM [GL Transactions]
    WHERE ((([GL Transactions].Debit) Like '*.???')) OR ((([GL Transactions].Debit) Like '*.????'))"

    Basically it searches records where the Debit has either 3 or 4 decimal places.

    I want to return all those records where that criteria is met using a linq vb to sql query.

    The below query does not return any records:

    Dim glTrans= (From glt dc.GL_Transactions
                                            Where glt.Debit Like "*.???" or glt.Debit Like "*.????" 
                                            Select glt)

    I'm using VB.NET 2017

    What Am I doing wrong?

    Thanks in advance.

    EG

    Monday, December 18, 2017 2:17 PM

All replies

  • Hi egstatus,

    >>What Am I doing wrong?

    Based on your description and related code, we could use Regex to achieve it. but database doesn't support funciton named Regex, we need to load all the data into memory, then filter them via Regex. the following code for your reference.

    Dim pattern As String = "^\d+(\.\d{3})?$"
    Dim pattern2 As String = "^\d+(\.\d{4})?$"
    Dim r As Regex = New Regex(pattern, RegexOptions.IgnoreCase)
    
    Dim r2 As Regex = New Regex(pattern2, RegexOptions.IgnoreCase)
    Using db As New EFDemoContext()
        Dim query = db.GL_Transactions.ToList().Where(Function(t) r.IsMatch(t.Debit) Or r2.IsMatch(t.Debit)).ToList()
    
    End Using

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 19, 2017 2:24 AM
    Moderator