Filter query from many to many relationship, doesn't seem possible in Lightswitch?

Question

I'm trying to implement this many to many relationship query as exemplified here.

I have an employees table, a "zone" table, and an employee xref zone table. One employee can belong to many zones, and one zone can have many employees. Coming at this from a SQL point of view, this makes perfect sense, and I can left join, inner join, whatever-join these data in SSMS all day long. What I can't wrap my head around is how to attach the correct linq query to the correct screen control to be able to filter a list of employees based on a pick list of zone. WCF RIA is NOT a development direction we are able to pursue.

I attached the following to the ResponseZone table, with the parameter "Employee" of type "Integer"

      Private Sub QRYZone_PreprocessQuery(Employee As System.Nullable(Of Integer), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.ResponseZones))
        query = From q In query
                Where q.xref_EmployeeZonesCollection.Where(Function(u) u.ResponseZones.Id = Employee).Count() > 0
    End Sub

I then add the xref entity to the screen and bind it to "zone". The only thing it acccomplishes is populates a pick list of all combinations of employees and zones, and filters nothing. Clearly I'm doing something wrong here, but I'm pulling my hair out trying to figure out what!

I have also tried Filter in Many to Many Relationship, Many to Many queries, and Queries involving more than one entity with no success. Either I have something different in my schema that those examples won't work with, or I'm missing something really obvious.

Answer 1

Added a query to the employees table "QRYParameters";

Added "ZoneID" of integer as a parameter;

Added

            If ZoneID.HasValue Then
            query = query.Where(Function(x) x.xref_EmployeeZonesCollection.Any(Function(y) y.ResponseZones.Id = ZoneID))
        Else
            query = From q In query
                    Select q
        End If

In the "Search Employees" screen added the Zones entity (List of zones);

Bound "ZoneID" from the Employee Grid Qry Parameters to "ID" from the Zone list;

Added the response zones pick list to the screen.

And it works.

However, I'd love to be able to pick multiple zones, at it stands, the vb in the query pre processor returns all zones to work around that.