Filtering LINQ Queries Using Business Rules Engine

• Download Code Effects Rule Demo Filter MVC - 439.1 KB
• Download Code Effects Rule Demo Filter ASP - 377.1 KB

Introduction

If you are a seasoned programmer or an IT manager, chances are that at some point in your professional life you were involved in the development of a decision making system that used a business rules engine (BRE). BREs play a crucial role in many large industries such as banking, insurance, medical care, retail, and logistics. Even though the birth of business rules can be traced back to the 60's, the IT sector didn't sit still all that time. Instead, vendors continued to improve their engines by implementing new technologies and adding new features. This article discusses the use of one such new feature, namely, rule-based filtering of LINQ queries using Web Rule, the XML-based super-fast rules engine, implementable as an ASP.NET or MVC component.

Before diving into the main subject, let's quickly define what a BRE is and how it works. In short, the goal of a BRE is to evaluate an instance of a class (known as the source object, or the fact object, or just the facts) against a business rule. It then either processes that instance in accordance with the output of the rule, or delegates further processing to one or many of the rule's actions. Every rule represents a piece of business logic used by the organization. The main advantage of a BRE is that conditions of the rule (i.e. the business logic) can be changed without needing to change the system that uses that rule.

For example, consider the following business rule:

Check if Product.Type is Jeans and Product.Cost > 19.99

In this oversimplified business rule, the Product .NET class is a source object. The BRE uses its Type and Cost properties as rule fields. The output of this rule is True or False. This is an evaluation type rule. You can also define an execution type rule that looks like this:

If Product.Type is Jeans and Product.Cost > 19.99 then ApplyDiscount(10) else ReduceShipping(5)

This rule type uses the ApplyDiscount and ReduceShipping .NET methods as the rule's actions. Depending on the result of the evaluation, the engine invokes one or the other action, passing the parameter values set by the rule author.

Rule-Based Data Filtering

So, rules engines are great. But how can we relate them to data filtering? Look closely at the first rule. It presents a very interesting possibility. Imagine if we rewrite the rule as follows:

Select records where Product.Type is Jeans and Product.Cost > 19.99

From this angle, it's easy to see that our business rule can be interpreted as a where clause in a select statement. In fact, any business rule of evaluation type can be used as a data filter. That's the idea behind the new feature of the Web Rule engine called Rule-Based Data Filtering. Web Rule's unique UI allows the end user to create a business rule and apply it as a data filter to the select queries of virtually any established LINQ provider, all on a single ASP.NET page or MVC view. This is what it looks like from the end user's perspective:

This feature of Web Rule makes the creation of advanced search forms and ad hoc reports a breeze. Users can build advanced data filters and immediately apply them to a data source simply by selecting fields, operators and clauses from a series of context-sensitive menus. Web Rule even supports the use of parentheses to prioritize filter conditions:

The Internet is full of advanced search forms that use all sorts of HTML control combinations in order to allow the end user as much data search freedom as possible. With the introduction of Rule-Based Data Filtering, Web Rule adds a whole new host of possibilities to that process. A live demo that demonstrates this new feature can be found here.

How It Works

This article comes with ASP.NET and MVC demo projects. All code samples, database schema and Entity models used in this article are taken from the MVC demo project.

To build our advanced search form, we only need three things: a SqlServer database that contains some test data, an MVC web app with a view that hosts our search form, and a LINQ To Entity data model that represents the database. Web Rule also supports LINQ To SQL. Let's start with the database:

As you can see, it's a simple SqlServer 2008 database that contains a small chunk of test data. It also declares three SQL views: Orders, Products, and Customers. Here is the SQL for the Orders view:

CREATE VIEW [dbo].[Orders]
AS
SELECT
	dbo.[Order].OrderID,
	dbo.[Order].Sub,
	dbo.[Order].Tax,
	dbo.[Order].Freight,
	dbo.[Order].Sub +
		ISNULL(dbo.[Order].Tax, 0) +
		dbo.[Order].Freight AS Total,
	dbo.[Order].Placed AS DatePlaced,
	dbo.[Order].Paid AS DatePaid,
	dbo.[Order].Shipped AS DateShipped,
	dbo.Customer.FirstName AS CustomerFirstName,
	dbo.Customer.LastName AS CustomerLastName,
	dbo.Customer.CompanyName AS CustomerCompany,
	dbo.Customer.Email AS CustomerEmail,
	dbo.Customer.CellPhone AS CustomerCellPone,
	dbo.Product.ProductTypeID,
	dbo.Product.ColorID,
	dbo.Product.SizeID,
	dbo.Product.SKU AS ProductSKU,
	dbo.Product.[Description] AS ProductDescription,
	dbo.Product.[Weight] AS ProductWeight,
	dbo.Product.Cost AS ProductCost,
	dbo.Product.Manufactured AS ProductionDate,
	dbo.Manufacturer.Name AS ManufacturerName,
	dbo.Manufacturer.[Description] AS ManufacturerDescription,
	dbo.ProductType.Name AS ProductTypeName,
	dbo.Color.Name AS ProductColorName,
	dbo.Size.Name AS ProductSizeName
FROM
	dbo.[Order]
		INNER JOIN dbo.OrderProduct
			ON dbo.[Order].OrderID = dbo.OrderProduct.OrderID
		INNER JOIN dbo.Product
			ON dbo.OrderProduct.ProductID = dbo.Product.ProductID
		INNER JOIN dbo.Manufacturer
			ON dbo.Product.ManufacturerID = dbo.Manufacturer.ManufacturerID
		INNER JOIN dbo.Customer
			ON dbo.[Order].CustomerID = dbo.Customer.CustomerID
		INNER JOIN dbo.Color
			ON dbo.Product.ColorID = dbo.Color.ColorID
		INNER JOIN dbo.ProductType
			ON dbo.Product.ProductTypeID = dbo.ProductType.ProductTypeID
		INNER JOIN dbo.Size
			ON dbo.Product.SizeID = dbo.Size.SizeID;

Our Entity model will contain the Order class that represents this SQL view, as well as the Product and Customer classes for the remaining views. We'll use the Order class as the source object for our form. The SQL views are used for two reasons:

• In most cases, we don't want to search for data in just one table. SQL views are a great way to join data from multiple tables. Thankfully, the LINQ To Entity framework can work with views as if they were regular tables.
• The next major version of Web Rule will natively support the IEnumerable interface. That support will allow for the creation of filters that understand fields of collection types like in the following rule: Get records where Order.Customers.FirstName = "John" That will, in turn, allow for the joining of multiple tables by their FKs within the filter itself. The current version requires the use of SQL views if the statement uses data from multiple tables.

The Entity model looks like this:

Notice that we only need classes for SQL views and lookup tables. We don't even need to define PKs in our model because all lookup operations for fields such as ProductTypeID, ColorID and SizeID will be done using the Dynamic Menu Data Sources feature of Web Rule. This simplifies things on the server a bit.

Now that we have our model, we can define the MVC view that hosts our search form, the view model that holds the search results, and the controller that provides the search logic. Web Rule takes care of most of the plumbing, so you only need to add a small amount of code to the HTML in order to declare the filter editor on the web page:

@using System.Web.Mvc;
@using CodeEffects.Rule.Common;
@using CodeEffects.Rule.Mvc;

@model CodeEffects.Rule.Demo.Filter.Mvc.Models.SearchModel

@{
	Html.CodeEffects().Styles()
		.SetTheme(ThemeType.Gray)
		.Render();	
}

@using(Html.BeginForm("Index", "Test", FormMethod.Post))
{
	<div>
		@{
			Html.CodeEffects().RuleEditor()
				.Id("Filter")
				.Mode(RuleType.Filter)
				.Rule(Model.Rule)
				.Render();
		}
	</div>
	<table>
		<thead>
			<tr>

			@foreach(var header in Model.Headers)
			{
				<th>@header</th>
			}
			</tr>
		</thead>
		<tbody>
			@foreach (var cells in Model.Data)
			{
			<tr>

				@foreach(var cell in cells)
				{
					<td>@cell</td>
				}
			</tr>
			}
		</tbody>
	</table>

}

@{
	Html.CodeEffects().Scripts().Render();
}

First, the view declares the using directives and the type of the view model. It then adds the CSS theme used by the filter editor. Next comes the form declaration, where Test is the controller's name. Then, the declaration of the filter editor that takes some optional and required settings, followed by the table that displays the search results. And finally, the line of code that writes the client data needed for Web Rule to function properly. You can find more details on this implementation in this documentation article.

The controller declares two overloads of the Index action, POST and GET. The GET Index action only creates the view model with default values and the instance of the RuleModel that takes the type of the source object:

[HttpGet]
public ActionResult Index()
{
	SearchModel s = new SearchModel();
	s.Rule = RuleModel.Create(typeof(Order));
	return View(s);
}

The POST Index action contains the most interesting part - the actual search:

[HttpPost]
public ActionResult Index(FormCollection form, RuleModel Filter)
{
	// Create an instance of the view model
	SearchModel s = new SearchModel();

	// Check if the Search button was clicked
	if(form["Search"] != null)
	{
		// Tell Web Rule which source object (entity) to use
		Filter.BindSource(typeof(Order));

		// Pass the filter to the view model
		s.Rule = Filter;

		// Check if the filter is empty or invalid
		if(Filter.IsEmpty() || !Filter.IsValid())
		{
			return View(s);
		}

		// Get the grid headers (the method is declared elsewhere)
		s.Headers = GetHeaders();

		// Get the filters XML
		string filterXml = Filter.GetRuleXml();

		// Create the statement and apply the filter to it
		var orders = from o in new WebRuleLinqEntities().Orders.Filter(filterXml)
						select o;

		// Get distinct results and convert them into displayable data
		foreach(Order order in orders.ToList<Order>().Distinct<Order>(new EntityComparer()))
		{
			// The GetRow method is declared elsewhere
			List<string> values = GetRow(order);

			// Add the result to the model
			s.Data.Add(values);
		}
	}
	else
	{
		// The Search button was not clicked; just create the model
		s.Rule = RuleModel.Create(typeof(Order));
	}

	return View(s);
}

First, the action creates a new model. It then passes it the filter data that was received from the client as RuleModel type. Next, it checks whether the filter is empty and is valid. And finally, the action performs the actual search by creating the LINQ statement, applying the Filter extension method and passing it the filter's XML. Notice the use of the Distinct extension at the end of the data retrieval. Because we use data supplied by a SQL view, the result may contain duplicate records. Unfortunately, we can't just "distinct" the select statement of the view itself. The Distinct extension helps us to get rid of any unwanted records. This is a relatively small sacrifice for the convenience of such easy data filtering right within the statement.

Web Rule's Filter extension fully supports delayed execution; it won't load the entire table into the server's memory and filter it from there. The final select statement contains the actual where clause created by the Entity provider.

This new feature of Web Rule takes the use of business rules to a new level, allowing developers to create complex search forms and reporting tools with minimal code.

Happy programming!