Click here to Skip to main content
15,441,032 members
Articles / Programming Languages / C# 5.0
Posted 3 Sep 2014

Tagged as


26 bookmarked

How to Convert LINQ Query to DataTable

Rate me:
Please Sign up or sign in to vote.
5.00/5 (8 votes)
3 Sep 2014CPOL
How to convert LINQ query to DataTable


LINQ is already popular with developers and it is getting more popular day by day for the certain advantages LINQ has. Now this tip is not about LINQ, but about something we need frequently for our day to day development - Converting LINQ query to DataTable.

First Things First, Planning

We will be developing a method which will accept the LINQ query, without any idea what it would be, as a parameter. Process its contents into a "DataTable" and return it. So, to receive the query, we will be using "dynamic" datatype which is very new to the context. Then, we will find out its "Property Information" and ready our DataTable for inserting data. After that, we will iterate through the query and populate our DataTable.

Second Thing Always Comes after the First, Coding

public static DataTable LinqQueryToDataTable(IEnumerable<dynamic> v)
    //We really want to know if there is any data at all
    var firstRecord = v.FirstOrDefault();
    if (firstRecord == null)
        return null;

    /*Okay, we have some data. Time to work.*/

    //So dear record, what do you have?
    PropertyInfo[] infos = firstRecord.GetType().GetProperties();

    //Our table should have the columns to support the properties
    DataTable table = new DataTable();
    //Add, add, add the columns
    foreach (var info in infos)
        Type propType = info.PropertyType;
        if (propType.IsGenericType
            && propType.GetGenericTypeDefinition() == typeof(Nullable<>)) //Nullable types should be handled too
            table.Columns.Add(info.Name, Nullable.GetUnderlyingType(propType));
            table.Columns.Add(info.Name, info.PropertyType);

    //Hmm... we are done with the columns. Let's begin with rows now.
    DataRow row;

    foreach (var record in v)
        row = table.NewRow();
        for (int i = 0; i < table.Columns.Count; i++)
            row[i] = infos[i].GetValue(record) != null ? infos[i].GetValue(record) : DBNull.Value;


    //Table is ready to serve.

    return table;

Last Thing

The last thing I can think of is calling the freshly baked method. We will begin with a class:

public class Item
    public int Id { get; set; }
    public decimal Price { get; set; }
    public string Genre { get; set; }
    public string Author { get; set; }
    public string Director { get; set; }

Now, the LINQ query:

Item[] items = new Item[] { 
    new Item{Id = 1, Price = (decimal)13.50, Genre = "Comedy", Author = "Gustavo Achong"}, 
    new Item{Id = 2, Price = (decimal)8.50, Genre = "Drama", Author = "Jessie Zeng"},
    new Item{Id = 3, Price = (decimal)22.99, Genre = "Comedy", Director = "Marissa Barnes"},
    new Item{Id = 4, Price = (decimal)13.40, Genre = "Action", Director = "Emmanuel Fernandez"}

var v = from i in items
        where i.Genre == "Comedy"
        select i;

DataTable dt = LinqQueryToDataTable(v);


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
Australia Australia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionDynamic - firstRecord.GetType().GetProperties(); Pin
Leexo10-Sep-14 5:43
MemberLeexo10-Sep-14 5:43 
AnswerRe: Dynamic - firstRecord.GetType().GetProperties(); Pin
debashishPaul10-Sep-14 20:29
professionaldebashishPaul10-Sep-14 20:29 
GeneralRe: Dynamic - firstRecord.GetType().GetProperties(); Pin
Leexo10-Sep-14 22:27
MemberLeexo10-Sep-14 22:27 
GeneralRe: Dynamic - firstRecord.GetType().GetProperties(); Pin
debashishPaul11-Sep-14 6:13
professionaldebashishPaul11-Sep-14 6:13 
QuestionWhy IEnumerable<dynamic>? Pin
Francesco Bagnasco10-Sep-14 4:45
MemberFrancesco Bagnasco10-Sep-14 4:45 
AnswerRe: Why IEnumerable<dynamic>? Pin
debashishPaul10-Sep-14 5:27
professionaldebashishPaul10-Sep-14 5:27 
GeneralRe: Why IEnumerable<dynamic>? Pin
Francesco Bagnasco10-Sep-14 6:16
MemberFrancesco Bagnasco10-Sep-14 6:16 
GeneralRe: Why IEnumerable<dynamic>? Pin
debashishPaul10-Sep-14 20:48
professionaldebashishPaul10-Sep-14 20:48 
GeneralRe: Why IEnumerable<dynamic>? Pin
Francesco Bagnasco11-Sep-14 3:24
MemberFrancesco Bagnasco11-Sep-14 3:24 
GeneralRe: Why IEnumerable<dynamic>? Pin
debashishPaul11-Sep-14 6:27
professionaldebashishPaul11-Sep-14 6:27 
GeneralRe: Why IEnumerable<dynamic>? Pin
Francesco Bagnasco11-Sep-14 7:48
MemberFrancesco Bagnasco11-Sep-14 7:48 
QuestionDataColumn Pin
James Curran8-Sep-14 6:25
MemberJames Curran8-Sep-14 6:25 
QuestionCopyToDataTable Pin
Pikoh3-Sep-14 23:19
MemberPikoh3-Sep-14 23:19 
AnswerRe: CopyToDataTable Pin
debashishPaul4-Sep-14 0:36
professionaldebashishPaul4-Sep-14 0:36 
AnswerRe: CopyToDataTable Pin
TheTruQ4-Sep-14 8:10
MemberTheTruQ4-Sep-14 8:10 
GeneralRe: CopyToDataTable Pin
debashishPaul4-Sep-14 18:53
professionaldebashishPaul4-Sep-14 18:53 
GeneralRe: CopyToDataTable Pin
Pikoh4-Sep-14 21:14
MemberPikoh4-Sep-14 21:14 
AnswerRe: CopyToDataTable Pin
James Curran8-Sep-14 6:23
MemberJames Curran8-Sep-14 6:23 
GeneralRe: CopyToDataTable Pin
Pikoh8-Sep-14 20:50
MemberPikoh8-Sep-14 20:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.