Telerik blogs

Using user-defined methods in a LINQ query

May 11, 2010 Productivity 0 Comments


As you are aware, for a given LINQ query Telerik OpenAccess ORM tries to push the query parts to the server as far as possible. Doing so minimizes the amount of data fetched from the server.

For example, consider this very simple query that fetches all discontinued Product entities from the Northwind database.

var query = from p in scope.Extent<Product>()
            where p.Discontinued
            select p;
 
The generated SQL statement would similar to -
SELECT a.[ProductID] AS COL1, a.[CategoryID] AS COL2, a.[Discontinued] AS COL3, a.[ProductName] AS COL4, a.[QuantityPerUnit] AS COL5, a.[ReorderLevel] AS COL6, a.[SupplierID] AS COL7, a.[UnitPrice] AS COL8, a.[UnitsInStock] AS COL9, a.[UnitsOnOrder] AS COL10 FROM [Products] a WHERE a.[Discontinued] <> 0

 

Sometimes it is necessary to use certain user-defined methods that perform some business logic, as a part of the filter predicate. For example, consider the following method that calculates whether a product needs to be reordered.

public bool ReorderRequired()
{
    return unitsInStock <= reorderLevel; 
}

 

We could use this method in a LINQ query as follows, to obtain all products that need to be reordered - 

var query = from p in scope.Extent<Product>() 
            where  p.ReorderRequired() 
            select p;

Trying to execute this query would result in a ‘NotSupportedExcpetion’. The reason for this is that OpenAccess does not know anything about the user-defined method and what SQL should or could be generated on the server. This query can be written so that filter is applied to the in-memory query result

var query = (from p in scope.Extent<Product>() 
             select p).ToList().Where(p => p.ReorderRequired()); 

 

Now consider the case where we need to combine the above mentioned queries into 1 LINQ query - query for all discontinued products or products that need to be reordered. We could write a LINQ query as follows

 var query = from p in scope.Extent<Product>()
             where p.Discontinued || p.ReorderRequired()
             select p;
As previously described, the above query would again result in a ‘NotSupportedException’ while handling the ‘p.ReorderRequired()’ method call.
To handle this slightly more complex query we can split it into 2 LINQ queries to obtain the same desired result.
var part1 = from p in scope.Extent<Product>()
            where p.Discontinued 
            select p;

 

var part2 = (from p in scope.Extent<Product>()
             where !p.Discontinued select p).ToList().Where(p => p.ReorderRequired());

 

Here we use basic rules of logic to rewrite the OR clause. The first query returns all discontinued products whereas the second query fetches all non-discontinued products and calls the user-defined method on the in-memory instances.

The final result is obtained by concatenating the two query results.

var result = part1.Concat(part2);

 

Note that the condition in the user-defined method is trivial in this case and as such, can also be directly specified in the original LINQ query.

Telerik Data Access

Comments

Comments are disabled in preview mode.