【ASP.NET MVC】匿名型別的LINQ To DataTable

內建的CopyToDataTable必須指定型別，但是遇到LINQ中的select new{..}就無法運作。

測試下可使用以下方法轉換：



Sample 1：傳入的LINQ轉為IEnumerable獲取實體資料，利用foreach方式創建一個新的DataTable。

using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;

public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
{
    DataTable dtReturn = new DataTable();
    // column names
    PropertyInfo[] oProps = null;
    // 檢查
    if (varlist == null) return dtReturn;
    foreach (T rec in varlist)
    {
        // Use reflection to get property names, to create table, Only first time, others will follow
        if (oProps == null)
        {
            oProps = ((Type)rec.GetType()).GetProperties();
            foreach (PropertyInfo pi in oProps)
            {
                Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                {
                    colType = colType.GetGenericArguments()[0];
                }
                dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
            }
        }
        DataRow dr = dtReturn.NewRow();
        foreach (PropertyInfo pi in oProps)
        {
            dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
        }
        dtReturn.Rows.Add(dr);
    }
    return (dtReturn);
}

/**
    使用方法：
    var result = from ....;
    DataTable dt = LINQToDataTable(result);
**/

Sample 2：

public DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
{
     if (query == null)
     {
          throw new ArgumentNullException("query");
     }
     
     IDbCommand cmd = ctx.GetCommand(query as IQueryable);
     SqlDataAdapter adapter = new SqlDataAdapter();
     adapter.SelectCommand = (SqlCommand)cmd;
     DataTable dt = new DataTable("sd");

     try
     {
          cmd.Connection.Open();
          adapter.FillSchema(dt, SchemaType.Source); 
          adapter.Fill(dt);
     }
     finally
     {
          cmd.Connection.Close();
     }
     return dt;
}

/**
    使用方法：
    var vrCountry = from ...;
    DataTable dt = LINQToDataTable(objEmpDataContext,vrCountry);
**/

因為負責的專案是分層架構，所以最後是用Sample1的方式寫在Services裡。

參考文章：Convert a LINQ Query Resultset to a DataTable