Populating a list that contains an array using linq

Question

I am using visjs.net to display a schedule

I'm having trouble writing LINQ to populate a list that contains a property that is an array

Here is the visjs documentation visjs doco

and some example code visjs source code

Here is an abridged version of the sample in javascript. I need to populate this JSON from a MVC controller instead of javascript

  var groups = new vis.DataSet();

  groups.add([
    {
      id: 1,
      content: "Lee",
      nestedGroups: [11,12]
    }
  ]);

  groups.add([
    {
      id: 11,
      content: "cook",
    },
    {
      id: 12,
      content: "shop",
    }
  ]);

spoiler alert: I don't know how to populate the nestedGroups property

This is the class I built for it:

    public class TimelineGroups
    {
        public int id { get; set; }
        public string content { get; set; }
        public string className { get; set; }
        public string orderBy { get; set; }
        public string[] nestedGroups { get; set; }
    }

This is the controller method that populates it, excluding the nestedGroups property, and this works just fine:

    public JsonResult EmployeeGroups()
    {
        List<TimelineGroups> e = (
            from emp in db.Employees
            where emp.Emp_Status == 1 && emp.EmployeeRole.IsChargeable == "Y"
            orderby emp.EmpRole_ID, emp.Emp_Last_Name
            select new TimelineGroups()
            {
                id = emp.Emp_ID,
                content = emp.Emp_Name,
                orderBy = emp.EmpRole_ID.ToString(),
                className = "bte-" + emp.EmployeeRole.EmpRoleName
            }
            ).ToList();

        return Json(e, JsonRequestBehavior.AllowGet);
    }

This all works correctly but now I want to add nested groups.

But I don't know the LINQ required to 'pivot' the required data into an array inside a collection.

This is what I have so far:

public JsonResult EmployeeClientGroups()
{
    // First load the client list which are children of employees
    List<TimelineGroups> cg = (
        from sw in db.ScheduleWorks
        where sw.EndDate > DateTime.Now
        select new TimelineGroups()
        {
            id = sw.Employee.Emp_ID * 1000 + sw.CustomerId,
            content = sw.Customer.Customer_Name
        }).Take(1000).Distinct().ToList();

    // now load the employee list
    // This includes client children
    List<TimelineGroups> eg = (
        from sw in db.ScheduleWorks
        where sw.EndDate > DateTime.Now
        select new TimelineGroups()
        {
            id = sw.Employee.Emp_ID,
            // How do I populate this with sw.Employee.Emp_ID * 1000 + sw.CustomerId
            nestedGroups = // What magic do I put here?
            content = sw.Employee.Emp_Name,
        }).Take(1000).Distinct().ToList();

    // Add the employee groups to the client groups
    cg.AddRange(eg);

    return Json(cg, JsonRequestBehavior.AllowGet);

}

I need to get all the client ID's for a given Employee and "pivot" them into an array, suitable to post back as JSON

To make them unique and match the children id's, the value I need to pivot in is:

sw.Employee.Emp_ID * 1000 + sw.CustomerId

Can this be done inline in a LINQ statement?

Answer 1

So it turns out this was kind of answered here but I will document my solution

This is the code that I eventually used:

    public class TimelineGroups
    {
        public string id { get; set; }
        public string content { get; set; }
        public string className { get; set; }
        public string orderBy { get; set; }
        public List<string> nestedGroups { get; set; }
    }


        List<TimelineGroups> eg = (
            from sw in db.ScheduleWorks
            where sw.EndDate > DateTime.Now
            where sw.Employee.Emp_Status == 1

            group (sw.EmployeeId.ToString() + "." + sw.CustomerId.ToString())
            by new {
                id = sw.EmployeeId.ToString(),
                EmpName = sw.Employee.Emp_Name,
                EmpOrder = sw.Employee.EmpRole_ID.ToString()
            }

            into g

            select new TimelineGroups()
            {
                id = g.Key.id,
                // find the list of employee+client keys 
                nestedGroups = g.ToList(),
                content = g.Key.EmpName,
                orderBy = g.Key.EmpOrder
            })
            .Take(1000)
            .ToList();

• Note no Distinct
• The argument to group is the field holding the values that i want to "pivot"
• then the part after by is the actual 'key' that will be grouped on
• I needed to use a List() rather than an array (maybe I could've used ToArray though?)
• Once this is grouped into an intermediate variable g, you can use g.ToList() to get the 'pivoted' values

Hopefully this will help someone else one day, maybe even someone using the visjs library

@Tetsuya Yamamoto fiddle pointed me in the right direction