none
Linq Filter for years RRS feed

  • Question

  • This code works but is not efficient, what I would like is for LINQ to return one record for each available year in the database to add to the dropdown list without having to loop thru all the records as in the code below

     

    'Fill Bill dropdown, filter with available years


            'add current year to list
            Dim lastDate As String = Now.Year
            ddlBillDateRange.Items.Clear()
            ddlBillDateRange.Items.Add(New ListItem(Now.Year, Now.Year))

            'get invoice list

            Dim db As New DataVendorDataContext
            Dim bill = (From i In db.APentries _
                        Where i.OwnerID = hfOwnerID.Value _
                        And i.Disabled = False _
                        And i.ActiveStatus = True _
                        And i.VendorID = ListBox1.SelectedValue _
                        Select i.InvoiceDate).Distinct.OrderByDescending(Function(InvoiceDate) InvoiceDate)

           
            'add only disticnt years to list
            For Each n In bill
                If Not n Is Nothing Then
                    If n.Value.Year <> lastDate Then
                        ddlBillDateRange.Items.Add(New ListItem(n.Value.Year, n.Value.Year))
                        lastDate = n.Value.Year
                    End If
                End If
            Next

    'Result example in dropdown would be

    '2011

    '2010

    '2008

    '2007

    Sunday, December 11, 2011 5:25 PM

Answers

  • Hi John;

    Try your query as follow:

    Dim bill = (From i In db.APentries _
                Where i.OwnerID = hfOwnerID.Value _
                And i.Disabled = False _
                And i.ActiveStatus = True _
                And i.VendorID = ListBox1.SelectedValue _
                And Not (i.InvoiceDate = Nothing)
                Select i.InvoiceDate.Value.Year).Distinct().OrderByDescending(Function(InvoiceDate) InvoiceDate)
    
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, December 11, 2011 6:40 PM
  • It worked when  I removed this line, And Not (i.InvoiceDate = Nothing), thanks

    Dim bill = (From i In db.APentries _
                Where i.OwnerID = hfOwnerID.Value _
                And i.Disabled = False _
                And i.ActiveStatus = True _
                And i.VendorID = ListBox1.SelectedValue _          
                Select i.InvoiceDate.Value.Year).Distinct().OrderByDescending(Function(InvoiceDate) InvoiceDate)
    • Marked as answer by John Canopy Monday, December 12, 2011 4:08 PM
    Sunday, December 11, 2011 7:09 PM

All replies

  • This code works but is not efficient, what I would like is for LINQ to return one record for each available year in the database to add to the dropdown list without having to loop thru all the records as in the code below

     

    'Fill Bill dropdown filter with available years


            'add current year to list
            Dim lastDate As String = Now.Year
            ddlBillDateRange.Items.Clear()
            ddlBillDateRange.Items.Add(New ListItem(Now.Year, Now.Year))

            'get invoice list

            Dim db As New DataVendorDataContext
            Dim bill = (From i In db.APentries _
                        Where i.OwnerID = hfOwnerID.Value _
                        And i.Disabled = False _
                        And i.ActiveStatus = True _
                        And i.VendorID = ListBox1.SelectedValue _
                        Select i.InvoiceDate).Distinct.OrderByDescending(Function(InvoiceDate) InvoiceDate)

           
            'add only disticnt years to list
            For Each n In bill
                If Not n Is Nothing Then
                    If n.Value.Year <> lastDate Then
                        ddlBillDateRange.Items.Add(New ListItem(n.Value.Year, n.Value.Year))
                        lastDate = n.Value.Year
                    End If
                End If
            Next

    • Merged by Lie You Monday, December 12, 2011 6:14 AM Merge them to keep into the same topic for good discussion
    Sunday, December 11, 2011 12:35 AM
  • Hi John;

    Try your query as follow:

    Dim bill = (From i In db.APentries _
                Where i.OwnerID = hfOwnerID.Value _
                And i.Disabled = False _
                And i.ActiveStatus = True _
                And i.VendorID = ListBox1.SelectedValue _
                And Not (i.InvoiceDate = Nothing)
                Select i.InvoiceDate.Value.Year).Distinct().OrderByDescending(Function(InvoiceDate) InvoiceDate)
    
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, December 11, 2011 6:40 PM
  • It worked when  I removed this line, And Not (i.InvoiceDate = Nothing), thanks

    Dim bill = (From i In db.APentries _
                Where i.OwnerID = hfOwnerID.Value _
                And i.Disabled = False _
                And i.ActiveStatus = True _
                And i.VendorID = ListBox1.SelectedValue _          
                Select i.InvoiceDate.Value.Year).Distinct().OrderByDescending(Function(InvoiceDate) InvoiceDate)
    • Marked as answer by John Canopy Monday, December 12, 2011 4:08 PM
    Sunday, December 11, 2011 7:09 PM
  • I just changed And Not (i.InvoiceDate = Nothing) = sign to Is Nothing and that worked too.
    Sunday, December 11, 2011 7:15 PM
  •  

    Very good. Please make sure to mark the solution "Mark As Answer" to complete the question.

    Thank you.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, December 11, 2011 7:21 PM
  • Hi John;

    You mark the wrong one as the solution, did you not.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, December 12, 2011 4:17 PM