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 Else edsContactList.OrderBy = "it.fldClientName" End If End Sub
I apologize ... I'm afraid I didn't explain this very well ... let me see if this clears things up:
The gridview is based on an EntityDataSource that returns a list of "Person" entities, such as:
Bill Smith
Jane Davis
Tom Miller
Using the "Includes PeopleRoles" clause in the EntityDataSource, I also have access to each person's associated PeopleRoles entities, which could be zero, one or more roles for each person:
Bill Smith ------> Role=Stable Contact [1 Role record]
Jane Smith -----> [no associated Role records]
Tom Miller ------> Role=Trainer, Role=Stable Contact [2 Role records]
I want to be able to set the gridview's EntityDataSource to return only Person entities who have an associated role entity of "Stable Contact". Given the data above, this would display Bill Smith and Tom Miller in the gridview.
I'm starting to wonder of this is possible with a single EntityDataSource, as I am trying to query a collection of related entities for each person. Maybe there's a workaround, possibly one EntityDataSource for Roles (which could be filtered by a Role type), and a second EntityDataSource for People, which is based on the first EntityDataSource.
If you have any ideas on how to accomplish this, please let me know. Thank you!
IT issues often require a personalized solution. With Ask the Experts™, submit your questions to our certified professionals and receive unlimited, customized solutions that work for you.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial