locked
Having problems creating a custom filter based on a related table (table1 -> table2 -> table3) RRS feed

  • Question

  • User1109545656 posted

    I'm trying to build a custom filter based on the following requirements. I'm  using LINQ to SQL:

    • The data model is like this: Users -> UserRole <- Role  (One user can be in many roles)
    • On the list users page, i want a filter that shows all roles in the dropdown. Then, when I select the role from the dropdown, i'd like the user list to show all users in that group.

    Here's what i've tried so far:

    1) Looked in the Dynamic Data Futures at the Cascade.ascx filter. This looks promising, however it's so complicated I can't figure out 100% how it works. Basically i need just one dropdown instead of two.

    2) I've tried to make a custom filter, here's the relevant code.  First, here's the meta data:

    public partial class User
    {
    public class User_Metadata
    {
       [ScaffoldColumn(true)]
       [
    Filter(FilterControl = "Role")]
      
    public object UsersInRoles { get; set; }
    }
    }

    And then,here's the filter control:

    1        public partial class Role_Filter : System.Web.DynamicData.FilterUserControlBase, ISelectionChangedAware
    2        {
    3    
    4            public event EventHandler SelectionChanged
    5            {
    6                add
    7                {
    8                    DropDownList1.SelectedIndexChanged += value;
    9                }
    10               remove
    11               {
    12                   DropDownList1.SelectedIndexChanged -= value;
    13               }
    14           }
    15   
    16           public override string SelectedValue
    17           {
    18               get
    19               {
    20                   return DropDownList1.SelectedValue;
    21               }
    22           }
    23   
    24           protected void Page_Init(object sender, EventArgs e)
    25           {
    26               if (!Page.IsPostBack)
    27               {
    28   
    29                   DropDownList1.Items.Clear();
    30                   DropDownList1.DataTextField = "RoleName";
    31                   DropDownList1.DataValueField = "RoleID";
    32                   DropDownList1.DataSource = (new RoleService()).ReadAllUsing(RoleService.ReadActiveByWorkspaceID, SessionManager.GetWorkspaceID(Session, Response));
    33                   DropDownList1.DataBind();
    34   
    35                   DropDownList1.Items.Insert(0, new ListItem("All", string.Empty));
    36   
    37                   if (!String.IsNullOrEmpty(InitialValue))
    38                       DropDownList1.SelectedValue = InitialValue;
    39               }
    40           }
    41   
    

     This seems (to me anyway) like it should work. However when i run it, i get the following error:

     Operator '==' incompatible with operand types 'EntitySet`1' and 'String'

    It appears that some part of the framework is trying to compare my business object collection to a string.

    Can anyone point me to a sample, post or tutorial on how to do this correctly? I've been at it for far too long and i'm getitng nowhere. I've scoured google and these forums, and i've found several posts that seem relevant but in the end I'm not able to translate it into a solution.

    Thanks for your help,

    Brian

    Tuesday, November 18, 2008 12:24 AM

Answers

  • User-797310475 posted

    (...) If i switch to Entity Framework are you saying that I can build a custom filter that will work too? As I understand it, the limitation is in the underlying data model and not within the Dynamic Data features. I just want to be sure I understand this correctly before I go through the effort to switch my data model from LINQ2SQL to EF.

    Hi Brian,

    There is no straighforward way of building a filter that would meet your requirements on top of Dynamic Data in 3.5SP1 (even with the ManyToMany template). We are addressing these scenarios for the next version of Dynamic Data, which will ship with .NET 4.0.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 18, 2008 1:49 PM

All replies

  • User-330204900 posted

    What you have there my friend look like a Many to Many relationship not supported under Linq to SQL but is suported under Entity Framework have alook at David Ebbo's sample here A ‘Many To Many’ field template for Dynamic Data

    Hope this helps [:D]

    Tuesday, November 18, 2008 3:48 AM
  • User1109545656 posted

    Thank you for the quick response, that does look like it will help. However I have one question: the article focuses entirely on field templates and doesn't mention filters.  If i switch to Entity Framework are you saying that I can build a custom filter that will work too? As I understand it, the limitation is in the underlying data model and not within the Dynamic Data features.

    I just want to be sure I understand this correctly before I go through the effort to switch my data model from LINQ2SQL to EF.

    Thanks!

    Brian

     

    Tuesday, November 18, 2008 9:35 AM
  • User-330204900 posted

    If you switch to EF you should get the relationship see below:

    From Blogger Pictures
    From Blogger Pictures

    As you can see the M2M table EmplyeeTeritories is made into a relatioship in EF (but not in Linq to SQL) and so you should be able to create your own custom filter see my blog here on adding DD Futures Filters and creating a custom filter here:

    1. Part 1 - Getting Dynamic Data Futures filters working in a File Based Website.
    2. Part 2 - Create the AnyColumn filter from the Dynamic Data Futures Integer filter.
    3. Part 3 – Creating the AnyColumnAutocomplete filter.

    Hope this helps [:D]

    Tuesday, November 18, 2008 10:41 AM
  • User-797310475 posted

    (...) If i switch to Entity Framework are you saying that I can build a custom filter that will work too? As I understand it, the limitation is in the underlying data model and not within the Dynamic Data features. I just want to be sure I understand this correctly before I go through the effort to switch my data model from LINQ2SQL to EF.

    Hi Brian,

    There is no straighforward way of building a filter that would meet your requirements on top of Dynamic Data in 3.5SP1 (even with the ManyToMany template). We are addressing these scenarios for the next version of Dynamic Data, which will ship with .NET 4.0.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 18, 2008 1:49 PM