NHibernate Second-Level Cache XOR Fetch

TL;DR

Don't use Fetch on entities that are already in the second level cache


Simulation:


If an entity(e.g., Country) needed from the main query (e.g., Order) are all already in second level cache(e.g., caused by displaying them in dropdowns, or pre-loaded at startup) and it infrequently changes, don't use Fetch on that entity:

var list = session.Query<Order>() 
           .Fetch(x => x.Country) // Remove this 
           .OrderBy(x => x.Country.CountryName); 

foreach (var item in list) 
{                      
    Console.WriteLine("Order Id: {0}\nOrder Date: {1}\nCountry: {2}", item.OrderId, item.OrderDate, item.Country.CountryName);                  
}

This is the query when there's a Fetch, inefficient:

SELECT  
     o.OrderId,  
     o.OrderDate,      
     c.CountryId, 
     o.Comment, 
     c.Country, 
     c.CountryName, 
     c.Population 
FROM  
    [Order] o 
LEFT JOIN 
    Country c 
        ON o.CountryId = p 
ORDER BY 
    c.CountryName 

Whereas if you remove Fetch..

var list = session.Query<Order>() 
           .OrderBy(x => x.Country.CountryName); 

foreach (var item in list) 
{                      
    Console.WriteLine("Order Id: {0}\nOrder Date: {1}\nCountry: {2}", item.OrderId, item.OrderDate, item.Country.CountryName);                  
} 

..it's less taxing for the database. Don't worry, you can still display the CountryName above, that's how awesome NHibernate second level caching is. Here's the resulting query:

SELECT  
     o.OrderId,  
     o.OrderDate,      
     c.CountryId, 
     o.Comment 
FROM  
     [Order] o 
LEFT JOIN 
     Country c 
         ON o.CountryId = c.CountryId
ORDER BY 
     c.CountryName 

Now, if you want to minimize the database load further, say you don't want to include the Order's comment, you can just select all the fields you need:

var list = session.Query<Order>().OrderBy(x => x.Country.CountryName).Select(x => new { x.OrderId, x.OrderDate, x.Country.CountryName });  

Though the above looks correct, the Order's comment is removed, the query bypassed the use of second level cache, and the resulting query is less efficient, int(CountryId) is more efficient than nvarchar(CountryName):

SELECT  
    o.OrderId,  
    o.OrderDate,          
    c.CountryName 
FROM  
    [Order] o 
LEFT JOIN 
    Country c 
        ON o.CountryId = c.CountryId
ORDER BY 
    c.CountryName 

And if you need the population, you need to add another column in Linq's Select, resulting to less efficient query:

SELECT  
     o.OrderId,  
     o.OrderDate,      
     c.CountryName, 
     c.Population 
FROM  
    [Order] o 
LEFT JOIN 
    Country c 
        ON o.CountryId = c.CountryId
ORDER BY 
    c.CountryName 

Here's the proper way to prevent over-selecting and at the same time maximizing the use of second-level cache:

var list = session.Query<Order>() 
           .OrderBy(x => x.Country.CountryName) 
           .Select(x => new { x.OrderId, x.OrderDate, Country = session.Get<Country>(x.Country.CountryId) });  
 
foreach (var item in list) 
{                      
    Console.WriteLine("Order Id: {0}\nOrder Date: {1}\nCountry: {2}\nPopulation: {3}", item.OrderId, item.OrderDate, item.Country.CountryName, item.Country.Population);                  
} 

The resulting query is very efficient, no over-select(e.g., Comment is not included) on main entity(Order), and the referenced entity's key is the only column included in the SELECT:

SELECT  
    o.OrderId,  
    o.OrderDate,      
    c.CountryId 
FROM  
    [Order] o 
LEFT JOIN 
    Country c 
        ON o.CountryId = c.CountryId
ORDER BY 
    c.CountryName 

Sample code: https://github.com/MichaelBuen/LocalizationWithFallbacksAndCaching

Happy Coding!