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:
SerializeToCsv()
method?<dynamic>
in this library?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"
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:
It flattens the List, so that all the properties of the objects in the list are primitives (eg: no nested properties)
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)