Eager loading custom entity

Hello,

I am on version 3.70

I added a new custom attribute called GridAttribute to the Product data model. This attribute is just used to display additional data on the product details page and is populated in the database via a stored procedure.

I added the appropriate data objects and database tables, modified the Product class to work with this (using this as a guide: http://docs.nopcommerce.com/pages/viewpage.action?pageId=1442499), and it all works fine. I am able to access the product's GridAttributes from the Views and display the data.

However, all the products are grouped together and only the Group Product pages are usable. Each group can have anywhere from 10 to 100 products in it and each of those products can have 3 to 10 GridAttributes. This results in the database getting hit by a lot of individual queries.

I would like to force eager loading when getting the initial group Product to join in all of its sub-Products and their GridAttributes right off the bat instead of lazy loading them later, but I am not sure where or how to implement this. I know there is IQueryable.Include(), but I haven't found where that can be done within NopCommerce's framework.

Ideally this eager loading would only happen on the product details page and not on any category, search, etc. pages.

I appreciate any help I could get with this.
Thanks!

So I was able to figure out how to do this. I don't know if its the best way, but I was able to reduce the loading time of some of my larger product sets from ~5 seconds and over 1000 queries to ~0.5 seconds and around 12 queries.
Here is how I did it for anyone that's wondering:

Entity Framework Relationships
First I had to make Entity Framework aware of the relationships in the data model. In Nop.Core/Domain, I added the following to the Products class:

private ICollection<ProductGridAttributeValue> _productGridAttributeValue;
private ICollection<Product> _childGroupedProducts;

public virtual ICollection<ProductGridAttributeValue> ProductGridAttributeValues
{
  get { return _productGridAttributeValue ?? (_productGridAttributeValue = new List<ProductGridAttributeValue>()); }
  protected set { _productGridAttributeValue = value; }
}

public virtual Product ParentGroupedProduct
{
  get;
  protected set;
}

public virtual ICollection<Product> ChildGroupedProducts
{
  get { return _childGroupedProducts ?? (_childGroupedProducts = new List<Product>()); }
  protected set { _childGroupedProducts = value; }
}

Note: I could have and probably should have called ChildGroupedProducts AssociatedProducts.
Note 2: I had made two classes and associated database tables called ProductGridAttribute and ProductGridAttributeValue to store the extra data I needed.

Then I added the mappings in Nop.Data/Mapping:

In ProductMap.cs:

this.HasRequired(p => p.ParentGroupedProduct).WithMany(p => p.ChildGroupedProducts);

In ProductGridAttributeValueMap:

this.HasRequired(pgav => pgav.Product).WithMany(p => p.ProductGridAttributeValues);

Custom ProductService
I implemented a new method in Nop.Service's ProductService.cs to replace ProductService.GetProductById():

private const string DETAILED_PRODUCTS_BY_ID_KEY = "Nop.product.detailed.id-{0}";

public Product GetDetailedProductById(int productId)
{
  if (productId == 0)
    return null;

  string key = string.Format(DETAILED_PRODUCTS_BY_ID_KEY, productId);
  return _cacheManager.Get(key, () => _productRepository.TableNoTracking
              .Where(p => p.Id == productId)
              .IncludeProperties(p => p.ChildGroupedProducts
                .Select(c => c.ProductGridAttributeValues
                  .Select(v => v.ProductGridAttribute)),
              p => p.ChildGroupedProducts
                .Select(c => c.ProductAttributeMappings
                  .Select(m => m.ProductAttributeValues)),
              p => p.ChildGroupedProducts
                .Select(c => c.ProductAttributeMappings
                  .Select(m => m.ProductAttribute))
              )
            ).FirstOrDefault();


}

Note: You'll also have to define this method in IProductService


Custom ProductController.PrepareProductDetailsPageModel()
I wrote my own custom PrepareProductDetailsPageModel method to build the ProductDetailsModel object. A lot of it was a direct copy from the original method with a bit of tweaks. For the site I'm building, we don't need all the features build into NopCommerce so some sections I left out and others have hard-coded values.

My goal here was to build the ProductDetailsModel object with all the data I needed in my view without trying to access any data that wasn't already eagerly loaded (therefore avoiding unneeded hits to the DB).

Some of the changes I made:

// Only the parent product has an SeName defined, so I moved this out of
// the new ProductDetailsModel statement so we don't do it for each child product.
if (!isAssociatedProduct)
{
  model.SeName = product.GetSeName();
}

// Changed this bit at the beginning of the Attributes section so that we
// used the data already eagerly loaded instead of querying the database for
// each child product.
//
// Original looked like:
// productAttributeMapping = _productAttributeService.GetProductAttributeMappingsByProductId(product.Id);

ICollection<ProductAttributeMapping> productAttributeMapping = null;
productAttributeMapping = product.ProductAttributeMappings;

// When loading AttributeValues for each AttributeMapping, use the eagerly loaded
// data instead of querying for each mapping for each child product.
//
// Original looked like:
// var attributeValues = _productAttributeService.GetProductAttributeValues(attribute.Id);

var attributeValues = attribute.ProductAttributeValues;

// Get associated products from eagerly loaded data instead of querying the database
//
// Original looked like:
// var associatedProducts = _productService.GetAssociatedProducts(product.Id, _storeContext.CurrentStore.Id);

// Note: Eager Loading query doesn't sort associated products properly, so sort the list in code here
var associatedProducts = product.ChildGroupedProducts.OrderBy(c => c.DisplayOrder);

If anyone has a better way, or more "Proper (tm)" way of doing this, I'd love to learn.

wow, great solution. Thats what I was wondering. thanks a lot for sharing