none
Need help querying ordersystem in LINQ for totals grouped by root category RRS feed

  • Question

  • Hey

    Let's start by giving you the layout of my tables.

    Orders -> OrderRows -> Product -> Category

    I am listing categories on my page and I also need to present the totalsales per category. The category table has a field named highercategoryID which tells the system which category a category might belongs to.

    But there is a couple of root categories and they have highercategoryID = 0 and I need to get all sales connected to that categoryID.

    So I get a list of root categories. But then I need to query the orderrows table joined to categories to find all products that belongs to a root level category and sum the sales in that category.

    If any of you can follow this please start tipping me of :)


    Andreas Kviby My English SharePoint Blog : http://itbloggen.se/cs/blogs/diaznet Please mark replies as answered if you find it has helped you Contact : a [.] kviby [at] hotmail [.] com Also make sure to look at www.sharepointcommunity.com & www.sharepointcommunity.se (Swedish)
    Monday, November 1, 2010 9:40 AM

Answers

  • Recursive queries are not natively supported by L2S, so your best bet (unless you want to do multiple roundtrips) is to either:

    a) Change the category table to include the root category id for each category within the category table

    ...or...

    b) Add a 'helper' many-to-many mapping table for mapping categories to all parent categories

    ...or...

    c) Create a table-valued function or view containing a common table expression recursive query that resolve the root-to-leaf relationship in the category hierarchy.

     

    ...after that you can write a linq query that groups by the root node, e.g.:

    from rc in dc.Categories
    join c in dc.Categories on rc.CategoryID equals c.RootCategoryID 
    join p in dc.Products on c.CategoryID equals p.CategoryID
    join or in dc.OrderRows on p.ProductID equals or.ProductID
    where rc.HigherCategoryID == 0 && ...etc...
    group or by rc into og
    select new { og.Key, TotalSales = og.Sum(o => o.SalesAmount) };

    ( sample query above based on option (a) )

     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Tuesday, November 2, 2010 3:35 AM
    Answerer

All replies

  • Hi Kviby,

    Could you please post the schema of the four tables ? It will be helpful for us to know the relationship and FK/PK fields of the tables better. Thanks.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 2, 2010 3:01 AM
    Moderator
  • Recursive queries are not natively supported by L2S, so your best bet (unless you want to do multiple roundtrips) is to either:

    a) Change the category table to include the root category id for each category within the category table

    ...or...

    b) Add a 'helper' many-to-many mapping table for mapping categories to all parent categories

    ...or...

    c) Create a table-valued function or view containing a common table expression recursive query that resolve the root-to-leaf relationship in the category hierarchy.

     

    ...after that you can write a linq query that groups by the root node, e.g.:

    from rc in dc.Categories
    join c in dc.Categories on rc.CategoryID equals c.RootCategoryID 
    join p in dc.Products on c.CategoryID equals p.CategoryID
    join or in dc.OrderRows on p.ProductID equals or.ProductID
    where rc.HigherCategoryID == 0 && ...etc...
    group or by rc into og
    select new { og.Key, TotalSales = og.Sum(o => o.SalesAmount) };

    ( sample query above based on option (a) )

     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Tuesday, November 2, 2010 3:35 AM
    Answerer
  • Hey

    I will try the option a today and see if that could be the best solution. It seem like a solid way of solving this issue.


    Andreas Kviby I connect .NET with iOS
    Wednesday, February 2, 2011 10:03 AM