Using Dynamic.Linq, how to return query to DataTable

Question

I'm using System.Linq.Dynamic

var query =     this.testFormsDataSet.Consignment.AsEnumerable().GroupBy(groupBy, "it")
.Select(selectBy);

With that query is all OK.

But right now I don't understand how to save query to DataTable. I tried to use https://msdn.microsoft.com/en-us/library/bb669096(v=vs.110).aspx .

DataTable res = CustomLINQtoDataSetMethods.CopyToDataTable(query);

or

DataTable res = query.CopyToDataTable();

But CopyToDataTable still doesn't work.

Please help!!

It says: "The type arguments for method '___' cannot be inferred from the usage. Try specifying the type arguments explicitly." — Kavrat, Mar 30 '15 at 08:25
You could try adding a `Cast()` before the `CopyToDataTable()`, but remember that the result of the `Select` **must** be a `DataRow` (because `CopyToDataTable` works only on `DataRow`s) — xanatos, Mar 30 '15 at 08:31
@xanatos I tried use 'Cast()' but in that class https://msdn.microsoft.com/en-us/library/bb669096(v=vs.110).aspx I get an Error system.invalidcastexception Failed to cast object of type "DynamicClass2" to "System.Data.DataRow". — Kavrat, Mar 30 '15 at 20:50
It should works as well. I'd suggest to see this: http://stackoverflow.com/questions/4969321/the-type-arguments-cannot-be-inferred-from-the-usage-try-specifying-the-type-ar - answer by Jim. — Maciej Los, Mar 30 '15 at 21:01
More about error causes: https://msdn.microsoft.com/en-us/library/hxfhx4sy%28v=vs.90%29.aspx — Maciej Los, Mar 30 '15 at 21:04
@Kavrat The failing `ToList()` is your problem. Dynamic LINQ is meant to work on `IQueryable`. — Gert Arnold, Mar 31 '15 at 06:32

score 0 · Accepted Answer · edited May 23 '17 at 11:58

Based on Convert IEnumerable to DataTable

public static class DynamicLinqDataTableHelpers
{
    public static DataTable ToDataTable(this IQueryable items)
    {
        Type type = items.ElementType;

        // Create the result table, and gather all properties of a type        
        DataTable table = new DataTable(type.Name);

        PropertyInfo[] props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);

        // Add the properties as columns to the datatable
        foreach (PropertyInfo prop in props)
        {
            Type propType = prop.PropertyType;

            // Is it a nullable type? Get the underlying type 
            if (propType.IsGenericType && propType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                propType = Nullable.GetUnderlyingType(propType);
            }

            table.Columns.Add(prop.Name, propType);
        }

        // Add the property values as rows to the datatable
        foreach (object item in items)
        {
            var values = new object[props.Length];

            if (item != null)
            {
                for (var i = 0; i < props.Length; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }
            }

            table.Rows.Add(values);
        }

        return table;
    }
}

and then

DataTable res = query.ToDataTable();

The main difference is that instead of "extracting" the type T through generics, I use the ElementType property of the IQueryable

Now... you seems to use a strange variation of Dynamic LINQ that works on IEnumerable... I've adapted the ToDataTable:

public static class DynamicLinqDataTableHelpers
{
    public static DataTable ToDataTable(this IEnumerable items)
    {
        // Create the result table, and gather all properties of a type        
        DataTable table = new DataTable();

        PropertyInfo[] props = null;

        // Add the property values as rows to the datatable
        foreach (object item in items)
        {
            if (props == null && item != null)
            {
                Type type = item.GetType();
                props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);

                // Add the properties as columns to the datatable
                foreach (PropertyInfo prop in props)
                {
                    Type propType = prop.PropertyType;

                    // Is it a nullable type? Get the underlying type 
                    if (propType.IsGenericType && propType.GetGenericTypeDefinition() == typeof(Nullable<>))
                    {
                        propType = Nullable.GetUnderlyingType(propType);
                    }

                    table.Columns.Add(prop.Name, propType);
                }
            }

            // When the column headers are defined, all the rows have
            // their number of columns "fixed" to the right number
            var values = new object[props != null ? props.Length : 0];

            if (item != null)
            {
                for (var i = 0; i < props.Length; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }
            }

            table.Rows.Add(values);
        }

        return table;
    }
}

Still doesn't work. "Error 1 Instance argument: cannot convert from 'System.Collections.IEnumerable' to 'System.Linq.IQueryable' " Error 2 'System.Collections.IEnumerable' does not contain a definition for 'ToDataTable' and the best extension method overload 'MyForm.DynamicLinqDataTableHelpers.ToDataTable(System.Linq.IQueryable)' has some invalid arguments — Kavrat, Apr 02 '15 at 18:20
@Kavrat There is something strange in your query: `this.testFormsDataSet.Consignment.AsEnumerable().GroupBy`, but I know that Dynamic LINQ doesn't work on `AsEnumerable()`. Try removing the `AsEnumerable()` — xanatos, Apr 03 '15 at 08:23
@Kavrat Ok... Try telling me the types of `this.testFormsDataSet` and `this.testFormsDataSet.Consignment`. If you do a `var xx = this.testFormsDataSet` and move the mouse on `var` you should be able to see it. Do the same for `Consignment` — xanatos, Apr 05 '15 at 07:17
'this.testFormsDataSet.Consignment' - Represents the strongly named clas DataTable, 'this.TestFormsDataSet' - Represents strongly typed in-memory cache od data. — Kavrat, Apr 06 '15 at 16:50
@Kavrat and you told that `this.testFormsDataSet.Consignment.AsEnumerable().GroupBy(groupBy, "it").Select(selectBy)` will compile? — xanatos, Apr 06 '15 at 16:52
Yes. I displayed result like that: 'foreach (var item in query) {MessageBox.Show(item.ToString()); }' And result was as I expexted — Kavrat, Apr 06 '15 at 16:53
'Interface System.Collections.IEnumerable. Exposes the enumerator , which supports a simple iteration over non-generoc collection' — Kavrat, Apr 06 '15 at 16:57
@Kavrat The `GroupBy` and the `Select`, if you do `Go To Definition` on them, do you have them in a source file or are they defined in a referenced assembly? Because they are "non-standard". I've created a version of `ToDataTable` that should work for you. — xanatos, Apr 06 '15 at 17:09
GroupBy and Select are from System.Linq.Dynamic '#region Assembly System.Linq.Dynamic.dll, v1.0.3.0 // D:\C#\MyForm\packages\System.Linq.Dynamic.1.0.4\lib\net40\System.Linq.Dynamic.dll #endregion', 'public static class DynamicQueryable' — Kavrat, Apr 06 '15 at 17:22
@Kavrat No, simply there are multiple implementation around... probably 3 or 4. If it does what you need, then it is the right one :) — xanatos, Apr 06 '15 at 17:30

Using Dynamic.Linq, how to return query to DataTable

1 Answers1