none
Hierarchical Data Load / Dynamic Self-Joins / MultiLevel Data Load using Linq RRS feed

  • Question

  • I have a database table say Sales where I store parent child hierarchy in different formats as below sample data.  This is all by application architecture.

    We have varying level of high level structures to hold these values.
    I have a separate table to hold high level structure Level definition like (Region-Country-State-City ***4 levels**) and (BusinessDomain-Territory-City **3 levels**).  Now I want to load these levels as columns and to be able to report against that.  At one point in time end user would not want to see more than one hierarchy but levels in a single hierarchy as indicated above will all be different.

    I have successfully tried to write multilevel queries and some queries with string concatenation on stored proc and also c# side to generate queries at run time, however I want to be able to write similar queries in Linq to SQL to adhere to development standards.

    Well in short, I need help in writing a Linq to sql query with variable number of SELFJOINS, loading hierarchical level names as columns.

    ID    ParentID       Type                   Name  Amount
    1      NULL               Region               NA      1000000
    2      1                    Country              USA     10000
    3      2                    State                  CT           1000000
    4      2                    State                    CA           10000000
    5      3                    City                     Hartford      2000000
    6      3                    City                     Clinton         2323232  
    7      NULL               BusDomain              Sales     1000000
    8      7                    Territory                      NJ     1000008
    9      8                    City                         Matawan           1000000
    11      NULL               Region                      Australia   1000000
    12      11                    Country                    Australia    10000
    13      12                    State                         NB            1000000
    14      12                    State                         NSW           1000000

    The reason behind loading data in this format is I use a Pivot viewer control from DevExpress to display this data further to end users.
    Each ID has its own value and is supposed to be displayed on pivot as end user expands the pivot of collapses it.


    Many thanks in advance. 

    Pritesh Ostwal
    Developer
    • Edited by Pritesh Ostwal Monday, April 20, 2009 9:02 PM Better explanation
    Monday, April 20, 2009 8:58 PM

Answers

  • LINQ to SQL has no support for loading hierarchical data so the only real option is to use TSQL either via a stored procedure or using Translate<T>.

    [)amien
    Tuesday, April 21, 2009 5:29 AM
    Moderator

All replies

  • LINQ to SQL has no support for loading hierarchical data so the only real option is to use TSQL either via a stored procedure or using Translate<T>.

    [)amien
    Tuesday, April 21, 2009 5:29 AM
    Moderator
  • LINQ to SQL has no support for loading hierarchical data so the only real option is to use TSQL either via a stored procedure or using Translate<T>.

    [)amien

    I will take this as answer for now.  No one else has responded so I will go with what I have already been doing, runtime Select statement generation.
    Developer
    Friday, May 1, 2009 1:08 PM