Avatar of JMS1965
JMS1965Flag for United States of America

asked on 

ASP.NET and LINQ: How to Filter EntityDataSource Based on Associated Records / subquery?

I am building my first web application, using ASP.NET 4 with VB. I'm also using LINQ to Entities.

The page I'm working on now is a listing of Contacts, a gridview that uses an EntityDataSource based on the "Person" entity.  This is a read-only list; no editing, adding or deleting. I've added some drop-down lists above the gridview to allow the user to filter the list, and am constructing the WHERE clause for the EntityDataSource in the code behind. So far, the user can filter the list based on Active/Inactive status (Boolean), Horse Owner status (Boolean) and/or on the first letter of the Contact Name. These filters are working properly.

My question involves the last filter I'd like to add. Each contact record can have associated records in the PeopleRoles table … for example, a contact could have no PeopleRoles records, or could have one for RoleType "Stable", or one for "Trainer", or could have both a "Stable" and a "Trainer" record in the PeopleRoles table.

I've added a drop-down list for this in the filter area; the items in the drop-down list are populated dynamically from the user's list of possible PeopleRoles.  But, I don't know how to wire this so that the selection of a specific role from that list re-filters the EntityDataSource to include only Contacts who have that associated role.

I have set the "Include" property of the EntityDataSource to include the necessary Navigation item "PeopleRoles", but this of course returns a collection of records for each contact. Is there a way to construct the Where property to return only Contacts who have a PeopleRoles record of the selected type? I'm thinking it might require a subquery or nested query, but am not sure where to start.

Below is the code-behind function that is called whenever any of the filtering drop-down lists have a new item selected. All are working except the "filter on roles" section … you'll see my initial attempt there on line 16.  In testing this generates the error: "'RoleID' is not a member of 'Transient.collection[edmIFAcore.PeopleRole(Nullable=True,DefaultValue=)]'. To extract a property of a collection element, use a subquery to iterate over the collection."

    Private Sub FilterGridView()
        Dim strWhere As String = "it.AcctID=" & Profile.iFAacctID

        'filter on horse owners
        Select Case ddlOwners.SelectedItem.Text
            Case "[All]"

            Case "Horse Owners Only"
                strWhere = strWhere & " AND it.HorseOwner=True"
            Case "Non-Horse Owners Only"
                strWhere = strWhere & " AND it.HorseOwner=False"
        End Select

        'filter on roles
        If ddlRoles.SelectedItem.Text <> "[All]" Then
            strWhere = strWhere & " AND it.PeopleRoles.RoleID=" & ddlRoles.SelectedValue
        End If

        'filter on first letter
        If ddlStartsWith.SelectedItem.Text <> "[All]" Then
            strWhere = strWhere & " AND it.fldClientName LIKE '" & ddlStartsWith.SelectedItem.Text & "%'"
        End If

        'filter on active status
        Select Case ddlActives.SelectedItem.Text
            Case "[All]"

            Case "Actives Only"
                strWhere = strWhere & " AND it.Inactive=False"
            Case "Inactives Only"
                strWhere = strWhere & " AND it.Inactive=True"
        End Select

        'apply filter and sorting to the EntityDataSource
        edsContactList.Where = strWhere
        If Len(edsContactList.OrderBy) > 0 Then
            edsContactList.OrderBy = edsContactList.OrderBy
            edsContactList.OrderBy = "it.fldClientName"
        End If

    End Sub

Open in new window

Thanks very much for any direction you can provide!

Avatar of undefined
Last Comment
Avatar of dj_alik

 to replace: ddlRoles.SelectedValue
Avatar of jagssidurala
Flag of India image

The roles binding should be like below, check your role binding with follwoing code.

ddlRoles.DataSource = dtRoles or any datasource;
ddlRoles.DataBind() ;
ddlRoles.DataValueFiled = "RoleId";
ddlRoles.DataTextFiled = "RoleName";

or also make sure that role binding should be with in the post back in page load.
Avatar of JMS1965
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of JMS1965
Flag of United States of America image


Need to try a different approach.

The successor to Active Server Pages, ASP.NET websites utilize the .NET framework to produce dynamic, data and content-driven web applications and services. ASP.NET code can be written using any .NET supported language. As of 2009, ASP.NET can also apply the Model-View-Controller (MVC) pattern to web applications

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo