none
CopyToDataTable in LINQ - how? RRS feed

  • Question

  • Hello!

    I have problem. I want to PIVOT my data which I got from LINQ query.

    Here is sample:
    http://www.telerik.com/help/aspnet/grid/?grdPivotData.html

    The problem is that I can not convert IQuerynumerable (LINQ query) to DataTable.

    Can anyone explain me how to do this or how to use CopyToDataTable? Cause I can not get it works.

    Thanks.
    Sunday, March 23, 2008 11:27 PM

Answers

  • Hi there, I had a similar problem and wrote an extension method to IQueryable based upon another example I'd seen.

    Code Snippet

    static public class ConvertDataTable
    {
        public static DataTable ToADOTable(this IQueryable query)
        {
            DataTable dtReturn = new DataTable();
            PropertyInfo[] columnProperties = null;

            foreach (var record in query)
            {
                // use reflection to get column names for the table, only first time, others will follow
                if (columnProperties == null)
                {
                    columnProperties = record.GetType().GetProperties();
                    foreach (PropertyInfo propertyInfo in columnProperties)
                    {
                        // sort out the issue of nullable types
                        Type columnType = propertyInfo.PropertyType;
                        if ((columnType.IsGenericType) && (columnType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            columnType = columnType.GetGenericArguments()[0];
                        }

                        // add a column to the table
                        dtReturn.Columns.Add(new DataColumn(propertyInfo.Name, columnType));
                    }
                }

                DataRow dataRow = dtReturn.NewRow();

                foreach (PropertyInfo propertyInfo in columnProperties)
                {
                    dataRow[propertyInfo.Name] = propertyInfo.GetValue(record, null) == null ? DBNull.Value : propertyInfo.GetValue(record, null);
                }

                dtReturn.Rows.Add(dataRow);
            }

            return dtReturn;
        }
    }


    Hope that helps Smile
    Monday, March 24, 2008 10:30 AM

All replies

  • Hi there, I had a similar problem and wrote an extension method to IQueryable based upon another example I'd seen.

    Code Snippet

    static public class ConvertDataTable
    {
        public static DataTable ToADOTable(this IQueryable query)
        {
            DataTable dtReturn = new DataTable();
            PropertyInfo[] columnProperties = null;

            foreach (var record in query)
            {
                // use reflection to get column names for the table, only first time, others will follow
                if (columnProperties == null)
                {
                    columnProperties = record.GetType().GetProperties();
                    foreach (PropertyInfo propertyInfo in columnProperties)
                    {
                        // sort out the issue of nullable types
                        Type columnType = propertyInfo.PropertyType;
                        if ((columnType.IsGenericType) && (columnType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            columnType = columnType.GetGenericArguments()[0];
                        }

                        // add a column to the table
                        dtReturn.Columns.Add(new DataColumn(propertyInfo.Name, columnType));
                    }
                }

                DataRow dataRow = dtReturn.NewRow();

                foreach (PropertyInfo propertyInfo in columnProperties)
                {
                    dataRow[propertyInfo.Name] = propertyInfo.GetValue(record, null) == null ? DBNull.Value : propertyInfo.GetValue(record, null);
                }

                dtReturn.Rows.Add(dataRow);
            }

            return dtReturn;
        }
    }


    Hope that helps Smile
    Monday, March 24, 2008 10:30 AM
  • Here is an easier way...

    public static DataTable GetSubjectListForGroup(int GroupID)
            {

                var res = from p in DataContext.tblPersons
                          where p.FirstName == "Peter"
                          orderby p.LastName
                          select new {
                              p.PersonId,
                              Subject = p.FirstName + " " + p.LastName,
                              p.tblClassification.ClassificationDesc,
                              p.Company,
                              Supervisor = p.tblPerson1.FirstName + " " + p.tblPerson1.LastName,
                              p.FERCCode,
                              p.Location,
                              p.Department,
                              p.CostCenter};
                         

              SqlCommand cmd = DataContext.GetCommand(res) as SqlCommand;

              DataTable dataTable = new DataTable();
              SqlDataAdapter adapter = new SqlDataAdapter(cmd);
              adapter.Fill(dataTable);
              return dataTable;
    Thursday, August 7, 2008 8:43 PM
  • @Peter This way is TOTALLY NOT ACCEPTABLE ... 

    You are querying the database twice!!   Why don't you just use the latter ado.net code and you won't need the linq part.  Just create your standard sql query string and pass it in...    
    Thursday, September 11, 2008 10:39 PM