How to convert IQueryable to DataTable

Question

I wrote the query using LinQ and I used the CopyToDataTable method. At that line it is showing implicit conversion type error from my database type to System.Data.DataRow.

var query = from i in dbContext.Personaldetails
            where i.ID == 1
            select i;

            return query.CopyToDataTable();

Any suggestions?

Solution

CopyToDataTable requires collection of DataRow objects. See How to: Implement CopyToDataTable Where the Generic Type T Is Not a DataRow to solve this problem.

UPDATE: If your entity has nullable field, you can modify ObjectShredder.ExtendTable method. Find place where new columns are added to table, and remove second parameter, which provides type of data in column (DataColumn class does not support nullable data types):

foreach (PropertyInfo p in type.GetProperties())
{
    if (!_ordinalMap.ContainsKey(p.Name))
    {
        DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name]
            // do not provide column type
            : table.Columns.Add(p.Name); 

        _ordinalMap.Add(p.Name, dc.Ordinal);
    }
}

Comments

[steve]

I have implemented that logic but at the query.copytodatatable it is showing error that can't implicit conversion type.

[Sergey Berezovskiy]

@nanicharan make sure you use CustomLINQtoDataSetMethods, not those from LINQ to dataset.

[steve]

I used the same method.i'll send my code.can u please check it once.

[Sergey Berezovskiy]

@nanicharan I created tables recently with exactly same code. Just copy-paste it from msdn. And make sure you use CustomLINQtoDataSetMethods extension. E.g. you can call it directly CustomLINQtoDataSetMethods.CopyToDataTable(query)

[steve]

var query = from i in dbContext.Personaldetails where i.ID == 1 select i; DataTable table = query.CopyToDataTable(); I used in this way.I wrote this query inside a method.next to that method i declared CustomLINQtoDataSetMethods

[steve]

thanks, i changed the code.the error rectified.now i want to load the data into my dhtmlx grid.

[steve]

,error rectified at that statement but,in the CustomLINQtoDataSetMethods, i declared two public static DataTable CopyToDataTable<T>(this IEnumerable<T> source) { return new ObjectShredder<T>().Shred(source, null, null); } here it is showing "Extension method must defined in a top level static class"

[Sergey Berezovskiy]

Seems like you declared CustomLINQtoDataSetMethods class inside some other class. Move it to the top level, directly in some namespace.

[steve]

hi, I used copy to datatable method to convert the linq query to datatable and when i debug the application,it is throwing "NotsupportedException handled by user code" and "dataset doesnot support system.data.nullable" at the second foreach loop in Datatable Extendable method().

[Sergey Berezovskiy]

Also you can verify if property type is nullable type.IsGenericType && (type.GetGenericTypeDefinition() == typeof(Nullable<>)) and provide nullable parameter type instead p.PropertyType.GetGenericArguments()[0]