locked
EntityDataSource and Where property not working for Many to Many relations RRS feed

  • Question

  • User1432255915 posted

    Hi,

    I have this many to many relation in my database:

    Roles------->RolePermission<-----Permission (Only RoleId and PermissionId in the RolePermission table and they are set as primary key)

    EF Schema looses the mapping table

    Role-------Permission

    On my custom List.aspx page I have a Hyper link and I pass the RoleId as a query string parameter to another page:

    <asp:TemplateField>
     <ItemTemplate>
        <asp:HyperLink ID="hprLinkFunc" Text="View Function Ids" 
          NavigateUrl='<%#string.Format("~/Admin/ViewFunctions.aspx?id={0}", Eval("RoleId")) %>'       runat="server" />
     </ItemTemplate>
    </asp:TemplateField>

    In my ViewFunction.aspx page I have an EntityDataSource that retrieves the query string and use a Where paramter to filter the permissions based on the navigation property

    <asp:EntityDataSource ID="EDS1" runat="server"
          ContextTypeName="BBLDSAccessManager.DAL.BBLondonAdminEntities"
         EntitySetName="Permissions" Where="it.Roles.RoleId=@roleId"         >
          <WhereParameters>
            <asp:QueryStringParameter Name="roleId" Type="String"
                 QueryStringField="id" Direction="Input" />
          </WhereParameters>
    </asp:EntityDataSource>

    For some reason it seems that it does not recognise the navigation it.Roles.RoleId.

    All I am trying it to navigate from the Permission to the navigtion property Roles and then get all permissions that belong to that roleId.

    Basically, I need to get all the permissions based on the RoleID (but this is not a one to many relations, it is a many to many)

    Please can someone share some light on how I will achieve this?

     'RoleId' is not a member of 'Transient.collection[BBLondonAdminModel.Role(Nullable=True,DefaultValue=)]'. To extract a property of a collection element, use a subquery to iterate over the collection. Near simple identifier, line 6, column 10.

    Cheers,

    CP

     

     

     

    Wednesday, October 10, 2012 8:39 AM

Answers

  • User-330204900 posted

    Hi  Pallone, I would use the query extensder and write some linq to do the filter, note you will need to do this on an custom page.

    <asp:QueryExtender TargetControlID="GridDataSource" ID="GridQueryExtender" runat="server">
        <asp:DynamicFilterExpression ControlID="FilterRepeater" />
        <asp:CustomExpression OnQuerying="Filter" />
    </asp:QueryExtender>

    then in the code behind add

    /// <summary>
    /// Filter current records.
    /// </summary>
    protected void Filter(object sender, CustomExpressionEventArgs e)
    {
        e.Query = from a in e.Query.Cast<DAL.BBLondonAdminEntities>()
                  where a... //do your filter here
                  select a;
    }

    I have done this with M:M but it is a awkward query.

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 11, 2012 9:11 AM
  • User1432255915 posted

    Thanks Steve,

    This query seems to work well and it is not so complicated Smile

      e.Query = e.Query.Cast<Permission>().Where(p => p.Roles.Any(pr => pr.RoleId == roleId));

    Cheers,

    CP

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 11, 2012 10:59 AM

All replies

  • User1432255915 posted

    Any help please? Is this a bug in the EntityDataSource?

    Cheers

    Wednesday, October 10, 2012 10:00 AM
  • User1432255915 posted

    Hi,

    I was wondering if I have to use ANYELEMENT to get it to work.

    Does anyone know how to fix my issue?

    <asp:EntityDataSource ID="EDS1" runat="server"
          ContextTypeName="BBLDSAccessManager.DAL.BBLondonAdminEntities"
          EntitySetName="Permissions" 
          Where="it.PermissionId = ANYELEMENT(SELECT VALUE ......)" 
          onquerycreated="EDS1_QueryCreated"
    
            >
    Cheers,
    CP


    Wednesday, October 10, 2012 10:13 AM
  • User1432255915 posted

    I have also tried this but to no avail:

     

     <asp:EntityDataSource ID="EDS1" runat="server"
          ContextTypeName="BBLDSAccessManager.DAL.BBLondonAdminEntities"
          EntitySetName="Permissions" 
          Include="Role"
          Where="it.Roles.[RoleId] = @roleId" 
         
    I get this error:

    'RoleId' is not a member of 'Transient.collection[BBLondonAdminModel.Role(Nullable=True,DefaultValue=)]'. To extract a property of a collection element, use a subquery to iterate over the collection. Near escaped identifier, line 6, column 10.

    Wednesday, October 10, 2012 10:23 AM
  • User-330204900 posted

    Hi Pallone, just goin out but will look into this when I return in about half an hour. :)

    Wednesday, October 10, 2012 10:30 AM
  • User1432255915 posted

    Thanks a lot Steve,

    You are super.

    I have also tried something like this but it did not work:

     <asp:EntityDataSource ID="EDS1" runat="server"
          ContextTypeName="BBLDSAccessManager.DAL.BBLondonAdminEntities"
          EntitySetName="Permissions" 
          Include="Role"
          Where="it.PermissionId IN (SELECT VALUE s.PermissionId FROM Permissions AS s where s.Roles.RoleId = @roleId)" 


    I think my query could be wrong.

    IS there a way to attach an image of my EF to the post?

    Cheers

    Wednesday, October 10, 2012 11:21 AM
  • User-330204900 posted

    Hi  Pallone, I would use the query extensder and write some linq to do the filter, note you will need to do this on an custom page.

    <asp:QueryExtender TargetControlID="GridDataSource" ID="GridQueryExtender" runat="server">
        <asp:DynamicFilterExpression ControlID="FilterRepeater" />
        <asp:CustomExpression OnQuerying="Filter" />
    </asp:QueryExtender>

    then in the code behind add

    /// <summary>
    /// Filter current records.
    /// </summary>
    protected void Filter(object sender, CustomExpressionEventArgs e)
    {
        e.Query = from a in e.Query.Cast<DAL.BBLondonAdminEntities>()
                  where a... //do your filter here
                  select a;
    }

    I have done this with M:M but it is a awkward query.

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 11, 2012 9:11 AM
  • User1432255915 posted

    Thanks Steve,

    This query seems to work well and it is not so complicated Smile

      e.Query = e.Query.Cast<Permission>().Where(p => p.Roles.Any(pr => pr.RoleId == roleId));

    Cheers,

    CP

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 11, 2012 10:59 AM