LINQ Tip of the week: LINQ and Fetch Plans

Today’s post will discuss one of the most interesting topics in LINQ to OpenAccess ORM and that is of course the proper use of the Fetch Plans functionality. If someone still wonders why Fetch Plans are so interesting when we talk about LINQ the answer is simple – they can boost the performance of your queries in times!
When one decides to use a fetch plan he/she always defines one or more fetch groups that are going to be used as part of it. Those fetch groups are containing only the fields that we want to select when we select objects from our class. That has one gotcha though.
Consider the following scenario: We have a person class with 26 fields and we only wish to select the Id, LastName and SSN in our query. That is usually followed by us defining a fetch group, for example “MyFetchGroup” that contains only those three fields.

You then write your query that selects all the Person objects from the database that returns all the person objects, and you want them ordered by their age.

If one opens the SQL Server Profiler (or hooks up to the OpenAccess query tracing mechanism) he/she will see a SELECT query in SQL which selects all the 26 columns from the person table. The fetch plan did not do its job. Well, here is the gotcha: while processing the LINQ Expression tree and transforming it to SQL query OpenAccess uses all the fields that compose that query. In this case, accessing the “orderby” part of the query, means accessing the “age” field of the Person class.

This triggers the “lazy-loading” mechanism to fetch this field from the database. Since this field is not part of our desired fetch group, it is fetched using the “Default Fetch Group” which by itself makes OpenAccess to load all the remaining data about a particular entity.

What is the solution to this hurdle?. When using fetch plans, one should not only think about what does he/she want to select within an entity but also about the fields that will be used for filtering, ordering etc..
So after adding the “age” field to the “MyFecthGroup” fetch group, our fetch plan works as expected and reduces the load of the database server for a significant rate.
For greater maintainability of your code you could even use a pattern where you have a “SelectFetchGroup” ,“FilterFetchGroup” and “OrderFetchGroup” which will be applied on the fields that could be used for the appropriate operations. That way you could add the necessary fetch groups to your fetch plan before executing different kinds of queries.

Here is an example of a query selecting the same data that we intended to select in the above sample but in this case we want to select only the persons younger than 50:

It is more than obvious that some of you are aware of how things work with the fetch plans.We know that we should provide more information regarding this excellent feature of Telerik OpenAccess ORM  so stay tuned.
For all the others that are new to OpenAccess or have not yet used the combination of LINQ and Fetch Plans for data querying, we hope we have saved some hours of debugging, profiling and tracing queries on your side.