Can I export hierarchical data to Excel?

Yossu

If I have a query that returns a collection as one of its columns, such as...

Customers.Select(c => new {
c.Name,
c.Orders.Select(o => o.OrderDate)
})

...and I want to export this to Excel so that I get rows like the following...

Jim Spriggs | 1st Jan '16
Jim Spriggs | 21st Mar '16
Fred Ferret | 12th Apr '16

...can I do this? If my data set includes a collection, then exporting to Excel ignores the column. I tried adding new line characters between the dates in the hope that it would produce something like this...

Jim Spriggs | 1st Jan '16
| 21st Mar '16
Fred Ferret | 12th Apr '16

...but it didn't help, the order dates were all in one cell on one line.

Any ideas? Thanks in advance.

Yossu

Sorry, worth pointing out that my actual query is a lot more complex than this, so it's not practical to rewrite the query to start form the Orders table and pull in the Customer as needed.

sgmoore

You could try something like
Customers.Select(c => new { Orders = c.Orders.Select(o => new { c.Name, o.OrderDate} ) } ).SelectMany(r=>r.Orders);

Yossu

Thanks, but won't that result in a collection of Orders? I want a collection of customers, but where each order will appear on a separate line when exported to Excel.

Please explain if I missed the point. Thanks for the reply.

nescafe

@Yossu, each order on a separate line makes it a collection of orders (e.g. the total number of exported lines equals the total quantity of the orders from all customers).

The example provided by sgmoore does exactly what you need.

I'd prefer the LINQ query syntax, which is a bit simpler and easier to read:

from c in Customers
from o in c.Orders
select new { c.Name, o.OrderDate }

Yossu

@nescafe I want a list of Customers, but where a customer has multiple Orders, then show each on a separate line. Not the same as a collection of orders, so not actually what sgmoore shows. Thanks anyway.

I prefer the method syntax, as I find it much easier to read, especially when used with extension methods

Thanks for the reply

nescafe

So the main issue is that you also want to see Customers with no orders?

Then try this:

from c in Customers
from o in c.Orders.DefaultIfEmpty()
select new { c.Name, o.OrderDate }

Method syntax:

Customers
  .SelectMany(
    c => c.Orders.DefaultIfEmpty(),
    (c, o) => new { c.Name, o.OrderDate }
  )

If you're using LINQ-to-objects, use o?.OrderDate to eliminate null reference exceptions.

Yossu

@nescafe Thanks for that, it's probably as close as I'll get! Ideally, I'd like not to repeat the Customer info on each row, but I guess that's probably asking too much.

Thanks again

nescafe

If I understand correctly, you want to suppress the customer info for all subsequent orders? This is in contrast to the first example you provided (with customer name Jim Spriggs repeated).

This will display the customer name only on the first row (where order index is zero)

from c in Customers.AsEnumerable()  // AsEnumerable mandatory when using LINQ-to-SQL
from o in c.Orders.DefaultIfEmpty().Select((x, i) => new { Entity = x, Index = i })
select new { Name = o.Index == 0 ? c.Name : null, o.Entity?.OrderDate }

Method syntax:

Customers
  .AsEnumerable() // Mandatory when using LINQ-to-SQL
  .SelectMany(
    c => c.Orders.DefaultIfEmpty().Select((x, i) => new { Entity = x, Index = i }),
    (c, o) => new { Name = o.Index == 0 ? c.Name : null, o.Entity?.OrderDate }
 )

The .AsEnumerable() is necessary to support the overload function .Select((T x, int i) => expr) function and will generate a lot of requests to the server, but the output should satisfy your needs

Yossu

Ooh, clever! Thanks for that. I'm always amazed at how much more there is to learn about Linq!