changing column data type for filtering

Feb 28, 2014 at 10:12 AM
Edited Feb 28, 2014 at 10:13 AM

I have an entity that has some fields that show boolean values. Unfortunately some of these are bit fields (0,1) and some of them are strings ("true", "false").

The problem is that I want a consistent filtering popup. So I want all the fields to be System.Boolean. So the user can filter on "yes" and "no" instead of having to search for equals or contains.

I have tried stuff like:
 columns.Add(u => Convert.ToBoolean(u.Authorised)).Titled("Authorised?").SetWidth(110).Sortable(true).Filterable(true);
But I get a "not supported by grid" error.

I can't change the database. Is there a way I can standardise these fields just for grid filtering?

Mar 1, 2014 at 5:29 AM

Which of type your Authorised property?
Mar 3, 2014 at 8:38 AM

sorry I didn't make this clear. The bit fields work just fine and the grid shows the boolean filter widget. However, the string fields show the string filter widget.

I want the string fields to show the boolean filter widget when they contain "true" or "false" strings.

As a workaround I switched to calling a stored proc and cast the strings to bit fields which worked fine. However, I wondered if or how I could cast them in the grid code instead?

Mar 12, 2014 at 3:20 AM

Try this:
columns.Add(u => u.Authorised).Titled("Authorised?").Sortable(true).Filterable(true).SetFilterWidgetType("System.Boolean")
More info about custom filters
Mar 21, 2014 at 9:32 AM
thank Bukharin but I already tried that and got the same result.

In the end I ditched EF for this problem because I was having too much trouble with INNER JOINs and these kinds of data issues.

So I used a stored proc and cast the string Booleans to bit type and the grid handles it fine now, e.g.

cast((SELECT (case when (up1.propertyvalue LIKE '%true%') then 1 else 0 end)) as bit) as Authorised

I like EF for simple cases and basic CRUD operations but once the queries become complex I think it's a nightmare. I am going to follow the guys
at Stackoverflow next time and use Dapper.