Querying

LINQ to Ucommerce provides rich capabilities to query the data stores of Ucommerce, but with great power comes great responsibility. This exercise introduces the three levels of data APIs.


Relevant APIs

UCommerce.Infrastructure
OjbectFactory.Resolve<T>
UCommerce.EntitiesV2
IRepository<T>
.Select()
ISessionProvider
.GetSession()
UCommerce.EntitiesV2
Product
PurchaseOrder
.CreatedDate
NHibernate.Linq
EagerFetchingExtensionMethods.Fetch()
EagerFetchingExtensionMethods.FetchMany()

Hands-on

 

Query for orders created after a certain date.


Query for products promoted to the homepage

  • ProductProperties
  • ProductDefinitionField
  • “ShowOnHomepage”


Join product to order line on Sku and VariantSku

  • Anonymous type for join { orderline.Sku, orderline.VariantSku }

 

N+1

  • Query products
  • Foreach over all products
  • Access ParentProduct property in foreach
  • Observe behavior in SQL Profiler
  • How many queries do you see?

 

Eager Loading to Avoid N+1

  • Use Fetch on ParentProduct property on product to tell NHibernate to initialize
  • How many queries do you see?

 

Large Cartesian Products Avoided

  • Set up two queries to load product and variants
  • Use ToFuture on LINQ queries to defer execution
  • Execute one query
  • How many batches are executed in SQL Server (use the tuning template in profiler)

 

Total Control of SQL with HQL

  • Use ISessionProvider to create a query
  • Use keyword join, outer join, fetch to control SQL
  • Watch the resulting queries in SQL Profiler (use the tuning template in profiler)
{{lineitem.ProductName}}

{{lineitem.VariantName}} - {{lineitem.Quantity}} x {{lineitem.FormattedPrice}} {{lineitem.FormattedPrice}}

Your cart is empty ;(
Total {{basket.FormattedProductsTotal}}
Checkout