Click here to Skip to main content
13,139,569 members (53,899 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

29.5K views
24 bookmarked
Posted 3 Sep 2014

How to Convert LINQ Query to DataTable

, 3 Sep 2014
Rate this:
Please Sign up or sign in to vote.
How to convert LINQ query to DataTable

Introduction

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));
        }
        else
        {
            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.Rows.Add(row);
    }

    //Table is ready to serve.
    table.AcceptChanges();

    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);

License

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

Share

About the Author

debashishPaul
Software Developer (Senior)
Australia Australia
No Biography provided

You may also be interested in...

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170915.1 | Last Updated 3 Sep 2014
Article Copyright 2014 by debashishPaul
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid