Details on T-SQL Auto-Generation Using Entity Framework with Code Effects

This topic assumes that you already have a good understanding of rule creation in Code Effects. In Rule-Based Data Filtering, evaluation type rules are called filters, and Execution type rules are not used. All references to specific filters in this topic suggest that someone previously created and saved those filters as XML documents using the Rule Editor.

Using Code Effects, developers can add rule-based data filters (where clauses) of any complexity to their LINQ queries. Code samples in this topic demonstrate several ways of applying evaluation type filters to LINQ to Entity select queries. See our demo projects for implementation examples.

To begin, let's consider a simple source object called TestClass:

public class TestClass
{
	public int Id { get; set; }
}

We can create a test data array of the TestClass type and filter it against a test filter:

// Assuming that the filter is
// "Get items where Id is less than [3]"
string filterXml = GetThatFilterXmlFromSomewhere();
 
// Create test data
TestClass[] array = new[]
        {
            new TestClass { Id = 1 },
            new TestClass { Id = 2 },
            new TestClass { Id = 3 },
            new TestClass { Id = 6 },
            new TestClass { Id = 8 }
        };
 
var result = array.AsQueryable<TestClass>().Filter(filterXml);
 
foreach(var item in result)
{
	Console.WriteLine(item.Id);
}
// Produces:
// 1
// 2

The result variable is of type IQueryable<TestClass>, which holds a where clause with a lambda expression. This expression is available for further manipulation and is compiled and executed in the foreach iterator (delayed execution).

This was a quick example of using the Code Effects' Filter() extension method with LINQ to Object, but the real advantage of Code Effects' Rule-Based Data Filtering is demonstrated by applying filters to Entity Framework queries. The job of the LINQ to Entity provider is to convert all expressions into SQL statements and execute them on the database server. Because the Filter extension holds a proper expression, the provider converts the filter into the proper SQL where clause, runs the resulting statement on the server, and returns the already filtered result set instead of retrieving all data first and then filtering the result set in memory.

Consider the following example (using Microsoft's AdventureWorks database):

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;
 
// Assuming that the filter is
// 'Get records where Product.Name starts with "A"'
 
string filterXml = GetProductFilterXml();
 
var products = db.Products.Filter(filterXml);
 
foreach(var product in products)
	Console.WriteLine("{0,3}: {1}", product.ProductID, product.Name);
 
/* Produces the following output
 
SELECT [t0].[ProductID], [t0].[Name], ...
FROM [Production].[Product] AS [t0]
WHERE [t0].[Name] LIKE @p0
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [A%]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
 
	1: Adjustable Race
	879: All-Purpose Bike Stand
	712: AWC Logo Cap
*/

(The job of the imaginary GetProductFilterXml method is to return the XML of a filter that was previously saved somewhere using the Rule Editor)

The provider is even smart enough to create joins if we change fields in the filter:

// Assuming that the filter is
// 'Get records where ProductModel.Name starts with "A"'
 
string filterXml = GetProductFilterXml();
 
var products2 = db.Products.Filter(filterXml);
 
foreach(var product in products2)
	Console.WriteLine("{0,3}: {1}", product.ProductID, product.Name);
 
/* Produces the following output:

SELECT [t0].[ProductID], [t0].[Name], ...
FROM [Production].[Product] AS [t0]
LEFT OUTER JOIN [Production].[ProductModel] AS [t1] ON
	[t1].[ProductModelID] = [t0].[ProductModelID]
WHERE [t1].[Name] LIKE @p0
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [A%]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
 
879: All-Purpose Bike Stand*/

Note, however, that if we decide to include the product model’s Name in the output, it will generate an extra select statement to retrieve the missing information:

foreach(var product in products2)
	Console.WriteLine("{0,3}: {1} ({2})",
		product.ProductID, product.Name, product.ProductModel.Name);
 
/* Produces following output
 
SELECT [t0].[ProductID], [t0].[Name], ...
FROM [Production].[Product] AS [t0]
LEFT OUTER JOIN [Production].[ProductModel] AS [t1] ON
	[t1].[ProductModelID] = [t0].[ProductModelID]
WHERE [t1].[Name] LIKE @p0
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [A%]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
 
SELECT [t0].[ProductModelID], [t0].[Name], [t0].[CatalogDescription],
	[t0].[Instructions], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Production].[ProductModel] AS [t0]
WHERE [t0].[ProductModelID] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [122]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
 
879: All-Purpose Bike Stand (All-Purpose Bike Stand)
*/

This is because we are filtering and iterating over products that do not include joined properties, such as ProductModel. Obviously, having a select statement for each product record is very inefficient. One way to solve this problem is to include the necessary field in the query:

var products3 = from product in db.Products
			select new
			{
				product.ProductID,
				product.Name,
				product.ProductModel
			};
 
foreach(var product in products3.Filter(filterXml))
	Console.WriteLine("{0,3}: {1} ({2})",
		product.ProductID, product.Name, product.ProductModel.Name);

/* Produces the following output:
 
SELECT [t0].[ProductID], [t0].[Name], [t2].[test], [t2].[ProductModelID],
	[t2].[Name] AS [Name2], [t2].[CatalogDescription],
	[t2].[Instructions], [t2].[rowguid], [t2].[ModifiedDate]
FROM [Production].[Product] AS [t0]
LEFT OUTER JOIN (
	SELECT 1 AS [test], [t1].[ProductModelID], [t1].[Name],
		[t1].[CatalogDescription], [t1].[Instructions],
		[t1].[rowguid], [t1].[ModifiedDate]
	FROM [Production].[ProductModel] AS [t1]
	) AS [t2] ON [t2].[ProductModelID] = [t0].[ProductModelID]
WHERE [t2].[Name] LIKE @p0
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [A%]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
 
879: All-Purpose Bike Stand (All-Purpose Bike Stand)
*/

This is much better, but now the query looks somewhat convoluted. That’s due to a nullable join to ProductModel as a record. We can further optimize this by selecting field names specifically:

var products4 = from product in db.Products
			select new
			{
				product.ProductID,
				product.Name,
				ModelName = product.ProductModel.Name
			};
 
foreach(var product in products4.Filter(filterXml))
	Console.WriteLine("{0,3}: {1} ({2})",
		product.ProductID, product.Name, product.ModelName);

Unfortunately, this will produce an exception because the filter evaluates a collection of anonymous types that do not have the ProductModel property. Other than modifying the filter itself by replacing <property name="ProductModel.Name"> with <property name="ModelName"> in the filter's XML (which we could do), the easier solution is to simply apply the filter to the db.Products collection, and then run a query on it. This is possible since the Filter extension returns an expression which does not get evaluated until later (foreach, Count(), ToArray(), etc.), so the following will work nicely:

var products5 = from product in db.Products.Filter(filterXml)
			select new
			{
				product.ProductID,
				product.Name,
				ModelName = product.ProductModel.Name
			};
 
foreach(var product in products5)
	Console.WriteLine("{0,3}: {1} ({2})",
		product.ProductID, product.Name, product.ModelName);
 
/* Produces the following output:
 
SELECT [t0].[ProductID], [t0].[Name], [t1].[Name] AS [ModelName]
FROM [Production].[Product] AS [t0]
LEFT OUTER JOIN [Production].[ProductModel] AS [t1] ON
	[t1].[ProductModelID] = [t0].[ProductModelID]
WHERE [t1].[Name] LIKE @p0
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [A%]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
 
879: All-Purpose Bike Stand (All-Purpose Bike Stand) 
*/

Obviously, there are restrictions on the types of operations that can be performed in this manner. Most are limitations of LINQ providers, especially around case sensitivity. The Code Effects team continues to work on the product to make it as robust as possible.