Export data from nested c# classes to csv file

Question

I have the following classes to work with and I want to export/flatten the contents to a csv file. I have tried to use SelectMany but I can’t find a way of selecting fields for my csv file from both Company level and the underlying level with SalesArea, Node and EthernetArea. Can someone explain what I need to do to accomplish this?

public class Rootobject
{
    public Company[] Companies { get; set; }
}
public class Company
{
    public string ORG_NAME { get; set; }
    public SalesArea[] Salesareas { get; set; }
    public Node[] Nodes { get; set; }
    public EthernetArea[] Ethernetareas { get; set; }
}
public class SalesArea
{
    public string OBJ_NAME { get; set; }
    public string AREA_CTYPE { get; set; }
}
public class Node
{
    public string OBJ_NAME { get; set; }
    public object BUILDING { get; set; }
}
public class EthernetArea
{
    public string OBJ_NAME { get; set; }
    public string AREA_CTYPE { get; set; }
    public Product[] Products { get; set; }
}
public class Product
{
    public string BANDWIDTH_A { get; set; }
    public string CONNECTION_TYPE { get; set; }
}

Answer 1

I'm sure there should be another way to do this, but anyways here it's how I did it...

If I understand correctly you want to 'Denormalize' the data and export the collection to a CSV.

I used many Select SelectMany and Join

var root = new Rootobject
    {
        Companies = new Company[] {
            new Company
        {
            ORG_NAME = "Co",
            Salesareas = new SalesArea[]{
            new SalesArea {
                OBJ_NAME = "Sa",
                AREA_CTYPE = "SaAr",
            }
        },
            Nodes = new Node[] {
            new Node {
                OBJ_NAME = "No",
                BUILDING = "NoBu"
            }
        },
            Ethernetareas = new EthernetArea[] {
            new EthernetArea {
                OBJ_NAME = "Et",
                AREA_CTYPE = "EtAr",
                Products = new Product[] {
                    new Product {
                        BANDWIDTH_A = "ProA",
                        CONNECTION_TYPE = "ProCon"
                    }
                }
            }
        }
        },
        new Company
        {
            ORG_NAME = "Co2",
            Salesareas = new SalesArea[]{
            new SalesArea {
                OBJ_NAME = "Sa2",
                AREA_CTYPE = "SaAr2",
            }
        },
            Nodes = new Node[] {
            new Node {
                OBJ_NAME = "No2",
                BUILDING = "NoBu2"
            }
        },
            Ethernetareas = new EthernetArea[] {
            new EthernetArea {
                OBJ_NAME = "Et2",
                AREA_CTYPE = "EtAr2",
                Products = new Product[] {
                    new Product {
                        BANDWIDTH_A = "ProA2",
                        CONNECTION_TYPE = "ProCon2"
                    },
                    new Product {
                        BANDWIDTH_A = "ProA3",
                        CONNECTION_TYPE = "ProCon3"
                    }
                }
            }
        }
        }
    }
    };

    var sas = root.Companies.SelectMany(x => x.Salesareas.Select(y => new { Company = x.ORG_NAME, SalesName = y.OBJ_NAME, SalesAreaType = y.AREA_CTYPE }));
    var nodes = root.Companies.SelectMany(x => x.Nodes.Select(y => new { Company = x.ORG_NAME, NodesName = y.OBJ_NAME, NodeBuilding = y.BUILDING }));
    var ethes = root.Companies.SelectMany(x => x.Ethernetareas.SelectMany(y => y.Products .Select(z => new { Company = x.ORG_NAME, EthernetName = y.OBJ_NAME, EthernetArea = y.AREA_CTYPE, BandwithA = z.BANDWIDTH_A, ConnnectionType = z.CONNECTION_TYPE })));

    sas.Join(nodes, x => x.Company, y => y.Company, (x, y) => new {x.Company, x.SalesName, x.SalesAreaType, y.NodesName, y.NodeBuilding})
        .Join(ethes, x => x.Company, y => y.Company, (x, y) => new {x.Company, x.SalesName, x.SalesAreaType, x.NodesName, x.NodeBuilding, y.EthernetName, y.EthernetArea, y.BandwithA, y.ConnnectionType})
        .Dump();

• Dump() is a LinqPad Extension Method

And here is the result...

With the Collection you can use CsvHelper as recommended by @Drag And Drop in order to generate the CSV file.... :)

Edit

I just realized that @Drag And Drop code is more straightforward, cleaner and better.... :)

Answer 2

Define a class that will be the representation of what you want in your CSV. As multiple object properties have the same name in your model.
In your comment you choosed to prefix your properties with the class name so you should have something like :

public class CompanyCSV
{
    public string Company_ORG_NAME { get; set; }

    public string SalesArea_OBJ_NAME { get; set; }
    public string SalesArea_AREA_CTYPE { get; set; }

    public string Node_OBJ_NAME { get; set; }
    public string Node_BUILDING { get; set; }

    public string EthernetArea_OBJ_NAME { get; set; }
    public string EthernetArea_AREA_CTYPE { get; set; }

    public string Product_BANDWIDTH_A { get; set; }
    public string Product_CONNECTION_TYPE { get; set; }
}

Then simply:

var result = from c in dataInput.Companies
                from s in c.Salesareas
                from n in c.Nodes
                from e in c.Ethernetareas
                from p in e.Products
                select new CompanyCSV
                {
                    Company_ORG_NAME = c.ORG_NAME,

                    SalesArea_OBJ_NAME = s.OBJ_NAME,
                    SalesArea_AREA_CTYPE = s.AREA_CTYPE,

                    Node_OBJ_NAME = n.OBJ_NAME,
                    Node_BUILDING = n.BUILDING.ToString(),

                    EthernetArea_OBJ_NAME = e.OBJ_NAME,
                    EthernetArea_AREA_CTYPE = e.AREA_CTYPE,

                    Product_BANDWIDTH_A = p.BANDWIDTH_A,
                    Product_CONNECTION_TYPE = p.CONNECTION_TYPE
                };


foreach (var line in result)
{ // Don't use this to generate your csv this is simply to show the result
    Console.WriteLine($"{line.c},{line.s},{line.n},{line.e},{line.p}, ..etc ..");
}

To write your CSV you can use CsvHelper. This will help you handlevalue with comma or quote. Do not generate your CSV by hand.

If your collection (Node[], Company[], etc.) can be null you should guard this edge case using a null-coalescing operator in the getter, like :

private Node[] nodes;
public Node[] Nodes {
    get { return nodes ?? (nodes = new Node[] { }); }
    set { nodes = value; }
}