How to Convert LINQ Query to DataTable

debashishPaul

Rate me:

5.00/5 (8 votes)

3 Sep 2014CPOL

64.3K

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)

Written By

debashishPaul

Software Developer (Senior)

Australia

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

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