Is it possible to return IEnumerable of anonymous objects from DataContext.ExecuteQuery?

Question

I develop a reporting engine where reports are based on templates. Every template has string with SQL query and every report has specific values for SQL query parameters. To render a report I set parameters and call DataContext.ExecuteQuery method to get list of records. But to catch returned columns I have to know their names and have a class with corresponding properties.

Is it possible somehow to return IEnumerable of anonymous objects from DataContext.ExecuteQuery and then determine their properties using Reflection?

I need a LINQ equivalent for SqlDataReader.GetValues.

Thanks!

Answer 1

Yes, you can do it. Please have a look at this snippet.

class Program {
        static void Main(string[] args) {
            var persons = new Person[]{
                new Person{Age=22,Name="John Doe",Id=1},
                new Person{Age=23,Name="Jack Smith", Id=2},
                new Person{Age=34,Name="Sara Parker", Id=3}
            };
            var anonData = GetAnonTypes(persons);
            foreach (var item in anonData as IEnumerable) {
                //use reflection to access propties 
            }
        }
        static object GetAnonTypes(IEnumerable<Person> persons) {
            var query=from p in persons  select new{
                Id=p.Id,
                Name=p.Name
            };
            return query;        
        }
    }

    public class Person {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

Answer 2

You're not getting compile-time type checking against this opaque sql. You don't really understand anonymous (compile-time) typing. Don't use LinqToSql for this.

Answer 3

Until we have C# 4.0 with dynamiс keyword we can use this solution (slightly modified code from an article Executing arbitrary queries in LINQ to SQL by Octavio Hernández Leal):

public static class DataContextExtension
{
 public static IEnumerable<Dictionary<string, object>> ExecuteQuery(this DataContext dataContext, string query)
 {
  using (DbCommand command = dataContext.Connection.CreateCommand())
  {
   command.CommandText = query;
   dataContext.Connection.Open();

   using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
   {
    while (reader.Read())
    {
     Dictionary<string, object> dictionary = new Dictionary<string, object>();

     for (int i = 0; i < reader.FieldCount; i++)
      dictionary.Add(reader.GetName(i), reader.GetValue(i));

     yield return dictionary;
    }
   }
  }
 }
}

This extension method returns IEnumerable of Dictionary<> objects where keys are names of query columns.