Export Dynamic LINQ to CSV

By , September 8, 2019 3:22 am

LINQ allows to perform various queries against different data structures. Wouldn’t it be great if you could easily export result of a LINQ query to CSV? Fortunately you can! This article by Scott Hanselman explain how and culminates in cool in its simplicity code:

namespace FooFoo
{
    public static class LinqToCSV
    {
        public static string ToCsv<T>(this IEnumerable<T> items)
            where T : class
        {
            var csvBuilder = new StringBuilder();
            var properties = typeof(T).GetProperties();
            foreach (T item in items)
            {
                string line = string.Join(",",properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray());
                csvBuilder.AppendLine(line);
            }
            return csvBuilder.ToString();
        }
 
        private static string ToCsvValue<T>(this T item)
        {
            if(item == null) return "\"\"";
 
            if (item is string)
            {
                return string.Format("\"{0}\"", item.ToString().Replace("\"", "\\\""));
            }
            double dummy;
            if (double.TryParse(item.ToString(), out dummy))
            {
                return string.Format("{0}", item);
            }
            return string.Format("\"{0}\"", item);
        }
    }
}

This code adds .ToCsv() extension method to any IEnumerable so you can run something like

string csv = linqQuery.ToCSV()

Unfortunately this doesn’t work if your query is Dynamic LINQ query

What is happening if you’re running .ToCsv() extension method on a dynamic LINQ query – type of item is Object, so GetProperties() method from Line 9 produces no results and hence string line on Line 12 – a row of the output CSV gets empty value as well.

We need a different way of getting properties of items in the IEnumerable.

public static string ToCsv(this IEnumerable items)
   where T : class
{
   var csvBuilder = new StringBuilder();

   var properties = items.First().GetType().GetProperties();

   string line = string.Join(",", properties.Where(p => p.PropertyType.Name != "Object").Select(p => p.Name.ToCsvValue()).ToArray());
   csvBuilder.AppendLine(line);

   foreach (T item in items)
   {
      line = string.Join(",", properties.Where(p =>; p.PropertyType.Name != "Object").Select(p => p.GetValue(item, null).ToCsvValue()).ToArray());
      csvBuilder.AppendLine(line);
   }
   return csvBuilder.ToString();
}

In this modified code of ToCSV() method I replaced the original method of getting type properties with the one deriving type from actual item of the IEnumerable (Line 6). This method of deriving properties resulted in one extra property of “Object” type, so to exclude it I added p.PropertyType.Name != "Object" condition.

I also added piece that outputs property names as the first row on Lines 8-9, so the CSV would have proper column headers.

Leave a Reply

Panorama Theme by Themocracy

Bitnami