Using AsQueryable With Linq To Objects And Linq To SQL

AsQueryable is a method that allow the query to be converted to an instance of IQueryable. When you use AsQueryable operator on an existing query and apply further transformations such as applying a filter or specifying a sort order, those lambda statements are converted to Expression trees. Depending on the provider you are using,  expression trees will be converted into the domain specific syntax and than executed. In the case of Linq to SQL provider, the expression tree would be converted to SQL and executed on SQL server. However if you use AsQueryable operator on a query that does not implement IQueryable<T> and only implements IEnumerable<T>, than any transformations that you apply on the query would automatically fall back on IEnumerable<T> specification. What this means is by tagging a query with AsQueryable you get benefits of both Linq to SQL and Linq to object implementation. If your existing query happens to implement IQueryable, the query is converted to SQL by the Linq to SQL provider, otherwise the query is executed in memory in the form IL code.

This offers an excellent benefits in real word scenarios where you have certain methods on an entity that return an IQueryable of T and some methods return List<T>. But then you have business rule filter that needs to be applied on all the collection regardless if the collection is returned as IQueryable of T or IEnumerable of T. From a performance stand point, you really want to leverage executing the business filter on the database if the collection implements IQueryable otherwise fall back to apply the business filter in memory using Linq to object implementation of delegates.

In Figure 23, I have a method called TopSellingProducts which returns an IQueryable of products. I am defining that all products which have been ordered more than 50 times is considered our top products. Since the TopSellingProducts method returns an IQueryable, I have not executed the query, I have simply declared the query of how to get top selling products. I have another method, called MostProfitableProducts that returns List of Products which have a UnitPrice greater than 60. I arbitrarily came up with this rule and stated that any products which have a UnitPrice of more than 60 dollars is considered profitable to the company. Looking further in figure 23, I have another method called DisplayProducts which is responsible for displaying Products. One of the business rules states that we can only display products that has either quantity greater than 0 or the product is not a discontinued product. To apply our business filter, I am sending the result of both TopSellingProducts and MostProfitableProducts to our filter method called FilterNonDisplyableProducts.  FilterNonDisplayableMethods simply removes the products that violates our business rule discussed above. Notice that FilterNonDisplaybleProducts takes in an IEnumerable because this is the lowest common denominator that we can work and is implemented by all  generic collection in one form or another. Before applying the business filter I am converting the IEnumerable of T collection passed in as a parameter To IQueryable of T using AsQueryable operator. We are doing this because we want the filter to be applied on the database for collections that implement IQueryable. If the collection does not implement IQueryable, the filter is applied in memory. This way you can apply the business rule to both IQueryable and IEnumerable and get the benefits of both worlds.

Figure below shows the SQL capture when I execute the code above. Based on SQL query send, we can confirm that our business filter was applied on the database for TopSelling Products because it was using IQueryable where as for the MostProfitableProducts Collection, the business filter was applied in memory because it implemented only IEnumerable of T.