none
Create DataTable From Linq Query RRS feed

  • Question

  • I am using this Linq Query - how would I create a datatablle from this query?

        static void Main(string[] args)
        {
            DataTable dtInfo = GetTable();
    
    
            var query =
                from row in dtInfo.AsEnumerable() select new
                {
                  name =  row.Field<string>("name"),
                  columnname = 
                  (row.Field<DateTime>("exam1complete")==DateTime.MinValue) 
                  ? 
                  ("exam1date") 
                  :
                  (
                    (row.Field<DateTime>("exam2complete") == DateTime.MinValue) ? ("exam2date") : ("exam3date")
                  ),
                  examdate =
                  (row.Field<DateTime>("exam1complete") == DateTime.MinValue)
                  ?
                  (row.Field<DateTime>("exam1date"))
                  :
                  (
                    (row.Field<DateTime>("exam2complete") == DateTime.MinValue) ? (row.Field<DateTime>("exam2date")) : (row.Field<DateTime>("exam3date"))
                  )
                }
                ;
        }

    Saturday, March 9, 2019 11:05 PM

Answers

  • First add the following method to your code:

            public static DataTable ToDataTable(IEnumerable ie)
            {
                DataTable dt = new DataTable();
                foreach (object obj in ie)
                {
                    Type t = obj.GetType();
                    PropertyInfo[] pia = t.GetProperties();
                    if (dt.Columns.Count == 0)
                    {
                        foreach (PropertyInfo pi in pia)
                        {
                            dt.Columns.Add(pi.Name, pi.PropertyType);
                        }
                    }
    
                    DataRow dr = dt.NewRow();
                    foreach (PropertyInfo pi in pia)
                    {
                        object value = pi.GetValue(obj, null);
                        dr[pi.Name] = value;
                    }
    
                    dt.Rows.Add(dr);
                }
    
                return dt;
            }
    

    Then simply call it thus:

    DataTable dt = ToDataTable(query);

    • Marked as answer by IndigoMontoya Sunday, March 10, 2019 9:45 PM
    Sunday, March 10, 2019 10:08 AM
    Moderator

All replies

  • First add the following method to your code:

            public static DataTable ToDataTable(IEnumerable ie)
            {
                DataTable dt = new DataTable();
                foreach (object obj in ie)
                {
                    Type t = obj.GetType();
                    PropertyInfo[] pia = t.GetProperties();
                    if (dt.Columns.Count == 0)
                    {
                        foreach (PropertyInfo pi in pia)
                        {
                            dt.Columns.Add(pi.Name, pi.PropertyType);
                        }
                    }
    
                    DataRow dr = dt.NewRow();
                    foreach (PropertyInfo pi in pia)
                    {
                        object value = pi.GetValue(obj, null);
                        dr[pi.Name] = value;
                    }
    
                    dt.Rows.Add(dr);
                }
    
                return dt;
            }
    

    Then simply call it thus:

    DataTable dt = ToDataTable(query);

    • Marked as answer by IndigoMontoya Sunday, March 10, 2019 9:45 PM
    Sunday, March 10, 2019 10:08 AM
    Moderator
  • Hello,

    Here is another version to work with that in the first example does a simple query with selected fields.

    using (var context = new NorthWindEntities())
    {
        var results = context.Customers.Select(cust => new { Name = cust.CompanyName, Contact = cust.ContactName }).ToList();
        var dt = results.ToDataTable();
    }

    In this example some of the columns will turn out to be collections (navigation properties)

    using (var context = new NorthWindEntities())
    {
        context.Configuration.LazyLoadingEnabled = false;
    
        var allCustomers = context.Customers
            .Include(customer => customer.Contact)
            .Include(customer => customer.ContactType)
            .Include(customer => customer.Contact.ContactContactDevices)
            .ToList();
    
        var dt = allCustomers.ToDataTable();
    
    }

    And here is the extension method.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Reflection;
    
    namespace Entity_cs
    {
        public static class GenericConverters
        {
            public static DataTable ToDataTable<T>(this IEnumerable<T> pSender)
            {
                var resultTable = new DataTable();
    
                // column names
                PropertyInfo[] firstRecord = null;
    
                if (pSender == null) return resultTable;
    
                foreach (T rec in pSender)
                {
                    if (firstRecord == null)
                    {
                        firstRecord = ((Type)rec.GetType()).GetProperties();
                        foreach (var pi in firstRecord)
                        {
                            var columnType = pi.PropertyType;
    
                            if ((columnType.IsGenericType) && (columnType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                            {
                                columnType = columnType.GetGenericArguments()[0];
                            }
    
                            resultTable.Columns.Add(new DataColumn(pi.Name, columnType));
                        }
                    }
    
                    var dr = resultTable.NewRow();
    
                    foreach (var pi in firstRecord)
                    {
                        dr[pi.Name] = pi.GetValue(rec, null) ?? DBNull.Value;
                    }
    
                    resultTable.Rows.Add(dr);
                }
    
                return resultTable;
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, March 10, 2019 11:44 AM
    Moderator
  • Check this too:

    var dtResult = new DataTable();
    dtResult.Columns.Add( "name" );
    dtResult.Columns.Add( "columnname" );
    dtResult.Columns.Add( "examdate", typeof( DateTime ) );
    
    var query =
        from row in dtInfo.AsEnumerable()
        let cn =
            row.Field<DateTime>( "exam1complete" ).Equals( DateTime.MinValue ) ?
                "exam1date"
            :
            row.Field<DateTime>( "exam2complete" ) == DateTime.MinValue ?
                "exam2date"
            :
                "exam3date"
        select new object[] { row["name"], cn, row[cn] };
    
    query.All( a => { dtResult.Rows.Add( a ); return true; } );
    



    • Edited by Viorel_MVP Sunday, March 10, 2019 6:16 PM
    Sunday, March 10, 2019 6:10 PM