Getting the field names from a Linq To Sql object

Question

I have a Linq expression which I'd like to insert to a ADO.NET datatable. I would like to know the name of the fields from the query in order to set the datatable name. Here is an example of my code:

var result=from item in context.table
           select new{
                field1=... ,
                field2=... ,
                field3=...
           };

What I'd like to do is to set the tables name.

Datatable.Columns.Add("field1"); .. etc

I tried to do it manually but I believe there should be an elegant solution for it.

Thanks

Answer 1

My suggestion:

var result=from item in context.table
           select new{
                field1=... ,
                field2=... ,
                field3=...
           };

static IEnumerable<string> GetPropertyNames<T>(IEnumberable<T> lst) 
{
  foreach (var pi in typeof(T).GetProperties())
  {
    yield return pi.Name;
  }
}

var propnames = GetPropertyNames(result);

Answer 2

By making use of reflection you can extract the names of the properties of your anonymous type created in the LINQ expression.

var result = from item in context.table
    select new {
         field1 = ... ,
         field2 = ... ,
         field3 = ... };

if (result.Any())
{
    Type t = result.First().GetType();
    foreach (PropertyInfo p in t.GetProperties())
    {
        // Get the name of the prperty
        Console.WriteLine(p.Name);
    }
}

Answer 3

This might be bit simpler ;) ...

var fieldNames = (from p in query.GetType().GetProperties() select p.Name).ToArray();

Answer 4

Because select new creates an anonymous type, there isn't a short elegant solution that I know of, but you can do what you want. The idea here is that you will take the first item returned by the query, and using the type information of the anonymous type, we can reflect its properties and fill your DataTable.

We can do this using the following method that takes a DataTable and the Type information of the anonymous type.

public static void FillColumns(DataTable table, Type anonymousType) {
    PropertyInfo[] properties = anonymousType.GetProperties();

    foreach (PropertyInfo property in properties) {
        table.Columns.Add(property.Name);
    }
}

Then for example you can do something like this

var result = from item in context.Table
            select new {
                field1 = item.f1,
                field2 = item.f2,
                field3 = item.f3
            };

if (result.Count() != 0) {
    DataTable table = new DataTable("Table");
    FillColumns(table, result.First().GetType());
}

Your DataTable in this short example would yield 3 columns field1, field2, and field3.
Edit Already invested the time, so might as well post the full working example.

namespace ConsoleApplication1
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Diagnostics;
    using System.Linq;
    using System.Reflection;

    #region Fake Database

    internal class DatabaseMock
    {
        private DatabaseMock() { 
            // Hides the default public constructor created by the compiler
            // Uses the factory pattern for creation instead
        }

        /// <summary>
        /// Creates a new instance of a database with three default items
        /// </summary>
        public static DatabaseMock Create() {
            DatabaseMock database = new DatabaseMock();

            List<ItemMock> items = new List<ItemMock>();
            items.Add(new ItemMock("item1"));
            items.Add(new ItemMock("item2"));
            items.Add(new ItemMock("item3"));

            database.Table = items;

            return database;
        }

        /// <summary>
        /// Gets the items in the database
        /// </summary>
        public IEnumerable<ItemMock> Table {
            get;
            private set;
        }
    }

    internal struct ItemMock
    {
        /// <summary>
        /// Initializes a new instance of the ItemMock class
        /// </summary>
        public ItemMock(string value) {
            _value = value;
        }

        private string _value;
        /// <summary>
        /// Gets the items value
        /// </summary>
        public string Value {
            get {
                return _value;
            }
        }
    }

    #endregion

    static class Program
    {
        /// <summary>
        /// Takes the specified DataTable and anonymous type information, and populates the table with a new DataColumn per anonymous type property
        /// </summary>
        public static void FillColumns(DataTable table, Type anonymousType) {
            PropertyInfo[] properties = anonymousType.GetProperties();

            foreach (PropertyInfo property in properties) {
                table.Columns.Add(property.Name);
            }
        }

        static void Main() {
            DatabaseMock database = DatabaseMock.Create();

            var query =
                from item in database.Table
                select new {
                    field1 = item.Value,
                    field2 = item.Value,
                    field3 = item.Value
                };

            if (query.Count() != 0) {
                DataTable table = new DataTable("Table");
                FillColumns(table, query.First().GetType());

#if DEBUG
                foreach (DataColumn column in table.Columns) {
                    Debug.WriteLine(column.ColumnName);
                }
#endif
            }
        }
    }
}