none
How to return hierarchical data from LINQ query RRS feed

  • Question

  • i have table where i store state & distric and relation ship maintain by ID & ParentID. the table structure is like

    DECLARE @StateDistrict TABLE (
          [ID] INT PRIMARY KEY
        , [Name] VARCHAR(50)
        , [parentID] INT
        )
    
    INSERT @StateDistrict (ID,[Name],[parentID])
    SELECT 1, 'JharKhand',0
    UNION SELECT 2, 'Rachi',1
    UNION SELECT 3,  'Bokaro',1
    UNION SELECT 4, 'WestBengal',0
    UNION SELECT 5,  'Midnapore',4
    UNION SELECT 6,  'Kolkata',4


    now i want to write LINQ query which display state first and then corresponding district. i got one searching goole but it seems linq query is not ok

    here is the query.

    private System.Collections.IEnumerable getStateDistrictList()
    {
        //SELECT ' -> ' + Districts.DistrictName, Districts.Id, Districts.StateId FROM Districts UNION SELECT States.StateName, -1 , States.Id FROM States ORDER BY Districts.StateId,Districts.Id
    
        return (
                    from Districts in db.Districts
                    select new
                    {
                        Column1 = (" -> " + Districts.DistrictName),
                        Id = Districts.Id,
                        StateId = Districts.StateId
                    }
                ).Union
                (
                    from States in db.States
                    select new
                    {
                        Column1 = States.StateName,
                        Id = (-1),
                        StateId = States.Id
                    }
                ).OrderBy(p => p.StateId).ThenBy(p => p.Id).ToList();
    }
    just see this query and you will see two result set return but there is no relation specified betwen state & district rather order by clause is used. so guide me what will be the right query and how to write LINQ for that. thanks
    Friday, November 22, 2013 2:32 PM

Answers

  • Hello,

    >>now i want to write LINQ query which display state first and then corresponding district. i got one searching goole but it seems linq query is not ok

    Does this mean that the query throw an error or the result is not what you want?

    If it throws an error, could you please share it with us?

    For hierarchical data query, I made a sample and please have a look at it below:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    var result = (from order in db.Orders
    
                                  select new { ID = order.OrderID, Name = order.OrderName }).Union
    
                    (from od in db.OrderDetails
    
                     select new { ID = od.OrderDetailID, Name = od.OrderDetailName }).OrderByDescending(p => p.ID).ToList();
    
                }
    

    The result:

    It is similar with yours.

    If the result is not what you want, could you please share the corresponding sql statement?

    With the sql statement, we can help you better.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 25, 2013 7:09 AM
    Moderator