Proper way to query a reference entity

When caching a query, especially reference entities, select the whole entity; otherwise, the cached query won't be able to cache the entities


A test:

[TestClass]
public class FailingTest
{
    [TestMethod]
    Test_if_theres_entity_cache_when_cacheable_query_didnt_select_the_whole_entity()
    {
        // Arrange
        var sf = Common.BuildSessionFactory();

        using (var session = sf.OpenSession())
        {
            var q = from c in session.Query<Company>().Cacheable()
                    select new CompanyDto { CompanyId = c.CompanyId, CompanyName = c.CompanyName, TinyLogo = c.TinyLogo };

            q.ToList();
        }


        // Act
        Mapper.NHibernateSQL = "";

        using (var session = sf.OpenSession())
        {
            session.Get<Company>(1); // this won't be able to get an entity cache from the cacheable query above
        }

        // Assert
        Assert.IsTrue(Mapper.NHibernateSQL == ""); // SQL will not be empty
    }
}

Output:

Test Name:    Test_if_theres_entity_cache_when_cacheable_query_didnt_select_the_whole_entity()
Test Outcome:    Failed
Result Message:    Assert.IsTrue failed.
NHibernate: 
    select
        company0_.company_id as col_0_0_,
        company0_.company_name as col_1_0_,
        company0_.tiny_logo as col_2_0_ 
    from
        Company company0_
NHibernate: 
    SELECT
        company0_.company_id as company1_2_0_,
        company0_.company_name as company2_2_0_,
        company0_.company_url as company3_2_0_,
        company0_.tiny_logo as tiny4_2_0_ 
    FROM
        Company company0_ 
    WHERE
        company0_.company_id=:p0;
    :p0 = 1 [Type: Int32 (0)]

As you can see, the cached-entity-aware Get was not able to obtain a cached entity from the cacheable query before it. Inefficient


Here's the recommended way:

[TestClass]
public class PassingTest
{
    [TestMethod]
    public void Test_if_theres_entity_cache_when_cacheable_query_didnt_select_the_whole_entity()
    {
        // Arrange
        var sf = Common.BuildSessionFactory();

        using (var session = sf.OpenSession())
        {
            var q = from c in session.Query<Company>().Cacheable()
                    select c;

            q.ToList().Select(c => new CompanyDto { CompanyId = c.CompanyId, CompanyName = c.CompanyName, TinyLogo = c.TinyLogo });
        }


        // Act
        Mapper.NHibernateSQL = "";

        using (var session = sf.OpenSession())
        {
            session.Get<Company>(1); // this will be able to get an entity cache from the queryable cache above
        }

        // Assert
        Assert.IsTrue(Mapper.NHibernateSQL == ""); // SQL will not be empty
    }
}

Output:

Test Name:    Test_if_theres_entity_cache_when_cacheable_query_didnt_select_the_whole_entity
Test Outcome:    Passed
NHibernate: 
    select
        company0_.company_id as company1_2_,
        company0_.company_name as company2_2_,
        company0_.company_url as company3_2_,
        company0_.tiny_logo as tiny4_2_ 
    from
        Company company0_

The session.Get<Company>(1) is able to obtain a cached entity from the cached query before it. Efficient


Happy Coding!