none
How do filter a linq query based on calculation that uses fields from a related table? RRS feed

  • Question

  •  

     

    Hi, I'm working on a wpf application (though this is a linq question).
    I have a table, tlkpProduct, that I am displaying in a datagrid (from WpfToolkip) called dgDataGrid. The data must be editable and I've implemented the code:

        Private mdb As MyDataContext
        Private WithEvents mobjProductView As BindingListCollectionView
    
        Private Sub loadData()
            Dim qry As IQueryable(Of tlkpProduct) = From Product In mdb.tlkpProducts _
                Order By Product.Model _
                Select Product
    
            Me.DataContext = qry
            mobjProductView = CType(CollectionViewSource.GetDefaultView(Me.DataContext), BindingListCollectionView)
            dgDataGrid.ItemsSource = mobjProductView
        End Sub
    This code works and I'm able to edit the data in the datagrid.

    I want to now apply a filter (there are many criteria fields) to the data and have implemented (e.g. user selects the product type):
        Private Sub applyCustomFilter()
            Dim qry As IQueryable(Of tlkpProduct) = From Product In mdb.tlkpProducts _
                Order By Product.Model _
                Select Product
    
            qry = qry.Where(Function(Product) Product.fkProductType = CInt(cboFilterFkProductType.SelectedValue))
    
            Me.DataContext = qry
            mobjProductView = CType(CollectionViewSource.GetDefaultView(Me.DataContext), BindingListCollectionView)
            dgDataGrid.ItemsSource = mobjProductView
        End Sub
    

    A second table, tlkpProductMarkup, contains the percentage markup. SalePrice = tlkpProduct.CostPrice * tlkpProductMarkup.ProductMarkupRate.
    I want to query the SalePrice based on user input (example is attempting to filter all records with a SalePrice greater than a user entered value).
    I attempted:
            qry = qry.Where(Function(Product) ((CDbl(Product.CostPrice) * _
                                (From ProductMarkup In mdb.tlkpProductMarkups _
                                      Where ProductMarkup.uidProductMarkup = Product.fkProductMarkup _
                                      Select ProductMarkup)(0).ProductMarkupRate)) _
                                >= CDbl(txtFilterStartPrice.Text))
    
    but am receiving error: "The query operator 'ElementAtOrDefault' is not implemented" on assigning to mobjProductView. I don't know what that means (though [guess!] could be as a result of tlkpProduct.fkProductMarkup being nullable; note that all records have a tlkpProduct.fkProductMarkup value at present.)
    Note that the "sub query" is looking up the ProductMarkupRate in the related table (there is only one related ProductMarkupRate per Product).

    How should I implement this filter? What am I getting wrong?

    This (MS Access) query does what I'm after (and demonstraits the relationship between the two tables):
    SELECT tlkpProduct.*
    FROM tlkpProduct LEFT JOIN tlkpProductMarkup ON tlkpProduct.fkProductMarkup = tlkpProductMarkup.uidProductMarkup WHERE tlkpProduct.CostPrice*tlkpProductMarkup.ProductMarkupRate>1000

    Any assistance will be greatly appreciated,
    Graeme.

    Thursday, November 5, 2009 12:27 AM

Answers

  • Hi William, thank for your input.

    Inserting the statement

    qry.AsEnumerable()
    didn't solve the issue.

    Linqpad looks like a great tool. I'll be buying it when as soon as it supports generation of VB.Net code.

    My endeavours with investigating ElementAtOrDefault changed the error message slightly from "not implemented" to "not supported."
    I do think it was Linqpad and the ElementAtOrDefault errors that lead me to a solution...

        Private Sub applyCustomFilter()
            Dim qry As IQueryable(Of tlkpProduct) = From Product In mdb.tlkpProducts _
                Order By Product.Model _
                Select Product
    ' This statement causes error ElementAtOrDefault not implemeted/supported (has Lamda expression)

    'qry = qry.Where(Function(Product) ((CDbl(Product.CostPrice) * _ ' (From ProductMarkup In mdb.tlkpProductMarkups _ ' Where ProductMarkup.uidProductMarkup = Product.fkProductMarkup _ ' Select ProductMarkup)(0).ProductMarkupRate)) _ ' >= CDbl(txtFilterStartPrice.Text))
    ' This statement causes no error (has no Lamda expression)
    qry = From Product In qry Join ProductMarkup _ In mdb.tlkpProductMarkups On Product.fkProductMarkup Equals ProductMarkup.uidProductMarkup _ Where CDbl(Product.CostPrice) * CDbl(ProductMarkup.ProductMarkupRate) >= CDbl(txtFilterStartPrice.Text) _ Select Product Me.DataContext = qry mobjProductView = CType(CollectionViewSource.GetDefaultView(Me.DataContext), BindingListCollectionView) dgDataGrid.ItemsSource = mobjProductView End Sub
    I know that this does not solve the ElementAtOrDefault errors (and is a full avoidence of that scenario.)

    Cheers,
    Graeme
    • Marked as answer by Graeme.R Thursday, November 5, 2009 11:05 PM
    Thursday, November 5, 2009 9:29 PM

All replies

  • Try the operation in Linqpad and look at what is generated. That might give you a clues.

    Also try moving the data from IQuerable format to IEnumerable by calling AsEnumerable before the filtering might do the trick.
    William Wegerson (www.OmegaCoder.Com)
    Thursday, November 5, 2009 2:45 PM
    Moderator
  • Hi William, thank for your input.

    Inserting the statement

    qry.AsEnumerable()
    didn't solve the issue.

    Linqpad looks like a great tool. I'll be buying it when as soon as it supports generation of VB.Net code.

    My endeavours with investigating ElementAtOrDefault changed the error message slightly from "not implemented" to "not supported."
    I do think it was Linqpad and the ElementAtOrDefault errors that lead me to a solution...

        Private Sub applyCustomFilter()
            Dim qry As IQueryable(Of tlkpProduct) = From Product In mdb.tlkpProducts _
                Order By Product.Model _
                Select Product
    ' This statement causes error ElementAtOrDefault not implemeted/supported (has Lamda expression)

    'qry = qry.Where(Function(Product) ((CDbl(Product.CostPrice) * _ ' (From ProductMarkup In mdb.tlkpProductMarkups _ ' Where ProductMarkup.uidProductMarkup = Product.fkProductMarkup _ ' Select ProductMarkup)(0).ProductMarkupRate)) _ ' >= CDbl(txtFilterStartPrice.Text))
    ' This statement causes no error (has no Lamda expression)
    qry = From Product In qry Join ProductMarkup _ In mdb.tlkpProductMarkups On Product.fkProductMarkup Equals ProductMarkup.uidProductMarkup _ Where CDbl(Product.CostPrice) * CDbl(ProductMarkup.ProductMarkupRate) >= CDbl(txtFilterStartPrice.Text) _ Select Product Me.DataContext = qry mobjProductView = CType(CollectionViewSource.GetDefaultView(Me.DataContext), BindingListCollectionView) dgDataGrid.ItemsSource = mobjProductView End Sub
    I know that this does not solve the ElementAtOrDefault errors (and is a full avoidence of that scenario.)

    Cheers,
    Graeme
    • Marked as answer by Graeme.R Thursday, November 5, 2009 11:05 PM
    Thursday, November 5, 2009 9:29 PM