locked
preprocess query from two different tables RRS feed

  • Question

  • How can I do a vb linq query to get all EmpID's from Associate table except that are already in Approver table?

    Associate

    EmpID   int   <primary key>

    ApproverID <many2many relationship to Approver table>

    Approver

    EmpID   int   <primary key> <one2many relationship to Associate table>

    The goal is to fill AutoComplete box in AddNewApprover modal screen so the pick list only shows associates that are not already approvers.

    Thnx.

     

    Monday, November 17, 2014 5:10 PM

Answers

  • Thanks to Nicolás Lope de Barrios, his solution worked for me as well:

    Private Sub NotApprover_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Associate))
       Dim qryApprovers = (From a In Approvers).Execute
       Dim allApprovers As New List(Of Integer)()
       For Each apprvr As Approver In Approvers
           allApprovers.Add(apprvr.EmpID)
       Next
       query = From q In query Where Not allApprovers.Contains(q.EmpID)
    End Sub

    Thanks all for your help.


    jajjaar123

    • Marked as answer by jajjaar123 Tuesday, December 9, 2014 9:13 PM
    Tuesday, December 9, 2014 9:13 PM

All replies

  • You should be able to create a new query from Associates, click PreprocessQuery method and do something like this.

    query = query.Any(Function(a) (a.Approver.Any(Function(b) (b.EmpID = a.EmpID))))

    Tuesday, November 18, 2014 4:54 AM
  • Thanks for the reply, I tried the suggested but get an error msg; 'Any is not a member of LightSwitchApplication.Approver' for part of Sub a.Approver.Any 

    here's my Sub:

            Private Sub NotApprover_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Associate))
                query = query.Any(Function(a) (a.Approver.Any(Function(b) (b.EmpID = a.EmpID))))
            End Sub


    • Edited by jajjaar123 Tuesday, November 18, 2014 3:35 PM
    Tuesday, November 18, 2014 3:03 PM
  • If there is a relationship between associate and approver, there should be a navigation property available.  Maybe it's approvers instead of approver, something like that.  Intellisense should give you all the available properties of associate when you type "a." after "Function(a) (".  That navigation property should have an Any() available.  At least I think it should.  Also, once you get it to work you will want to modify my original query and use:

            Private Sub NotApprover_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Associate))
                query = query.Any(Function(a) (a.Approver.Any(Function(b) (Not b.EmpID = a.EmpID))))
            End Sub
    I added the "Not" to exclude any Id that is listed as an approver.  If Any() really isn't available on the navigation property perhaps you could try a where clause instead.   Hopefully this helps.

    Tuesday, November 18, 2014 4:43 PM
  • hi Simon,

    There is one to many relationship between Approver (EmpID  int  <primary key>) and Associate (ApproverID  int). And my data source is SQL Server 2008 database.

    Also still get the error; 'Any' is not a member of 'LightSwitchApplication.Approver' and not sure how to write the where clause so do not get similar error.

    Thnx.


    jajjaar123

    Friday, November 21, 2014 3:28 PM
  • I suspect there may be a design issue here.  You mentioned a many-to-many relationship.  I don't think that is supported in LightSwitch.  What you can do as a work around is set your tables up like this:

    Approver

    Id

    Name

    OtherProperties

    Associate

    Id

    Name

    OtherProperties

    Approver_Associate

    Id

    ApproverId

    AssociateId

    This way both Approver and Associate will have a 0..1 to many relationship with Approver_Associate.  This should allow you to query down through the navigation properties using Any().

    • Marked as answer by Angie Xu Monday, November 24, 2014 3:11 AM
    • Unmarked as answer by jajjaar123 Wednesday, November 26, 2014 2:12 AM
    Friday, November 21, 2014 9:35 PM
  • I mentioned one-to-many relationship not many-to-many. See screen shot of my database diag below.


    jajjaar123

    Wednesday, November 26, 2014 2:27 AM
  • Hi jajj.....

    This kind of query I like do write it in two steps. The result code is more readable:

     Private Sub Query1_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Associate))
    
                ' get the associates already in 'Approvers'
                Dim qryEmpInApprovers = Me.DataWorkspace.ApplicationData.Approvers.Select(Function(n) n.EmpID).Execute
    
                ' is there any associate in 'Approvers' ?
                If qryEmpInApprovers.Any Then
                    ' get the array of EmpID
                    Dim empSet = qryEmpInApprovers.ToArray
                    ' filter the Associates, excluding the existents in 'Approvers'
                    query = query.Where(Function(n) Not empSet.Contains(n.EmpID))
                Else
                    ' do nothing!
                End If
    
            End Sub

    I hope it helps you.

    Best regards,


    Ciro

    Friday, November 28, 2014 10:04 AM
  • Thanks to Nicolás Lope de Barrios, his solution worked for me as well:

    Private Sub NotApprover_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Associate))
       Dim qryApprovers = (From a In Approvers).Execute
       Dim allApprovers As New List(Of Integer)()
       For Each apprvr As Approver In Approvers
           allApprovers.Add(apprvr.EmpID)
       Next
       query = From q In query Where Not allApprovers.Contains(q.EmpID)
    End Sub

    Thanks all for your help.


    jajjaar123

    • Marked as answer by jajjaar123 Tuesday, December 9, 2014 9:13 PM
    Tuesday, December 9, 2014 9:13 PM