locked
Walking relations RRS feed

  • Question

  • Hi,

    I am still a bit puzzled on how to solve transitive relations. For instance, if I have relations like these:

    Product 1----* PurchaseDetails *----1 Customer

    How would I be able to design a screen that has Products on one side and all related Customers on the other without using the intermediate PurchaseDetails? What I would be interested in are all Customers that bought a specific Product once, regardless of any intermediate details.

    Thanks in advance,
    Manuel

    Wednesday, August 25, 2010 10:09 AM

Answers

  • Hi Manuel,

    Great question.  Here's how it's done:

    1. Create an Editable Grid screen for Products.
    2. In the member list (on the far left pane of the screen designer), click the "Add PurchaseDetails" link (or whatever the name of the relationship to PurchaseDetails is).  This adds a new query to the member list.  This query returns all the purchase details for the selected product.
    3. In the main content tree for the screen, you'll see an "Add" drop-down button.  Click that and select PurchaseDetailsCollection (or whatever the name is of the query that you just added).  This binds that query to a DataGrid.  Within the DataGridRow node, you'll see all of the members of the PurchaseDetails entity list.  One of those will be Customer.  If you remove everything except Customer, you'll only see the Customer displayed in the grid.  If necessary, you can customize the grid further to show more details of the Customer.  To do this, click that Add drop-down button and select "Other Screen Data...".  This will open a dialog that lets you type a property path to the data that you want to display in the grid as a column, starting from the PurchaseDetails entity.  So you could type "Customer.HomeAddress" if you wanted to show the customer's address on the screen, for example.

    Hope that helps.

    Wednesday, August 25, 2010 12:57 PM
  • Oh, right.  Since there are many PurchaseDetails for each Product, you'll potentially see duplicate Customers for each Product listed.  In this case, you won't be able to rely on the implicit relationship queries (due to the nature of the 1:m).   You'll need to create a custom query that does this and bind that to a screen grid or list.  This is going to use some of the advanced querying features.  Basically, what you can do is create a query for customers that returns those customers which have purchased the specified product.  Here are the steps for how to do this:

    1. Create a query for customers by right-clicking the Customers table in the Solution Explorer and selecting "Add Query".
    2. Name the query "CustomersByProduct".
    3. Click the "Add Parameter" button in the Parameters section of the query designer.
    4. Name the parameter ProductId and change its type to Int32.
    5. Since the logic for this query cannot be represented through the designer, you'll need to write the code for the query.  In the Write Code drop-down button in the command bar, select "CustomersByProduct_PreprocessQuery".
    6. Paste the following code into the generated method body:
      query = query.Where(c => c.PurchaseDetails.Any(pd => pd.Product.Id == ProductId));
      
      This is a LINQ statement that returns all customers which have any purchase details for the specified product.
    7. Now that we've got the query defined, we need to consume it in the screen.  Create an Editable Grid screen for Products.
    8. Next we need to add that query as a member of the screen.  Click the "Add Data Item..." button in the command bar at the top of the designer.  In the dialog, choose the Query radio button and select "CustomersByProduct" in the query list.  Click OK to close the dialog.
    9. Remember that ProductId parameter that was added to the query?  That needs to be bound to the currently selected product in the product grid.  So whenever you select a product, the query will be re-executed against the newly selected product.  This will update the grid displaying the customers for that product.  To do this, select "ProductId" in the Query Parameters for CustomersByProductCollection.  In the property sheet, enter the following for the Parameter Binding: "ProductCollection.SelectedItem.Id".  ProductCollection is the name of the query that is bound to the products grid on the screen (your name may be different so just replace that here if it is). 
    10. In the main content tree for the screen, you'll see an "Add" drop-down button.  Click that and select CustomersByProductCollection.  This binds that query to a grid on the screen.

    That should be it.  Now if you run the app, you'll see a unique list of customers that have purchased the selected product.

    • Marked as answer by Manuel Warum Monday, August 30, 2010 12:08 PM
    Thursday, August 26, 2010 12:51 PM

All replies

  • Hi Manuel,

    Great question.  Here's how it's done:

    1. Create an Editable Grid screen for Products.
    2. In the member list (on the far left pane of the screen designer), click the "Add PurchaseDetails" link (or whatever the name of the relationship to PurchaseDetails is).  This adds a new query to the member list.  This query returns all the purchase details for the selected product.
    3. In the main content tree for the screen, you'll see an "Add" drop-down button.  Click that and select PurchaseDetailsCollection (or whatever the name is of the query that you just added).  This binds that query to a DataGrid.  Within the DataGridRow node, you'll see all of the members of the PurchaseDetails entity list.  One of those will be Customer.  If you remove everything except Customer, you'll only see the Customer displayed in the grid.  If necessary, you can customize the grid further to show more details of the Customer.  To do this, click that Add drop-down button and select "Other Screen Data...".  This will open a dialog that lets you type a property path to the data that you want to display in the grid as a column, starting from the PurchaseDetails entity.  So you could type "Customer.HomeAddress" if you wanted to show the customer's address on the screen, for example.

    Hope that helps.

    Wednesday, August 25, 2010 12:57 PM
  • Oh, thank you. That works well. But I do have one follow-up question: what if I only wanted to display each related Customer instance at most once, i.e. only display unique values?
    Thursday, August 26, 2010 8:26 AM
  • Oh, right.  Since there are many PurchaseDetails for each Product, you'll potentially see duplicate Customers for each Product listed.  In this case, you won't be able to rely on the implicit relationship queries (due to the nature of the 1:m).   You'll need to create a custom query that does this and bind that to a screen grid or list.  This is going to use some of the advanced querying features.  Basically, what you can do is create a query for customers that returns those customers which have purchased the specified product.  Here are the steps for how to do this:

    1. Create a query for customers by right-clicking the Customers table in the Solution Explorer and selecting "Add Query".
    2. Name the query "CustomersByProduct".
    3. Click the "Add Parameter" button in the Parameters section of the query designer.
    4. Name the parameter ProductId and change its type to Int32.
    5. Since the logic for this query cannot be represented through the designer, you'll need to write the code for the query.  In the Write Code drop-down button in the command bar, select "CustomersByProduct_PreprocessQuery".
    6. Paste the following code into the generated method body:
      query = query.Where(c => c.PurchaseDetails.Any(pd => pd.Product.Id == ProductId));
      
      This is a LINQ statement that returns all customers which have any purchase details for the specified product.
    7. Now that we've got the query defined, we need to consume it in the screen.  Create an Editable Grid screen for Products.
    8. Next we need to add that query as a member of the screen.  Click the "Add Data Item..." button in the command bar at the top of the designer.  In the dialog, choose the Query radio button and select "CustomersByProduct" in the query list.  Click OK to close the dialog.
    9. Remember that ProductId parameter that was added to the query?  That needs to be bound to the currently selected product in the product grid.  So whenever you select a product, the query will be re-executed against the newly selected product.  This will update the grid displaying the customers for that product.  To do this, select "ProductId" in the Query Parameters for CustomersByProductCollection.  In the property sheet, enter the following for the Parameter Binding: "ProductCollection.SelectedItem.Id".  ProductCollection is the name of the query that is bound to the products grid on the screen (your name may be different so just replace that here if it is). 
    10. In the main content tree for the screen, you'll see an "Add" drop-down button.  Click that and select CustomersByProductCollection.  This binds that query to a grid on the screen.

    That should be it.  Now if you run the app, you'll see a unique list of customers that have purchased the selected product.

    • Marked as answer by Manuel Warum Monday, August 30, 2010 12:08 PM
    Thursday, August 26, 2010 12:51 PM
  • Matt - that example is fabulous!  Your whole product team is doing yeoman work to support us and help us

    understand and improve our use of LightSwitch.

    Thanks so much

    Thursday, August 26, 2010 1:27 PM
  • Paste the following code into the generated method body:
    query = query.Where(c => c.PurchaseDetails.Any(pd => pd.Product.Id == ProductId));
    
    This is a LINQ statement that returns all customers which have any purchase details for the specified product.

     

    Sorry Matt, but could you please explain this step (#6)?  I've been trying to do something similar to Manuel, but my understanding of LINQ is pretty limited.  Otherwise, your instructions are very clear and easy to follow.  Thanks!

    Wednesday, October 13, 2010 5:42 PM
  • Couldn't help but to ease drop. There is an example in the LightSwitch content that shows how to extend a query by using LINQ. The example uses the Adventureworks database. It returns all customers who purchased a specific product. Here is the example code if it helps:

    VB:

     Private Sub CustomersWhoBoughtProduct_PreprocessQuery _
            (ByVal ProductID As System.Nullable(Of Integer), _
             ByRef query As System.Linq.IQueryable(Of ExtendAQueryApp.Customer))

                query = From myCustomers In query
                        From myOrders In myCustomers.SalesOrderHeaders
                        From myOrderDetails In myOrders.SalesOrderDetails
                        Where myOrderDetails.Product.ProductID = ProductID
                        Select myCustomers


    End Sub

    C#

    partial void CustomersWhoBoughtProduct_PreprocessQuery
            (int? ProductID, ref IQueryable<Customer> query)
             {
                query = from myCustomers in query
                         from myOrders in myCustomers.SalesOrderHeaders
                         from myOrderDetails in myOrders.SalesOrderDetails
                         where myOrderDetails.Product.ProductID == ProductID
                         select myCustomers;
              }

    It's basically a customer query that accepts a product ID parameter. Then you extend the query in the PreprocessQuery method as Matt described above. In a screen, you can bind productID parameter to the selected item of a product collection on the screen.  That way when a user clicks a product in a list, all the customers who bought the product appear in another collection.

    The topic that contains the code example is How to: Extend a Query by Using Code. For more information about binding the query parameter to the selected item of a collection on a screen, see How to: Provide a Value to a Query Parameter.


    Norm Estabrook
    Wednesday, October 13, 2010 10:39 PM
  • Thank you Norm for your fast response.  I do understand what Matt is doing with his LINQ code, but I find myself confused with the code itself.  Your code samples bring up a great example of what has me confused.  The LINQ code you have posted is exactly like the LINQ code I have seen elsewhere. It is also very understandable since it looks a lot like the SQL statements I'm used to.  Matt's code, on the other hand, seems to be another form of the LINQ code and this "shorthand" method is proving difficult for me to decipher or modify.  Looking back, I realize that my post was horribly vague on what exactly had me confused.

     

    Even so, another thought strikes me looking at your code, Norm: does LightSwitch allow you to specify multiple sources?  In my experience, every time I try to put more than one entity into a From or use multiple From statements, as you have shown, LS complains that the "Definition of method 'SelectMany' is not accessible in this context."  Since LS queries only allow a single source, I had assumed that this was a design choice for LS rather than an error on my part.

    Thursday, October 14, 2010 4:50 PM