First level of cache with query with joins

Hi,

This morning, I try to execute a query with some joins. This query is executed many times with the same unit of work. When I looked into my console log with logging enabled, I noticed that the query is executed against the database instead of seeking the data in first level of the cache.

Here is my query:

  public List<Models.BroadcastProduct> GetBroadcastProductList(int productTypeId)
  {
     var products = from x in _extranet.BroadcastProducts
                    join y in _extranet.BroadcastRelatedProductTypes on x.Id equals y.BroadcastProductId into tempTypes
                    from types in tempTypes.DefaultIfEmpty()
                    where x.ProductTypeId == productTypeId || types.LinkedProductTypeId == productTypeId
                    select x;

     return products.ToList();
  }

It is a normal behavior or I missed something?

Thank you.

This is normal behaviour. The first level cache stores entities, not the result sets of particular queries. So we still query the database to find out what entities to return, but the actual entity instances will be got from the cache rather than materialised from the data reader.

If you need to remember the result set of a query, you'll need to store it manually on a per-unit of work basis. Since you have a strong-typed unit of work, you could add a dictionary as an instance member in the partial class, and store the result sets in that.

Thank you Ivan. That's exactly what I have done.