using Linq to break flat table results into object collection

Question

I have a database return result which has flatten results like below. I want to use Linq to break the flat results into primary classes with the items populating the primary class items property collection.

public class Result
{
 public string PrimaryKey { get; set; }
 public string Status { get; set; }
 public string ItemName { get; set; }
}

public class ObjectA
{
 public string PrimaryKey { get; set; }
 public string Status { get; set; }

 public List<Item> Items = new List<Item>();
}

public class Item
{
 public string Name { get; set; }
}

static void Main(string[] args)
{
 GetObjectAs();
}

static List<ObjectA> GetObjectAs()
{
 // this is our table results
 List<Result> results = new List<Result>();
 results.Add(new Result()
 {
  PrimaryKey = "1",
  Status = "Done",
  ItemName = "item1"
 });
 results.Add(new Result()
 {
  PrimaryKey = "2",
  Status = "Fail",
  ItemName = null
 });
 results.Add(new Result()
 {
  PrimaryKey = "3",
  Status = "Done",
  ItemName = "item2"
 });
 results.Add(new Result()
 {
  PrimaryKey = "3",
  Status = "Done",
  ItemName = "item3"
 });

 List<ObjectA> returnResults = new List<ObjectA>();

 // need to break into 3 ObjectA objects

 // ObjectA 1 needs an Item added to its Items collection with ItemName item1

 // ObjectA 2 has no items since the ItemName above is null

 // ObjectA 3 needs 2 Items added to its Items collection item2 and item3

 // return our collection
 return returnResults;
}

PS this is just sample code, I know you shouldn't expose a List as a public property and should return an IEnumerator instead of the actual List etc.

Answer 1

You can use GroupBy to group the results by the primary key, then you can operate on the subset of rows within the group to obtain the status (hopefully all values for Status are the same, which is why I used First) and the list of items.

var items = results.GroupBy(r => r.PrimaryKey).Select(grp => new ObjectA() 
            {
                PrimaryKey = grp.Key,
                Status = grp.Select(r => r.Status).First(),
                Items = grp.Where(r => r.ItemName != null)
                       .Select(r => new Item() { Name = r.ItemName }).ToList()
            }).ToList();

Answer 2

return results
    .GroupBy(r => r.PrimaryKey)
    .Select(grp => new ObjectA
    {
        PrimaryKey = grp.Key,
        Status = grp.First().Status,
        Items = grp.Where(i => i.ItemName != null).Select(i => new Item { Name = i.ItemName }).ToList()
    }).ToList();