Ok, so finally I have an e-commerce related post. Tonight I was adding cross-sells to my site. The concept is simple - when displaying a product, at the bottom of the product page, list products that might also be of interest to the shopper. To facilitate this, you'll need a Cross Sell table:
This table just contains two product id's. One is the product Id of the item the shopper is currently viewing, the other is the product Id of the product that you want to cross sell. So all you have to do is make a call out to your database by passing your product Id and return one or more products that you want to cross sell. Then display them.
In the past, I would have written a stored procedure to retrieve the data. Something like this:
But I'm moving away from stored procs in this new e-commerce site I'm working on, and using Linq to Sql. I have written hundreds on basic linq queries, but how do you accomplish this task? I have a select embedded inside a select. Well, here is the solution:
First grab your Cross Sell records and store them in a var called crossSells. Then create another query from your Products table, join to manufacturers (to get the manuf. name for dispay purposes), and set the where to get those items from crossSells that contain your productId. No matter how many "queries" you construct, only one SQL statement is executed. Your var is an IQueryable and you can embed these inside others. Very cool and very easy to get straight in your head when you seperate them.
Your SQL query isn't actually constructed and run until you enumerate over it (call .ToList() for example). In the above code, I want to return a generic list of Product objects. So, after creating my two linq statements, I return products.ToList();
The SQL that is created can be easily seen. Just set a breakpoint on the return statement and look products.
There you have it. Move away from stored procedures and into the cool world on Linq to SQL (or Linq to Entities).
Thanks to Rob Conery's blog for this information.