Proper way to cache entities from cached query with NHibernate

Despite the Profile entity is cached, it's possible that the CityFk property below cannot get a cached City and it will get the City from the database instead. Causing N + 1 problem.

public static async Task<ProfileDto> GetProfileDto(IDdd ddd, int userId)
{
    var profile = await ddd.GetAsync<Profile>(userId);

    var user = profile?.User ?? await ddd.GetAsync<IdentityDomain.User>(userId);
                
    var dto = new ProfileDto
    {
        Username        = user.UserName,
        AddressLine1    = profile?.AddressLine1,
        AddressLine2    = profile?.AddressLine2,
        StateFk         = profile?.City?.State?.Id ?? 0,
        CityFk          = profile?.City?.Id ?? 0,
        ZipCode         = profile?.ZipCode,
        Email           = user.Email,
        Telephone       = profile?.Telephone,
        MobilePhone     = profile?.MobilePhone
    };

    return dto;
}

This code produces City and State list.

The citiesQuery is cached, but it can not produce cache for City entities.

public static async Task<IEnumerable<StateCityDto>> GetListWithStateAsync(IDdd ddd)
{    
    var citiesQuery =
        from c in ddd.Query<City>().FetchOk(fc => fc.State)
        select new
        {
            Id = c.Id,
            Name = c.Name,
            StateId = c.State.Id,
            StateName = c.State.Name
        };


    var citiesList = await citiesQuery.CacheableOk().ToListAsyncOk();

    var distinctStates =
        citiesList.Select(x => new { x.StateId, x.StateName }).Distinct();


    var stateCityListDto =
        from state in distinctStates
        orderby state.StateName
        select new StateCityDto
        {
            Id = state.StateId,
            Name = state.StateName,
            Cities = (
                from city in citiesList
                where city.StateId == state.StateId
                orderby city.Name
                select new CityDto { Id = city.Id, Name = city.Name }
            ).ToList()
        };


    return stateCityListDto.ToList();
}

After saving the Profile:

NHibernate: 
     UPDATE
         job.profile 
     SET
         city_fk = :p0 
     WHERE
         user_fk = :p1;
     :p0 = 999 [Type: Int32 (0:0:0)], :p1 = 1 [Type: Int32 (0:0:0)]

When GetProfileDto is called again:

NHibernate: 
     SELECT
         profile0_.user_fk as user1_1_0_,
         profile0_.address_line_1 as address2_1_0_,
         profile0_.address_line_2 as address3_1_0_,
         profile0_.city_fk as city4_1_0_,
         profile0_.zip_code as zip5_1_0_,
         profile0_.telephone as telephone6_1_0_,
         profile0_.mobile_phone as mobile7_1_0_ 
     FROM
         job.profile profile0_ 
     WHERE
         profile0_.user_fk=:p0;
     :p0 = 1 [Type: Int32 (0:0:0)]
 NHibernate: 
     SELECT
         city0_.id as id1_0_0_,
         city0_.name as name2_0_0_,
         city0_.state_fk as state3_0_0_ 
     FROM
         job.city city0_ 
     WHERE
         city0_.id=:p0;
     :p0 = 999 [Type: Int32 (0:0:0)]

ProfileDto should get be able to get the City from cached City entities. The SQL log shows otherwise though.

The citiesQuery query in GetListWithStateAsync above, despite the query is cached, that query cannot produce the cache for City entities.

To make GetListWithStateAsync's citiesQuery produce cache for City entities, don't select individual fields of City from the query, to wit:

public static async Task<IEnumerable<StateCityDto>> GetListWithStateAsync(IDdd ddd)
{
    // The citiesQuery is cached. And it also produces cache for City entities.
    var citiesQuery = ddd.Query<City>().FetchOk(c => c.State);
                            
    var citiesList = await citiesQuery.CacheableOk().ToListAsyncOk();

    var distinctStates =
        citiesList.Select(x => new 
            { 
                StateId = x.State.Id, 
                StateName = x.State.Name
            }).Distinct();


    var stateCityListDto =
        from state in distinctStates
        orderby state.StateName
        select new StateCityDto
        {
            Id = state.StateId,
            Name = state.StateName,
            Cities = (
                from city in citiesList
                where city.State.Id == state.StateId
                orderby city.Name
                select new CityDto { Id = city.Id, Name = city.Name }
            ).ToList()
        };


    return stateCityListDto.ToList();
}

After saving the Profile:

NHibernate: 
     UPDATE
         job.profile 
     SET
         city_fk = :p0 
     WHERE
         user_fk = :p1;
     :p0 = 992 [Type: Int32 (0:0:0)], :p1 = 1 [Type: Int32 (0:0:0)]

When GetProfileDto is called again:

NHibernate: 
     SELECT
         profile0_.user_fk as user1_1_0_,
         profile0_.address_line_1 as address2_1_0_,
         profile0_.address_line_2 as address3_1_0_,
         profile0_.city_fk as city4_1_0_,
         profile0_.zip_code as zip5_1_0_,
         profile0_.telephone as telephone6_1_0_,
         profile0_.mobile_phone as mobile7_1_0_ 
     FROM
         job.profile profile0_ 
     WHERE
         profile0_.user_fk=:p0;
     :p0 = 1 [Type: Int32 (0:0:0)]

Without selecting the individual fields of the city in GetListWithStateAsync, the GetProfileDto is now able to get the city from the cached cities produced by cached city query.



Happy coding!