Use LINQ deferred execution
When working with the Sitefinity CMS API, developers usually make requests that eventually go to the database and fetch some data to display or work with. The Sitefinity CMS API hides to complexity of all this work and makes things seem easy. There is a lot going on behind the scenes, and you should understand the implications of calling the Sitefinity CMS API when you work with it, in order to avoid performance problems.
Sitefinity CMS uses the Open Access ORM for its primary data layer. Among other great features, this allows us to use LINQ for writing queries to the database. All Sitefinity CMS API calls that fetch lists of items actually return IQueryable objects. The only reason for this is that you as a Sitefinity CMS developer, can also use LINQ with the results.
While LINQ provides a very readable and easy way to execute queries in code (rather than manually write SQL), this readability is not its only advantage. An often misunderstood (and the cause for many performance issues) of LINQ is deferred execution. Deferred execution means that DB queries generated by LINQ are executed not when your code runs, but rather immediately before the results are needed.
To illustrate this, you will look at an example of binding a list of items and showing them in a repeater. It is a simple repeater showing the title of each news item. In your project, create a Web Form User Control.
EXAMPLE: For more information about the markup of the widget, see LINQDefferedExecution.ascx in Sitefinity SDK: documentation-samples on GitHub.
To bind this repeater to a collection of news items, you use the Sitefinity CMS API to set its data source.
EXAMPLE: For more information about the code-behind of the widget, see LINQDefferedExecution.ascx.cs in Sitefinity SDK: documentation-samples on GitHub.
The Get() method of the news items façade returns an IQueryable<NewsItem> object. This essentially represents the collection of news items from the database. However, the database query to retrieve those news items will not be executed when Get() is called. It will be executed, when the Repeater control needs to display the data and enumerates its datasource. If you inspect the news object while debugging the code above, the debugger will warn us about this.
What Microsoft attempt to say in the message about the Results View is “This is a query which hasn’t been executed yet, but if you want to see the result of it in debug mode, it will be executed.”
This concept of deferred execution exists to take advantage of database servers and their ability to execute queries. SQL and all DBMSes are specifically optimized for this, and you should offload as much work to them as possible, rather than relying on your web server. You should let LINQ only generate queries and pass them to the data layer, rather than manipulate lists in code.
A lot of developers are used to working with IList objects in their code. You used to do the same before LINQ was introduced. This is comfortable, because lists give a lot of options – they are dynamic and you can do anything with them – add items, iterate over them and delete items. However, when casting an IQueryable to an IList, there are almost always performance penalties. We can substitute the above call to get news items by appending a ToList() method.
There will be no difference in functionality, the Repeater will bind just fine. However, by adding the ToList() call, you have explicitly requested that the query be executed. This has resulted in copying the whole result in memory. Now all news items occupy space in our server’s RAM. The larger their number, the larger amount of memory consumed. This is why we should avoid working with IList objects, and use their IQueryable alternative wherever possible.
To remember this easily, you can use two rules of thumb:
- An IQueryable represents a query that is waiting to be executed
- An IList represents the result of a query, which has already been executed
The moral of the whole story is that you should never convert an IQueryable to an IList, unless you know why you need to do it and are aware of the consequences. The Sitefinity CMS API gives you a lot of control over the results it fetches from the database. Try and use the API methods, instead of retrieving a list and performing operations on it. You’ll save some memory on your server.
For more information about deferred execution, read the following resources: