A Richer DynamicFilterRepeater – Part 4 Custom DynamicFilterControls & Expressions
Posted by jheyse on April 11th, 2008
In post 2 in this series I contrived a scenario where composite parameters were used to select products from the AdventureWorks database by weight. The weight of products is stored in either lbs or grams which make it necessary for the following SQL logic:
select
*
from
Production.Product
where
(WeightUnitMeasureCode = 'LB' and Weight >= 2 and Weight <= 3) or
(WeightUnitMeasureCode = 'G' and Weight >= 907 and Weight <= 1306)
Previously, this was accomplished by compounding a serious of And, Or, and Range expressions together. We can condense this down to a single custom range parameter and corresponding FilterUserControl.
The first step is to create a user control which extends RangeFilterUserControlBase. We will name it Weight and place it in the ~/App_Shared/DynamicDataFilters/ folder. The control will allow for values in both Grams and Pounds to support globalization, so we need to add a UnitType property. The ascx for the control defines tbMin, tbMax, and a DropDownList to allow the user to select either Pounds or Grams. The control extends RangeFilterUserControlBase and overrides the default implementation of GetWhereParameters and returns a WeightExpressionParameter which we will define next.
public partial class Weight : Catalyst.Web.DynamicData.RangeFilterUserControlBase
{
public enum WeightUnit
{
Pounds,
Grams
}
public WeightUnit UnitType
{
get
{
return (WeightUnit)Enum.Parse(typeof(WeightUnit), DropDownList1.SelectedValue);
}
set
{
DropDownList1.Items.FindByValue(value.ToString()).Selected = true;
}
}
public override string MinValue
{
get
{
return tbMin.Text;
}
set
{
tbMin.Text = value;
}
}
public override string MaxValue
{
get
{
return tbMax.Text;
}
set
{
tbMax.Text = value;
}
}
public override TypeCode Type
{
get { return TypeCode.Decimal; }
}
public override IEnumerable<Parameter> GetWhereParameters(IDynamicDataSource dataSource)
{
yield return new WeightExpressionParameter()
{
Name = this.DataField,
MinValue = this.MinValue,
MaxValue = this.MaxValue,
UnitType = this.UnitType,
Type = this.Type
};
}
}
The WeightExpressionParameter extends the RangeExpressionParameter and overrides the GetLambaExpression to return a LambdaExpression which will do the Grams to Pounds conversion for us. The WeightExpressionParameter also includes a property for the UnitType which is stored in ViewState to ensure post backs and Ajax enabled controls work correctly.
public class WeightExpressionParameter : RangeExpressionParameter
{
private const decimal ConversionFactor = 453.59237m;
public WeightUnit UnitType
{
get
{
object obj2 = this.ViewState["UnitType"];
if (obj2 == null)
{
return WeightUnit.Pounds;
}
return (WeightUnit)obj2;
}
set
{
if (this.UnitType != value)
{
this.ViewState["UnitType"] = value;
this.OnParameterChanged();
}
}
}
public override LambdaExpression GetLambdaExpression(Type itType)
{
decimal? min = (decimal?)MinParameterValue;
decimal? max = (decimal?)MaxParameterValue;
if (UnitType == WeightUnit.Grams)
{
min /= ConversionFactor;
max /= ConversionFactor;
}
if(itType == typeof(BusinessObjects.Product))
{
Expression<Func<BusinessObjects.Product, bool>> x = p =>
(min == null || (p.WeightUnitMeasureCode == "lb" ? p.Weight : p.Weight / ConversionFactor) >= min) &&
(max == null || (p.WeightUnitMeasureCode == "lb" ? p.Weight : p.Weight / ConversionFactor) <= max);
return x;
}
else
throw new ArgumentException("Expected itType to be of type BusinessObjects.Product", "itType");
}
}
The LINQ-to-SQL interpreter is amazingly smart and is able to understand fairly complex statements. This SQL that is passed to the database server is exactly what we are looking for, it is even able to short circuit the query if either min or max is null!
SELECT TOP (10) [t0].[ProductID], [t0].[Name], [t0].[ProductNumber], [t0].[MakeFlag], [t0].[FinishedGoodsFlag], [t0].[Color], [t0].[SafetyStockLevel], [t0].[ReorderPoint], [t0].[StandardCost], [t0].[ListPrice], [t0].[Size], [t0].[SizeUnitMeasureCode], [t0].[WeightUnitMeasureCode], [t0].[Weight], [t0].[DaysToManufacture], [t0].[ProductLine], [t0].[Class], [t0].[Style], [t0].[ProductSubcategoryID], [t0].[ProductModelID], [t0].[SellStartDate], [t0].[SellEndDate], [t0].[DiscontinuedDate], [t0].[rowguid], [t0].[ModifiedDate], [t2].[test], [t2].[ProductSubcategoryID] AS [ProductSubcategoryID2], [t2].[ProductCategoryID], [t2].[Name] AS [Name2], [t2].[rowguid] AS [rowguid2], [t2].[ModifiedDate] AS [ModifiedDate2], [t4].[test] AS [test2], [t4].[ProductModelID] AS [ProductModelID2], [t4].[Name] AS [Name3], [t4].[CatalogDescription], [t4].[Instructions], [t4].[rowguid] AS [rowguid3], [t4].[ModifiedDate] AS [ModifiedDate3]
FROM [Production].[Product] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[ProductSubcategoryID], [t1].[ProductCategoryID], [t1].[Name], [t1].[rowguid], [t1].[ModifiedDate]
FROM [Production].[ProductSubcategory] AS [t1]
) AS [t2] ON [t2].[ProductSubcategoryID] = [t0].[ProductSubcategoryID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t3].[ProductModelID], [t3].[Name], [t3].[CatalogDescription], [t3].[Instructions], [t3].[rowguid], [t3].[ModifiedDate]
FROM [Production].[ProductModel] AS [t3]
) AS [t4] ON [t4].[ProductModelID] = [t0].[ProductModelID]
WHERE ((
(CASE
WHEN [t0].[WeightUnitMeasureCode] = @p0 THEN CONVERT(Decimal(29,5),[t0].[Weight])
ELSE [t0].[Weight] / @p1
END)) >= @p2) AND ((
(CASE
WHEN [t0].[WeightUnitMeasureCode] = @p3 THEN CONVERT(Decimal(29,5),[t0].[Weight])
ELSE [t0].[Weight] / @p4
END)) <= @p5)
-- @p0: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [lb]
-- @p1: Input Decimal (Size = 0; Prec = 29; Scale = 5) [453.59237]
-- @p2: Input Decimal (Size = 0; Prec = 34; Scale = 5) [2]
-- @p3: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [lb]
-- @p4: Input Decimal (Size = 0; Prec = 29; Scale = 5) [453.59237]
-- @p5: Input Decimal (Size = 0; Prec = 34; Scale = 5) [3]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
Here is a screen shot of the control in action:
Weight Filter Control Example: Weight.zip
