OrderBy Child Entity With EntityDataSource OrderBy Property

Question

I have an EntityDataSource which is to select all the user entities which have certain roles ordered by roleID. (I want to display the users gruoped by role, and therefore the order part is important).

the following code:

<asp:EntityDataSource ID="UserDataSource" runat="server" ConnectionString="name=MyEntities" 
    DefaultContainerName="MyEntities" EnableFlattening="False"
    EntitySetName="Users" Where="EXISTS(SELECT VALUE u FROM it.UserRoles AS u)" OrderBy="it.UserRoles.RoleId">
</asp:EntityDataSource>

produces the following error:

'RoleId' is not a member of 'Transient.collection[MyModel.UserRole(Nullable=True,DefaultValue=)]'. To extract a property of a collection element, use a subquery to iterate over the collection.

Adding Include="UserRoles" is of no help.

---

Answer 1

OK. I found the right syntax. here it is:

OrderBy="ANYELEMENT(SELECT VALUE SqlServer.MIN(r.RoleId) From it.UserRoles AS r)"

Answer 2

Issue is that a single user may have multiple UserRole. So you cannot use role id to order set of users - there is no single role id associated with a given user. That's the error message says - it.UserRoles is a collection and does not have role id property. That property exists on the element (UserRole) of the collection.

I am not sure about your ordering logic (considering user can multiple roles) but assuming that user can have one and only one role, you can use expression such as it.UserRoles.First().RoleId to get your ordering. Better way would be to edit your model to show correct multiplicity with User-UserRole relation.