Thursday 13 January 2011

Adding a Multi-Column Search to the Default List page in Dynamic Data (UPDATED)

This will extend the default filters that Dynamic Data provides by adding a free text search box the will search multiple text fields on the same page. I have done this in a few projects now as one off but after this post Custom filters & searches within DD in Visual Web Developer 2010 on the Dynamic Data Forum I decided to document it here.

Multi Column Search

Figure 1 – Multi Column Search

Here in Figure 1 you can see the multi column search box, in this example the search is on FistName, LastName, Address, City and Country, it will even allow search across entities e.g. on Orders Employee.LastName etc. which make it very flexible.

Things we will need to do

  • Attribute to tell the system which columns to search on.
  • Add mark-up to show the UI on the List page.
  • Code to wire up the UI and QueryExtender.

The Attribute

This attribute will be added to the Class/Table not it’s columns and will have only one property Columns.

[AttributeUsage(AttributeTargets.Class)]
public class MultiColumnSearchAttribute : Attribute
{
    public String[] Columns { get; private set; }

    public MultiColumnSearchAttribute(params String[] columns)
    {
        Columns = columns;
    }
}

Listing 1 – MultiColumnSearchAttribute

and we apply it as in Listing 2 The Employee table show standard multi-column search but the Order table shows how we can search across entity boundaries you can see this in Figure 2.

[MetadataTypeAttribute(typeof(Employee.EmployeeMetadata))]
[MultiColumnSearch(
    "LastName", 
    "FirstName", 
    "Address", 
    "City", 
    "Country")]
public partial class Employee
{
    internal sealed class EmployeeMetadata
    {
        //...
    }
}

[MetadataTypeAttribute(typeof(Order.OrderMetadata))]
[MultiColumnSearch(
    "Employee.LastName", 
    "Employee.FirstName", 
    "Customer.CompanyName", 
    "Customer.ContactName", 
    "Shipper.CompanyName")]
public partial class Order
{
    internal sealed class OrderMetadata
    {
        //...
    }
}

Listing 2 – sample metadata

Cross Entity Search

Figure 2 – cross entity search

Adding UI Mark-Up

I have added the multi column search UI in between the validators and the QueryableFilterRepeater see Listing 3

<asp:DynamicValidator runat="server" ID="GridViewValidator" ControlToValidate="GridView1"
    Display="None" CssClass="DDValidator" />

<fieldset id="MultiSearchFieldSet" class="DD" runat="server" visible="false">
    <legend>Full Text Search</legend>
    <asp:TextBox ID="txbMultiColumnSearch" CssClass="DDTextBox" runat="server" />
    <asp:Button ID="btnMultiColumnSearchSubmit" CssClass="DDControl" runat="server" Text="Search"
        OnClick="btnMultiColumnSearch_Click" />
    <asp:Button ID="btnMultiColumnSearchClear" CssClass="DDControl" runat="server" Text="Clear"
        OnClick="btnMultiColumnSearch_Click" />
</fieldset>
<br />

<asp:QueryableFilterRepeater runat="server" ID="FilterRepeater">

Listing 3 – Multi Column Search UI

Code to wire up the UI and QueryExtender

/// <summary>
/// Setups the multi column search.
/// </summary>
private void SetupMultiColumnSearch()
{
    // get multi column search attribute
    var multiColumnSearch = table.GetAttribute<MultiColumnSearchAttribute>();

    if (multiColumnSearch != null)
    {
        var searchExpression = new SearchExpression()
            {
                DataFields = multiColumnSearch.Columns.ToCsvString(),
                SearchType = SearchType.Contains
            };
        
        // create control parameter
        var controlParameter = new ControlParameter() { ControlID = txbMultiColumnSearch.ID };
        
        // add control parameter to search expression
        searchExpression.Parameters.Add(controlParameter);

        // set context
        searchExpression.SetContext(GridQueryExtender, Context, GridDataSource);

        // add search expression to query extender
        GridQueryExtender.Expressions.Add(searchExpression);

        // make multicolumn search field set visible
        MultiSearchFieldSet.Visible = true;
    }
}

Listing 5 – SetupMultiColumnSearch method

In Listing 5 (which is called from the Page_Init method) we get the attribute and the SearchExpression the if both are not null we add the fields to the SearchExpressions DataFileds property, then we make the whole thing visible.

Note: Much thanks to David Ebbo who sorted out the issue I had had with adding the search expression in code rather than declaratively.

protected void btnMultiColumnSearch_Click(object sender, EventArgs e)
{
    var button = (Button)sender;
    if (button.ID == btnMultiColumnSearchClear.ID)
        txbMultiColumnSearch.Text = String.Empty;
}

Listing 6 – button event

And lastly we need an event to fire when the clear button is fired.

Note: I have both buttons wired-up to this handler as I also add session history here so that when you search on something and go to another page when you return you get the same search, but I also want it cleared if I click the clear button.

Download

54 comments:

Gareth Thomas Hill said...

Another great post Steve - this seems to be just what I'm looking for!

However, trying to implement it in an existing 'ASP.NET Dynamic Data Linq to SQL Web Application' and just realised your example is 'ASP.NET Dynamic Data Entities Web Application'.

It's complaining I don't have a definition for OfType in GridQueryExtender.Expressions.OfType in the SetupMultiColumnSearch method.

Any ideas how I can get it to work with Linq-To-SQL?

Gareth Thomas Hill said...

Doh, further to previous comment just realised I was missing using System.Linq from List.aspx.cs and that was the error rather than it being a Linq-to-SQL/Entities issue.

ocking said...

Hi Steve,

You have a great work and have a top rank in google search

Please kindly advise where to control the LINQ expression for case in-sensitive search ? Thank you for your inspiration

System.StringComparison.OrdinalIgnoreCase

Thank you so much again

Stephen J. Naughton said...

By default the search is case in-sensitive it all depends on your DB setup.

Steve

ocking said...

sorry, I forgot to mention I'm using Oracle9i and I can make it case-insensitive match on '=' operator but note 'like' operator
what can i do on the coding level ?
thx again

FYR.
http://www.orafaq.com/node/999

Setting NLS_COMP to ANSI causes Oracle to use the sort order specified in NLS_SORT when doing an ORDER BY. But one big limitation is that when NLS_COMP is set to ANSI, only certain SQL functions and operations will use the NLS_SORT sort order. The rest will still use the default BINARY sort order. For example, the = (equality) operator will do a case-insensitive comparison, but the "like" operator will not:

Stephen J. Naughton said...

Sorry Ocking, the case in-sensitivity all happens in the DB I the only thing you could do is make the search patter all lower case but I don't think that would help.

Steve

ocking said...

Thanks Steve for your guidance. I think I can try working on search pattern. how can i include both GetMethod “toUpper” and “Contains” in the same Expression.Call statement ?

Sorry for my newbie questions. Thank you again for your kindly help.

Stephen J. Naughton said...

if you are generating your own expression the just format the parameter before adding to the expression.

Steve

Kosmix said...

Any idea why I get this error?

[ArgumentException: Argument types do not match]
System.Linq.Expressions.Expression.Constant(Object value, Type type) +4190292
System.Web.UI.WebControls.Expressions.SearchExpression.CreateCallExpression(Expression property, String query) +150
System.Web.UI.WebControls.Expressions.SearchExpression.GetQueryable(IQueryable source) +394
System.Web.UI.WebControls.Expressions.QueryExpression.GetQueryable(IQueryable source) +161
System.Web.UI.WebControls.QueryExtender.OnDataSourceQueryCreated(Object sender, QueryCreatedEventArgs e) +47
System.Web.UI.WebControls.QueryableDataSourceView.OnQueryCreated(QueryCreatedEventArgs e) +108
System.Web.UI.WebControls.QueryableDataSourceView.ExecuteQueryExpressions(IQueryable source, QueryContext context) +52
System.Web.UI.WebControls.QueryableDataSourceView.ExecuteQuery(IQueryable source, QueryContext context) +21
System.Web.UI.WebControls.LinqDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +343
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +26
System.Web.UI.Control.PreRenderRecursiveInternal() +103
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2496

Stephen J. Naughton said...

it's as it says an argument exception the type you can search on must be a string so if you are searching on a FK relationsip you must put something like
User.Name rather than just User.

Steve

Anonymous said...

Hi, all. Great post! Today i tested of using Dynamic Data 4 technology with one of my tables which consists about one million records(now it is only for test). Full search works is very fine. But if this table is involved in relationships it seems that it is necessarily to removed all dropdown filters...

Sorry for my little English.

Stephen J. Naughton said...

Hi there, I have never had to remove any FK filters or any other filters. the queries are composable so there should be no issue also you can can include Field from ForeignKey relationships
i.e.
Products<-Categories
you can include the Category.Description Field as a column to search on.

Steve

Konstantin Konstantinov said...

Hello, Steve! In my post i have meant following. For instance say that we have used Northwind database and the table Customers consists, for example, of 700 000 records. Then say we have opened table Customers in our Dynamic Data site. The we want to click link View Orders which is in column Orders. How do you think, how much time the page with Orders will load?

You know, that the list page with Orders consists dropdown filter with 700 000 records of customers.

Stephen J. Naughton said...

Hi Konstantin, are you talking about a ForeignKey filter that has 700,000 records? if so you can replace those with the autocomplete filter also available from NuGet.Org

Steve

Konstantin Konstantinov said...

Yes, i am talking about a ForeignKey filter(dropdown list) that has 700,000 records.

Thank you very much for your answer. I have understood you and in future i will do it.

Generally speaking,i have used alternative way of multi colulmn search.

It was as you can see below:







But this way is very very ineffective when we have too many records...

Stephen J. Naughton said...

I like the Autocomplete idea and would like a better control I will probably go for a proprietary control from Telerik or DevExpress that will make the UI slicker. The principal of showing only matched records is the way for Large rows in FK column though.

Steve

P.S. can't see you image and I'm happy for you to send me e-mail direct my address is at the top of the page.

Tim Faase said...

How hard would it be to alter the code to search ALL system.string fields?

We run a rapid web SD business and adding metadata to what we've mostly realized as needing all fields searched would make things easier.

Also, can you do without the ToCSVString? We develop in VB and having issues transferring the function.

Tim

Stephen J. Naughton said...

Quite easy I think you would just need to loop through the metacolumns for the current talbe and check their type. should be relativly easy.

Send me a direct mail and I'll e-mail you the code.

Steve

Tim Faase said...

Hi Steve, I figured it out thanks. Persistence was what I needed!

One question I do have now however :)

I wrote a line just after it puts together the CSV string of columns it's searching.

'Uncomment to customise search
'Example searches specified parent table ALSO
If table.Name = "ChildTbls" Then
strSearchingColumns &= ",ParentTbl.Test"
End If

However it doesn't like the parent relationship in the query. I'm guessing it's related to your line in page_load:

GridDataSource.Include = table.ForeignKeyColumnsNames;

I'm using a LINQ datasource and my data source doesn't include INCLUDE property.

Thoughts?

Stephen J. Naughton said...

Hi Tim, you can't filter ove a 1:M only a M:1 relationship that is why you are havin issues.

Steve

Tim Faase said...

Hi Steve, yep - that's what I'm doing. On the child table I'm searching ParentTbl.ColName

There's no tricks to get it to work? Just should happen?

Stephen J. Naughton said...

And is that field a tring field?

Steve

P.S. you can e-mail me direct if you like my e-mail address is in my profile.

Andrew said...

Hi Steve, thanks heaps for the guide, the search is working great in our product!

Quite often we use custom foreign key display names instead of the default. One way we figured on doing this was to extend LINQ classes with custom properties:

E.g.

Partial Class Person

Public Readonly Property FullName() As String
Get
Return FirstName & " " & Surname
End Get

End Class

Then setting on the metadata. So when a person shows up as a foreign key, we see their full name instead of just the default foreign key display name.

However, this breaks the search :( We get an exception about the property having no supported translation to SQL. Understandable, since this property is only implemented in the VB.NET code.

A couple of alternatives we have looked at:

1) Using computed columns to generate the display name: this works great except you can only use local columns to generate computed columns, so this isn't an option is we need to refer to a foreign key to generate the custom foreign key display name.

2) Using a new column which gets re-computed in VB.NET on INSERT/UPDATE. This works but is not particularly elegant, is is a bit annoying to develop in case of needing to add the column after the table already has data, or wanting to change the definition of the foreign key display name - we have to iterate over the whole table and update manually.

Do you know of any "nice" way to solve this problem? If we could get the search working with those LINQ class property extensions that would be just gold... some way to support translation of that property into SQL...

Cheers!

Stephen J. Naughton said...

Hi Anderew, I generally use computed column I get around the FK issue by using scaler valued functions and pass in the FK value.

Send me a direct e-mail and we'll have a chat about it.

Steve

Anonymous said...

Hi Steve, great article.

But I have one question: what mechanism we can use to search over N:M or 1:M relations , for example at Northwind DB from the employee list, filter the employees that have an exact Territory. Any idea ?

I also have tried Custom Expressions but when I create the linq query you are very limited to the current entity collection, I have not found to filter using other entities' properties

Stephen J. Naughton said...

you cannot generically filter on 1:M I would use the a DynamicFilterExpression in the QueryExtender and the filter with some custom Linq.

Steve

Unknown said...


Any idea why i am getting below mentioned error. columsn belongs to same table. no dependency among columns.

[ArgumentException: 'System' is not a member of type 'ProjectName.tableName']
System.Linq.Expressions.Expression.PropertyOrField(Expression expression, String propertyOrFieldName) +2745289
System.Web.UI.WebControls.Expressions.ExpressionHelper.CreatePropertyExpression(Expression parameterExpression, String propertyName) +129
System.Web.UI.WebControls.Expressions.SearchExpression.GetQueryable(IQueryable source) +381
System.Web.UI.WebControls.Expressions.QueryExpression.GetQueryable(IQueryable source) +161
System.Web.UI.WebControls.QueryExtender.OnDataSourceQueryCreated(Object sender, QueryCreatedEventArgs e) +47
System.Web.UI.WebControls.QueryableDataSourceView.OnQueryCreated(QueryCreatedEventArgs e) +108
System.Web.UI.WebControls.QueryableDataSourceView.ExecuteQueryExpressions(IQueryable source, QueryContext context) +52
System.Web.UI.WebControls.QueryableDataSourceView.ExecuteQuery(IQueryable source, QueryContext context) +21
System.Web.UI.WebControls.LinqDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +343
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +26
System.Web.UI.Control.PreRenderRecursiveInternal() +103
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Control.PreRenderRecursiveInternal() +175
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2496

Stephen J. Naughton said...

HI Navneet, this only works with text columns that will work with T-SQL

Column LIKE '%search term%'

the error says you are trying to do a search on column "System" is this a column in your table?

Steve

CryptoJones said...

Sorry to sound like a newb, but where does

[AttributeUsage(AttributeTargets.Class)]
public class MultiColumnSearchAttribute : Attribute
{
public String[] Columns { get; private set; }

public MultiColumnSearchAttribute(params String[] columns)
{
Columns = columns;
}
}




AND

[MetadataTypeAttribute(typeof(Employee.EmployeeMetadata))]
[MultiColumnSearch(
"LastName",
"FirstName",
"Address",
"City",
"Country")]
public partial class Employee
{
internal sealed class EmployeeMetadata
{
//...
}
}

[MetadataTypeAttribute(typeof(Order.OrderMetadata))]
[MultiColumnSearch(
"Employee.LastName",
"Employee.FirstName",
"Customer.CompanyName",
"Customer.ContactName",
"Shipper.CompanyName")]
public partial class Order
{
internal sealed class OrderMetadata
{
//...
}
}


Go? I was assuming somewhere in DynamicData\Templates\List.aspx.cs

Andrew said...

CryptoJones: Just put them in their own .cs files. E.g. a sensible place for MultiColumnSearchAttribute would be in a file called MultiColumnSearchAttribute.cs, and a sensible place for the Employee partial class would be in Employee.cs.

If the 'Employee' entity class is in some namespace, then just make sure your partial class is in the same namespace (you probably won't need to worry about this).

CryptoJones said...

Okay, so I downloaded the Code Example and implemented the code in my project.

But the Search button still just does nothing.

On the example code, I saw that on List.aspx.cs, right above the private void SetupMultiColumnSearch() it said // not working.

Does the example code actually work?

Thanks again for all your help.

CJ

Stephen J. Naughton said...

It did i will test, I've been ill with a heat attack and heart bypass.

CryptoJones said...

Steve,

Sorry to hear about your health issues. I changed the connection to a local copy of the Northwind database and it worked.

For some reason I can not get it to work using my database and metadata.

Any advice is greatly apreciated.

Thanks,
CJ

Stephen J. Naughton said...

I'll look into it for you

Steve

Marcus Abrahamsson said...

Hi

Is it possible to make a searchexpression or something similiar that works with operators like OR?

Stephen J. Naughton said...

You would need to do a lot more work I think what I have added here is fine for most of my customers.

Steve

Anonymous said...

Thanks Steve

I have implemented your solution in my application and it worked as advertised. Then I realised we need more than one multisearch on the same form and have modified your solution somewhat. The process is as follows.
First I changed the MultiColumnSearchAttribute as follows

[AttributeUsage(AttributeTargets.Property)]
public class MultiColumnSearchAttribute : Attribute
{
public String GroupName { get; private set; }

public MultiColumnSearchAttribute(String groupname)
{
GroupName = groupname;
}
}

Then in my Metadata class I set the attribute for column with the name of the textbox that will be used to do the search. The idea is to use the argument as something we can group by and filter on.

[MultiColumnSearch("txbMultiColumnSearchSubject")]
public string SubjectLine { get; set; }

Then I added required number of search boxes in List.aspx




Finaly, I've modified your SetupMultiColumnSearch method in List.aspx.cs as follows

private void SetupMultiColumnSearch()
{
var query = table.Columns.Where(x => x.GetAttribute() != null).GroupBy(y => y.GetAttribute().GroupName).Select(t => t).ToArray();
foreach (var obj in query)
{
var multiColumnSearch = table.Columns.Where(x => x.GetAttribute() != null).Where(x => x.GetAttribute().GroupName == obj.Key).Select(t => t.Name).ToArray();
var searchExpression = new SearchExpression()
{
DataFields = multiColumnSearch.ToCsvString(),
SearchType = SearchType.Contains
};

// create control parameter
var controlParameter = new ControlParameter() { ControlID = obj.Key };

// add control parameter to search expression
searchExpression.Parameters.Add(controlParameter);

// set context
searchExpression.SetContext(GridQueryExtender, Context, GridDataSource);

// add search expression to query extender
GridQueryExtender.Expressions.Add(searchExpression);
}
}

And there you go, more than 1 multisearch on your page.

Thanks very much for doing the hard work

Nick

Stephen J. Naughton said...

Thanks Nick, good call :)

Steve

Anonymous said...

sorry I am looking at this and wanted to ask about what the 'table' object is in line 7 of listing 3 ... is that a MetaTable object or something else? the metatable object does not have a GetAttribute method and I am unsure what you are referring to

Stephen J. Naughton said...

Hi that is just a helper function you can get from another of my posts see it here Writing Attributes and Extension Methods for Dynamic Data

Steve

Stephen J. Naughton said...

This is on the List page and is the table field. this is set to the correct meta table in the Page_Init

Steve

Faizan said...

Hi Steve. Hope you are in good health. I am getting an error in SetupMultiColumnSearch() function in var multiColumnSearch = table.GetAttribute();

'System.Web.DynamicData.MetaTable' does not contain a definition for 'GetAttribute' and no extension method 'GetAttribute' accepting a first argument of type 'System.Web.DynamicData.MetaTable' could be found (are you missing a using directive or an assembly reference?

Please help i am stuck up...

Stephen J. Naughton said...

Hi Faizan, go to my post to anonymous three back where I link to the Writing Attributes and Extension Methods for Dynamic Data article that is what you need.

Faizan said...

Thanks Steve. I have resolved the issue. After that another error occured in
DataFields = multiColumnSearch.Columns.ToCsvString(),

I searched it on internet and used the overload of string.join as
DataFields = string.Join(",", multiColumnSearch.Columns),

Thanks once again.

Stephen J. Naughton said...

Thanks Faizan, that look good I had never seen that before :)

Steve

Unknown said...

Hi Steve..
I want Search for different Datatypes.

Stephen J. Naughton said...

Hi Sravanthi, sadly the Query Extender does not support this :( you would have to add simple column filters for that.

Steve

Pawan said...

Hi Steve,

From where I can get the sample code?

Stephen J. Naughton said...

the code is on my OneDrive but onedrive broke my links so just look around my public folder here my Public OneDrive Folder

Steve

Pawan said...

Thanks Steve,

My current application is on ASP.net MVC2.0 and Dynamic data. Can I implement the same in my exixting code?

Stephen J. Naughton said...

MVC should make no difference Dynamic Data is all WebForms MVC wont mater. what version of ASP.Net are you running?

Pawan said...

I am using Version: 2.0.50727.5477

Pawan said...

I am using Version: 2.0.50727.5477

Stephen J. Naughton said...

sorry I meant .Net what version of .Net, I suspect now that it may be 3.5 SP1 that Dynamic Data came out in?

Steve