Serializing a list of dynamic objects to a CSV with ServiceStack.Text

Question

All of my EF classes have a Projection() method that helps me choose what I want to project from the class to the SQL queries:

Example:

    public static Expression<Func<Something, dynamic>> Projection()
    {
        return e => new
        {
            something = e.SomethingId,
            name = e.Name,
            requiredThingId = e.RequiredThingId,
            requiredThing = new
            {
                requiredThingId = e.RequiredThing.RequiredThingId,
                name = e.RequiredThing.Name,
                ...
            },
            optionalThingId = e.OptionalThingId,
            optionalThing = e.OptionalThingId == null ? null : new 
            {
                optionalThingId = e.OptionalThing.OptionalThingId,
                name = e.OptionalThing.Name
            }
            ...
        };
    }

I use these projection methods like this:

  List<dynamic> projected = dbContext.Something.Select(Something.Projection()).ToList();

This way lets me reuse my projections all around my project.

I want to use ServiceStack.Text to serialize these lists to CSV.

I'm doing this:

 string csvString = CsvSerializer.SerializeToCsv<dynamic>(Something.Select(Something.Projection()).ToList());

 byte[] csvBytes = System.Text.Encoding.Unicode.GetBytes(csvString);

 return File(csvBytes, "text/csv", "foo.csv");

But the result is an empty csv (csvString is full of "\r\n"'s and nothing more)

Questions:

• Am I using correctly the SerializeToCsv() method?
• Can I use <dynamic> in this library?
• Suggestions for achieving my purpose?

For the example above the desired CSV would flatten all the properties, something like:

  "somethingId";"name";"requiredThingId";"requiredThing.requiredThingId";"requiredThing.name";"optionalThingId";"optionalThing.optionalThingId";"optionalThing.name"

Solution 1

I will answer my own questions, but will not mark as accepted in hope of a new greater answer..

Am I using correctly the SerializeToCsv() method? Can I use dynamic in this library?

Answer: Yes to both but ServiceStack.Text v4.0.36 is needed (which is available at MyGet, not Nuget)

Suggestions for achieving my purpose?

Answer: With ServiceStack.Text it is possible to serialize to CSV a List<dynamic>, but all the nested properties will be rendered as JSON and they will not be flattened out, eg:

   List<dynamic> list = new List<dynamic>();
   list.Add(new
   {
         name = "john", 
         pet = new 
         { 
              name = "doggy"
         }
   });

   string csv = CsvSerializer.SerializeToCsv(list);

This list will be serialized to this csv:

name, pet
"john", { name = "doggy" }

And not to this csv, as I was expecting:

name, pet_name
"john", "doggy"

So... I finally ended up writing this code:

public class CsvHelper
{
    public static string GetCSVString(List<dynamic> inputList)
    {
        var outputList = new List<Dictionary<string, object>>();

        foreach (var item in inputList)
        {
            Dictionary<string, object> outputItem = new Dictionary<string, object>();
            flatten(item, outputItem, "");

            outputList.Add(outputItem);
        }

        List<string> headers = outputList.SelectMany(t => t.Keys).Distinct().ToList();

        string csvString = ";" + string.Join(";", headers.ToArray()) + "\r\n";

        foreach (var item in outputList)
        {
            foreach (string header in headers)
            {
                if (item.ContainsKey(header) && item[header] != null)
                    csvString = csvString + ";" + item[header].ToString();
                else
                    csvString = csvString + ";";
            }

            csvString = csvString + "\r\n";
        }

        return csvString;
    }

    private static void flatten(dynamic item, Dictionary<string, object> outputItem, string prefix)
    {
        if (item == null)
            return;

        foreach (PropertyInfo propertyInfo in item.GetType().GetProperties())
        {
            if (!propertyInfo.PropertyType.Name.Contains("AnonymousType"))
                outputItem.Add(prefix + "__" + propertyInfo.Name, propertyInfo.GetValue(item));
            else
                flatten(propertyInfo.GetValue(item), outputItem, (prefix.Equals("") ? propertyInfo.Name : prefix + "__" + propertyInfo.Name));
        }
    }
}

What this does is:

1. It flattens the List, so that all the properties of the objects in the list are primitives (eg: no nested properties)

2. It creates a CSV from that flattened list.

This algorithm is O(n*m), being
n: number of items in the list
m: number of properties inside each item (including nested properties)

Solution 2

Whilst the recommendation is to use clean POCO's for Serialization, you can serialize in-line anonymous types, e.g:

var somethings =  dbContext.Something.Select(e => new { 
    something = e.SomethingId, 
    name = e.Name 
});
somethings.ToCsv().Print();

Otherwise I've just added support for serializing IEnumerable<object> and IEnumerable<dynamic> in this commit.

This change is available from v4.0.36+ that's now available on MyGet.