none
Use 'Where(Function(t)' on calculated field RRS feed

  • Question

  • Hello

    I hope this makes sense ...

     

    I have the following query that creates a calculated field (cycFreq) based on two database fields:

    Code Snippet

    Dim lTreat = From t In ldb.tblTreatments _

    Join p In ldb.tblPeoples On t.PerformedBy Equals p.id _

    Let cycFreq = IIf(t.Cycle > 1, t.Cycle & " ", "") & IIf(t.Frequency = "M", "Monthly", IIf(t.Frequency = "W", "Weekly", "Adhoc")) _

    Where t.RiskID = id _

    Select New With {t.id, _

         t.Action, _

         t.ActionType, _

    t.DueDate, _

    p.Title, _

    cycFreq}

     

    I use this model extensively and it works fine in Grids and reports. The resulting value is correct but looking at the values returned in lTreat when running in debug, the cycFreq field shows as "3 Monthly: {String}" whereas the other fields just show the value.

     

    I am trying to apply a filter before the query results are used:

    Code Snippet

    If freqFilter <> "" Then

    lTreat = lTreat.Where(Function(t) t.cycFreq = freqFilter)

    End If

     

     

    The result is always an empty object. I have tried 't.cycFreq.toString' and I have tried putting the IIF commands in the Function but the result is always the same.

     

    Can anyone help??

    Monday, August 25, 2008 12:20 PM

Answers

  • The function IIf is late bound, which means it does not work well with LINQ to SQL.

     

    Use the newer replacement If() function instead. For example:

     

    Code Snippet

    Dim dm As New DB.NorthwindDataContext

    dm.Log = Console.Out

    'create query with calc value

    Dim q = From o In dm.Order_Details _

    Let text = If(o.Discount > 0, "Discount: " & o.Discount.ToString, "No discount") _

    Select o.OrderID, o.Discount, text

    'filter out some orders

    q = From o In q Where Not o.text.StartsWith("N") Select o

    For Each item In q

    Console.WriteLine("{0}: {1:c} {2}", item.OrderID, item.Discount, item.text)

    Next

    Console.WriteLine("Press any key to continue")

    Console.ReadKey()

     

     

     

    If you try replacing  Let text = If(o.Discount > 0, "Discount: " & o.Discount.ToString, "No discount"with IIf(...) it won't work.

     

    I was quite impressed with the generated SQL from this

    Tuesday, August 26, 2008 5:32 PM
    Answerer