Linq Grouping by a list of a list and selecting from both lists

Question

So I'm having trouble wrapping my head around Group By currently for Linq and in essence am looking to grab the id from Pirate and all information within Ships, Ideally thrown into a list of IEnumerable DataRow (DataRow got hidden..).

public class Pirate
    {
        public string Id { get; set; }
        public List<Ship> Ships { get; set; }
    }
    public class Ship
    {
        public string Name { get; set; }
        public string ShipClass { get; set; }
        public string AvastyMast { get; set; }
    }

static void Main(string[] args)
    {
        List<Pirate> Pirates = new List<Program.Pirate>();

        Pirate Arr = new Pirate();
        Arr.Id = "1";

        Ship Pinta = new Ship();
        Pinta.Name = "Maria";
        Pinta.ShipClass = "BattleShip";
        Pinta.AvastyMast = "You Sunk My BattleShip";

        Ship Manta = new Ship();
        Pinta.Name = "Clara";
        Pinta.ShipClass = "Scout";
        Pinta.AvastyMast = "You Sunk My BattleShip!";

        Arr.Ships.Add(Pinta);
        Arr.Ships.Add(Manta);

        Pirates.Add(Arr);

        Pirate Sid = new Pirate();
        Sid.Id = "2";

        Ship Nuclara = new Ship();
        Pinta.Name = "Boon";
        Pinta.ShipClass = "Sub";
        Pinta.AvastyMast = "You Sunk My BattleShip!!";

        Ship Nutella = new Ship();
        Pinta.Name = "Slimer";
        Pinta.ShipClass = "Scout";
        Pinta.AvastyMast = "You Sunk My BattleShip!!!";


    }

So what I would like to do is Have a list of DataRows via Linq from the above, so that if I go through each DataRow and wrote out each row and column it would produce the following.

1 , Maria, BattleShip, You Sunk My Battleship
1 , Clara, Scout, You Sunk My Battleship!
2 , Boon, Sub, You Sunk My Battleship!!
2, Slimer, Scout, You Sunk My Battleship!!!

Answer 1

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Pirate> pirates = new List<Pirate>() {
                    new Pirate() { id = "1" , Ships = new List<Ship>(){
                        new Ship() { name = "Maria", _class = "BattleShip", avastyemast = "You Sunk My Battleship"},
                        new Ship() { name = "Clara", _class = "Scout", avastyemast = "You Sunk My Battleship"}
                    }
                },
                    new Pirate() { id = "2" , Ships =  new List<Ship>() {
                        new Ship() { name = "Boon", _class = "Sub", avastyemast = "You Sunk My Battleship"},
                        new Ship() { name = "Slimer", _class = "Scout", avastyemast = "You Sunk My Battleship"}
                    }
                }
            };

            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(string));
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("class", typeof(string));
            dt.Columns.Add("avastyemast", typeof(string));

            foreach (Pirate pirate in pirates)
            {
                foreach(Ship ship in pirate.Ships)
                {
                    dt.Rows.Add(new string[] {
                        pirate.id,
                        ship.name,
                        ship._class,
                        ship.avastyemast
                    });
                }

            }


            //using linq
           var newRows = pirates.Select(x => x.Ships.Select(y => new List<object>() {x.id, y.name, y._class, y.avastyemast})).SelectMany(z => z).ToList();
           foreach (var row in newRows)
           {
               dt.Rows.Add(row);
           }
        }
    }
    public class Pirate
    {
        public string id {get;set;} 
        public List<Ship> Ships {get;set;}

    }
    public class Ship
    {
        public string name {get;set;}
        public string _class {get;set;}
        public string avastyemast {get;set;}
    }
}
​

Answer 2

You can try this:

var pirates = new List<Pirate>(); // or wherever your pirates will come from

var data = from p in pirates
           let pirateId = p.Id
           from s in p.Ships
           select new
           {
               PirateId = pirateId,
               Name = s.Name,
               Class = s.ShipClass,
               AvastyMast = s.AvastyMast
           };

If you really want to get DataRow objects out of it, you can create a small method to create the DataRow:

private static DataRow CreateDataRow(DataTable table, string pirateId, string name,
    string shipClass, string avastyMast)
{
    var row = table.NewRow();

    // Make sure the column names match those in the table!
    row["PirateId"] = pirateId;
    row["Name"] = name;
    row["ShipClass"] = shipClass;
    row["AvastyMast"] = avastyMast;

    return row;
}

And use it like this:

var pirates = new List<Pirate>(); // or wherever your pirates will come from
var dataTable = new DataTable(); // or wherever your DataTable will come from

var data = from p in pirates
           let pirateId = p.Id
           from s in p.Ships
           select CreateDataRow(dataTable , pirateId, s.Name, s.ShipClass, s.AvastyMast);