none
Linq using aliases with join RRS feed

  • Question

  •                 var ReportQuery = from c in context.Departments
    join o in context.Reports on c.DepartmentsKey equals o.DepartmentsKey
    select new
    {
    id = c.DepartmentsKey,
    text = c.Name,
    cls = c.ClassName,
    iconCls = c.IconClassName,
    children = c.ReportsList
    };

    TextResponse = JsonConvert.SerializeObject(ReportQuery.ToList());

    The statement above uses aliases for Departments but I don't know how to do it for ReportList which is one to many relationship. By putting children = c.ReportsList, exposed the child records but I couldn't rename them or choose which one to expose.
    This is the result I am getting with the statement above:

    [{
    "id":2,"text":"Accounting","cls":"forum-ct","iconCls":"forum-parent","children":
    [
    {"ReportsKey":12,"DepartmentsKey":2,"Name":"Combined Report","ClassName":"forum","IconClassName":"icon-forum","Leaf":true,"Departments":null},
    {"ReportsKey":22,"DepartmentsKey":2,"Name":"New POP Report","ClassName":"forum","IconClassName":"icon-forum","Leaf":true,"Departments":null}
    ]
    }]


    This is the result I would like to have:


    [{
    "id":2,"text":"Accounting","cls":"forum-ct","iconCls":"forum-parent","children":
    [
    {"id":12,"text":"Combined Report","cls":"forum","iconCls":"icon-forum","Leaf":true},
    {"id":22,"text":"New POP Report","cls":"forum",,"iconCls":"icon-forum","Leaf":true}
    ]
    }]

    Any help with the Linq code please?

    Thanks
    Wednesday, December 16, 2009 7:01 PM

All replies

  • I had to do it manually and here is the code:
    Hope this helps as I couldn't find a way of doing without iterating but it works.
                    var ReportQuery = (from c in context.Departments
                                       select c).ToList();
    
                    IList<Department> departments = new List<Department>();
    
                    for (int i = 0; i < ReportQuery.Count(); i++)
                    {
                        Department department = new Department();
                        department.children = new List<BusinessLayer.Report>();
                        department.cls = ReportQuery[i].ClassName;
                        department.expanded = ReportQuery[i].Expanded.Value;
                        department.iconCls = ReportQuery[i].IconClassName;
                        department.id = ReportQuery[i].DepartmentsKey.ToString();
                        department.text = ReportQuery[i].Name + " (" + ReportQuery[i].ReportsList.Count.ToString() + ")";
                        for (int j = 0; j < ReportQuery[i].ReportsList.Count; j++)
                        {
                            Report report = new Report();
                            report.cls = ReportQuery[i].ReportsList[j].ClassName;
                            report.leaf = ReportQuery[i].ReportsList[j].Leaf.Value;
                            report.iconCls = ReportQuery[i].ReportsList[j].IconClassName;
                            report.id = ReportQuery[i].ReportsList[j].ReportsKey.ToString();
                            report.text = ReportQuery[i].ReportsList[j].Name;
                            department.children.Add(report);
                        }
    
                        departments.Add(department);
                    }
    
                    TextResponse = JsonConvert.SerializeObject(departments);

    Wednesday, December 23, 2009 8:25 PM