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)