generate category tree structure json data?

Question

Category Table:

ID => int, primary key

CategoryName => varchar

ParentCategoryID => int , nullable

Generate following json data format:

[{
    "id":1,
    "name":"Root",
    "Edit":"<a href='Edit/id'>edit.png</a>",
    "Delete":"<a href='Delete/id'>delete.png</a>",
    "children":[{
        "id":2,
        "name":"Horoscope",
        "Edit":"<a href='Edit/id'>edit.png</a>",
        "Delete":"<a href='Delete/id'>delete.png</a>",
        "children":[{
            "id":21,
            "name":"Daily",
            "Edit":"<a href='Edit/id'>edit.png</a>",
            "Delete":"<a href='Delete/id'>delete.png</a>",
            "children":[{
                "id":211,
                "name":"Aries",             
                "Edit":"<a href='Edit/id'>edit.png</a>",
                "Delete":"<a href='Delete/id'>delete.png</a>"
            },{
                "id":212,
                "name":"Taurus",                
                "Edit":"<a href='Edit/id'>edit.png</a>",
                "Delete":"<a href='Delete/id'>delete.png</a>"
            }]
        },{
            "id":22,
            "name":"Weekly",            
            "Edit":"<a href='Edit/id'>edit.png</a>",
            "Delete":"<a href='Delete/id'>delete.png</a>",
            "children":[{
                "id":221,
                "name":"Gemini",                
                "Edit":"<a href='Edit/id'>edit.png</a>",
                "Delete":"<a href='Delete/id'>delete.png</a>"
            },{
                "id":222,
                "name":"Aries",             
                "Edit":"<a href='Edit/id'>edit.png</a>",
                "Delete":"<a href='Delete/id'>delete.png</a>"
            },{
                "id":223,
                "name":"Taurus",            
                "Edit":"<a href='Edit/id'>edit.png</a>",
                "Delete":"<a href='Delete/id'>delete.png</a>"
            }]
        }]
    },{
        "id":3,
        "name":"News",      
        "Edit":"<a href='Edit/id'>edit.png</a>",
        "Delete":"<a href='Delete/id'>delete.png</a>",
        "children":[{
            "id":31,
            "name":"Sports",            
            "Edit":"<a href='Edit/id'>edit.png</a>",
            "Delete":"<a href='Delete/id'>delete.png</a>"
        },{
            "id":32,
            "name":"Interantional",         
            "Edit":"<a href='Edit/id'>edit.png</a>",
            "Delete":"<a href='Delete/id'>delete.png</a>"
        },{
            "id":33,
            "name":"Entertaintment",            
            "Edit":"<a href='Edit/id'>edit.png</a>",
            "Delete":"<a href='Delete/id'>delete.png</a>"
        }]
    }]
}]

I have Jquery TreeGrid Plugins which needs above json format. How Can I generate above json data from category table of database and return json data in controller action. I have use entity framework for model.

Answer 1

I think you need to have a property named "Children" in your domain class like this,

Category {

public int Id{get;set;}
public string CategoryName{get;set;}
public int ParentCategoryID {get;set;}
public Category ParentCategory {get;set;}

public virtual IList<Category > Children{get;set;}
}

And then you can use some mapper like AutoMapper to map those models to your view model (which has properties same as you need it for json object)

Answer 2

I would not mix data and UI instructions to start with! It clutters the JSON message with redundant repetitive data. I think it's up to the client to decide where to put the data and how to display them, or otherwise you should send these instructions separately (e.g. as first part of the message).

That said,the easiest way to get this done is having your class definition like this:

class Category
{
    public int Id { get; set; }
    public string CategoryName { get; set; }
    public int? ParentCategoryID { get; set; }
    [ForeignKey("ParentCategoryID")]
    public virtual ICollection<Category> SubCategories { get; set; }
}

When you query Categories with ParentCategoryID == null, with lazy loading enabled, and serialize into JSON you'll see that all levels are included because for each Category a query is emitted to get its children.

Note that Category does not have a ParentCategory property because that may cause JSON serialization to fail because of circular references.

For serialization you could use JavaScriptSerializer if you're not in MVC API controllers.

BTW. The best way to do this would be to leave your domain model transport-ignorant and use a structure of CategoryDto objects.