Data Filtering [Answered] LockedRSS

• • Reply
  christoph_D

  None

  0 Points

  6 Posts

  Data Filtering

  Apr 02, 2012 10:25 AM|christoph_D|LINK

  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

• • Reply
  sjnaughton

  All-Star

  17916 Points

  5681 Posts

  MVP

  Re: Data Filtering

  Apr 03, 2012 05:25 AM|sjnaughton|LINK

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

  See my blog C# Bits | Twitter @sjnaughton
  Always seeking an elegant solution.
• • Reply
  Decker Dong...

  All-Star

  94130 Points

  18109 Posts

  Re: Data Filtering

  Apr 03, 2012 09:14 PM|Decker Dong - MSFT|LINK

  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

• • Reply
  christoph_D

  None

  0 Points

  6 Posts

  Re: Data Filtering

  Apr 22, 2012 02:32 PM|christoph_D|LINK

  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 

• • Reply
  sjnaughton

  All-Star

  17916 Points

  5681 Posts

  MVP

  Re: Data Filtering

  Apr 23, 2012 07:41 AM|sjnaughton|LINK

  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.

  See my blog C# Bits | Twitter @sjnaughton
  Always seeking an elegant solution.
• • Reply
  valZ

  Member

  71 Points

  48 Posts

  Re: Data Filtering

  Apr 23, 2012 09:00 AM|valZ|LINK

  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;
  }

  
  

• • Reply
  valZ

  Member

  71 Points

  48 Posts

  Re: Data Filtering

  Apr 23, 2012 10:27 AM|valZ|LINK

  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);
            }
  }

• • Reply
  valZ

  Member

  71 Points

  48 Posts

  Re: Data Filtering

  Apr 25, 2012 03:02 AM|valZ|LINK

  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 };
  }