locked
Data Filtering RRS feed

  • Question

  • User-1087571539 posted

    Hello, 

    I currently work on some projects using Dynamic Data and Entity Framework 3.4.1 (Code First) in a ASP.NET Web Application.

    My datamodel looks like the following:

    - Projects
    - Users (that are assigned to a specific project)
    - HardwareItems (that are assigned to a specific User)

    So for example, the user "John Doe" is part of Project "XYZ" and has 2 HardwareItems. A project can have multiple users assigned.

    In our solution, an administrator can access the web interface and select a project he wants to work on. All Lists and filters should be filtered by the selected project (XYZ). The administrator should not have the possibility to see any of the other users but the users who are part of the project XYZ. Furthermore, in any filters, lists, ... the administrator should only see the HardwareItems of Users that are part of the selected project. 

    We currently managed the filtering by extending e.g. the Lists.aspx File and adding a Where-Clause to the EntityDataSource. The problem is, that the Filter is not reacting to this Where Statement and shows e.g. all hardwareitems and all users no matter what Where Statement is set.

    Is there a good way to filter Data for Dynamic Data in an early stage, like at the creation of the DataContext (DBSets, ...)? 

    Any help would be highly appreciated! Regards

    Christoph

    Monday, April 2, 2012 10:25 AM

Answers

  • User350138131 posted

    Hi to all.

    I ran into a same problem (if I correctly understood the question) and solved it as follows.

    There were added two new tables UsersInProjects  and  AssetsInUsers, connections are built and FilterByRoleAttribute applied.

    [HideTableInDefault(true)]
    public class User_MD
    {
    	public object userId { get; set; }	//PK
    	public object userName { get; set; }
    }
    [HideTableInDefault(true)]
    public class Asset_MD
    {
    public object assetId { get; set; }	//PK
    public object assetName { get; set; }
    }
    [FilterByRole("hoho","projectId")] // FilterByRole([string RoleStartsWith],[string PrimeryColumnName]) 
    public class Project_MD
    { 
    public object projectId { get; set; }	//PK
    public object projectName{ get; set; }	
    }
    public class AssetsInUser_MD
    {
    public object projectId { get; set; }	//PK
    public object userId { get; set; }	//PK
    	public object assetId { get; set; }	//PK
    	[UIHint("Cascade")]
    	[FilterUIHint("Cascade")]
    	[Cascade("Project")]
    	public object UsersInProject { get; set; }
    }
    
    public class UsersInProject_MD
    {
    public object projectId { get; set; }	//PK
    public object userId { get; set; }	//PK
    }
    
    
    [AttributeUsage(AttributeTargets.Class)]
    public class FilterByRoleAttribute : Attribute
    {
    	public string RoleStartsWith { get; set; }
    	public string AssociatedField { get; set; }
    	public FilterByRoleAttribute(string roleStartsWith, string associatedField)
    	{
    		RoleStartsWith = roleStartsWith;
    		AssociatedField = associatedField;
    	}
    	public static FilterByRoleAttribute Default = new FilterByRoleAttribute("",null);
    }
    
    
    public static string[] GetFilterByRole(this MetaTable table)
    {
    var tabValue = table.Attributes.OfType<FilterByRoleAttribute>().DefaultIfEmpty(new FilterByRoleAttribute("",null)).First() as FilterByRoleAttribute;
    	return new string[] { tabValue.RoleStartsWith, tabValue.AssociatedField };
    }
    
    //In aspnet_Roles table add Role ( RoleName=“hoho”+projectId )
    //In aspnet_UsersInRoles table add this Role to specific user
    public partial class ForeignKeyFilter : System.Web.DynamicData.QueryableFilterUserControl
    {
    	protected void Page_Init(object sender, EventArgs e)
    {
    	…
    		PopulateListControl(listControl);
    		PopulateByRoles(listControl, column.ParentTable.GetFilterByRole()[0], "");
    
    }
    }
    public static string[] PopulateByRoles(ListControl DropDownList1, string startsWith, string parentValue)
    {
    	string[] values; //Current user`s roles with names, starts with string startsWith. 
    	Remove all items of DropDownList1 except those, whose values are contained in  string[] values.
    	return values;
    }
    


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 23, 2012 9:00 AM

All replies

  • User-330204900 posted

    Hi christoph_D, you should use the QueryExtender to do this :) as that is also used byt the filters :)

    Tuesday, April 3, 2012 5:25 AM
  • User3866881 posted

    Hello christ:)

    Agree with the MVP's idea——In my mind,QueryExtender is the "extender" that has extended the functionality of searching for different kinds of fields such as boolean,int,string……ect。What you do is just bind the DataSource to the QueryExtender and then set some properties。

    See the free sample at:http://www.codeproject.com/KB/webforms/aspnet4queryextender.aspx?PageFlow=Fluid

    Tuesday, April 3, 2012 9:14 PM
  • User-1087571539 posted

    Hello, 
    thanks for your answers, sorry for my late reply. The solution you mentioned works like a charm, but unfortunately only partly.

    The QueryExtender does not apply to the ForeignKeyFilters. 

    For example: 
    My model looks like the following: 
    - Projects
    - User (a User is assigned to specific project) 
    - Asset (an Asset is assigned to a specific User)

    I create two Projects, A and B. My QueryExtender filters Users and Assets on the currently selected Project. So viewing the User and Asset list only displays the items that are in this selected project. Nice.
    BUT:

    Looking at the Assets List, and the filters that are available, I can filter by the User that are assigned to this Asset. (Like "Display all Assets of User X"). The problem is, that the ForeignKey Filter will display ALL Users in the database, and not only the Users that are available in this project. 

    Therefore, the method using the QueryExtender is too late to filter the items - it would be nice filtering the data on base of Entity Framework. But this does not really work.

    Any ideas? Can I apply the same QueryExtender to the Filters?

    Thanks for your help. 
    Christoph 

    Sunday, April 22, 2012 2:32 PM
  • User-330204900 posted

    Hi Christoph, it will work with FK's you just need to 1. disable the mail filter using [isplay(AutoGenerate false)] on those columns then you should be able to apply the filters. However what I do for columns that already have filters is create a custome filter and then hide it.

    Monday, April 23, 2012 7:41 AM
  • User350138131 posted

    Hi to all.

    I ran into a same problem (if I correctly understood the question) and solved it as follows.

    There were added two new tables UsersInProjects  and  AssetsInUsers, connections are built and FilterByRoleAttribute applied.

    [HideTableInDefault(true)]
    public class User_MD
    {
    	public object userId { get; set; }	//PK
    	public object userName { get; set; }
    }
    [HideTableInDefault(true)]
    public class Asset_MD
    {
    public object assetId { get; set; }	//PK
    public object assetName { get; set; }
    }
    [FilterByRole("hoho","projectId")] // FilterByRole([string RoleStartsWith],[string PrimeryColumnName]) 
    public class Project_MD
    { 
    public object projectId { get; set; }	//PK
    public object projectName{ get; set; }	
    }
    public class AssetsInUser_MD
    {
    public object projectId { get; set; }	//PK
    public object userId { get; set; }	//PK
    	public object assetId { get; set; }	//PK
    	[UIHint("Cascade")]
    	[FilterUIHint("Cascade")]
    	[Cascade("Project")]
    	public object UsersInProject { get; set; }
    }
    
    public class UsersInProject_MD
    {
    public object projectId { get; set; }	//PK
    public object userId { get; set; }	//PK
    }
    
    
    [AttributeUsage(AttributeTargets.Class)]
    public class FilterByRoleAttribute : Attribute
    {
    	public string RoleStartsWith { get; set; }
    	public string AssociatedField { get; set; }
    	public FilterByRoleAttribute(string roleStartsWith, string associatedField)
    	{
    		RoleStartsWith = roleStartsWith;
    		AssociatedField = associatedField;
    	}
    	public static FilterByRoleAttribute Default = new FilterByRoleAttribute("",null);
    }
    
    
    public static string[] GetFilterByRole(this MetaTable table)
    {
    var tabValue = table.Attributes.OfType<FilterByRoleAttribute>().DefaultIfEmpty(new FilterByRoleAttribute("",null)).First() as FilterByRoleAttribute;
    	return new string[] { tabValue.RoleStartsWith, tabValue.AssociatedField };
    }
    
    //In aspnet_Roles table add Role ( RoleName=“hoho”+projectId )
    //In aspnet_UsersInRoles table add this Role to specific user
    public partial class ForeignKeyFilter : System.Web.DynamicData.QueryableFilterUserControl
    {
    	protected void Page_Init(object sender, EventArgs e)
    {
    	…
    		PopulateListControl(listControl);
    		PopulateByRoles(listControl, column.ParentTable.GetFilterByRole()[0], "");
    
    }
    }
    public static string[] PopulateByRoles(ListControl DropDownList1, string startsWith, string parentValue)
    {
    	string[] values; //Current user`s roles with names, starts with string startsWith. 
    	Remove all items of DropDownList1 except those, whose values are contained in  string[] values.
    	return values;
    }
    


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 23, 2012 9:00 AM
  • User350138131 posted

    Sorry, in the case of CascadeFilter

    public partial class CascadeFilter : System.Web.DynamicData.QueryableFilterUserControl
    {
    	protected void Page_Init(object sender, EventArgs e)
              {
    	…
    		PopulateListControl(listControl);
    		PopulateByRoles(parentDropDown, parentTable.GetFilterByRole()[0], "");
    
              }
    
              void parentDropDown_SelectedIndexChanged(object sender, EventArgs e)
              {
                ...
                PopulateByRoles(filterDropDown, filterTable.GetFilterByRole()[0], parentDropDown.SelectedValue);
              }
    }

    Monday, April 23, 2012 10:27 AM
  • User350138131 posted

    To filter the grid (Reports table)

            protected void QueryExtender_OnQuering(object sender, CustomExpressionEventArgs e)
            {
                string[] filtByRole = table.GetFilterByRole();
                string[] selectedValues = PopulateByRoles(null, filtByRole[0], "");
                if (selectedValues != null && selectedValues.Length > 0)
                {
                    ParameterExpression parameterExpression = Expression.Parameter(e.Query.ElementType, "item");
                    List<Expression> orFragments = new List<Expression>();
                    foreach (string serializedValue in selectedValues)
                    {
                        List<Expression> andFragments = new List<Expression>();
                        Expression property = ExpressionHelper.CreatePropertyExpression(parameterExpression, filtByRole[1]);
                        object value = Convert.ChangeType(serializedValue, ExpressionHelper.GetUnderlyingType(property.Type));
                        Expression equalsExpression = Expression.Equal(property, Expression.Constant(value, property.Type));
                        andFragments.Add(equalsExpression);
                        var expr = ExpressionHelper.Join(andFragments, Expression.AndAlso);
                        orFragments.Add(expr);
                    }
    
                    Expression body = ExpressionHelper.Join(orFragments, Expression.OrElse);
                    LambdaExpression lambda = Expression.Lambda(body, parameterExpression);
                    MethodCallExpression whereCall = Expression.Call(typeof(Queryable), "Where", new Type[] { e.Query.ElementType }, e.Query.Expression, Expression.Quote(lambda));
                    e.Query = e.Query.Provider.CreateQuery(whereCall);
                }
            }
    
    
    public static string[] GetFilterByRole(this MetaTable table)
    {
             var tabValue = table.Attributes.OfType<FilterByRoleAttribute>().DefaultIfEmpty(new      FilterByRoleAttribute("",null)).First() as FilterByRoleAttribute;
    	return new string[] { tabValue.RoleStartsWith, tabValue.AssociatedField };
    }

    Wednesday, April 25, 2012 3:02 AM