Click here to Skip to main content
14,090,690 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

43.9K views
26 bookmarked
Posted 3 Sep 2014
Licenced CPOL

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 
Thanks.

Not clear about GetValue() method though. I could not find any reference of such overload in MSDN. But when I go to the definition of the method, it takes to:

C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\mscorlib.dll

// Summary:
        //     Returns the property value of a specified object.
        //
        // Parameters:
        //   obj:
        //     The object whose property value will be returned.
        //
        // Returns:
        //     The property value of the specified object.
        [DebuggerHidden]
        [DebuggerStepThrough]
        public object GetValue(object obj);
        //
        // Summary:
        //     Returns the property value of a specified object with optional index values
        //     for indexed properties.
        //
        // Parameters:
        //   obj:
        //     The object whose property value will be returned.
        //
        //   index:
        //     Optional index values for indexed properties. This value should be null for
        //     non-indexed properties.
        //
        // Returns:
        //     The property value of the specified object.
        //
        // Exceptions:
        //   System.ArgumentException:
        //     The index array does not contain the type of arguments needed.-or- The property's
        //     get accessor is not found.
        //
        //   System.Reflection.TargetException:
        //     The object does not match the target type, or a property is an instance property
        //     but obj is null.
        //
        //   System.Reflection.TargetParameterCountException:
        //     The number of parameters in index does not match the number of parameters
        //     the indexed property takes.
        //
        //   System.MethodAccessException:
        //     There was an illegal attempt to access a private or protected method inside
        //     a class.
        //
        //   System.Reflection.TargetInvocationException:
        //     An error occurred while retrieving the property value. For example, an index
        //     value specified for an indexed property is out of range. The System.Exception.InnerException
        //     property indicates the reason for the error.
        [DebuggerHidden]
        [DebuggerStepThrough]
        public virtual object GetValue(object obj, object[] index);
        //
        // Summary:
        //     When overridden in a derived class, returns the property value of a specified
        //     object that has the specified binding, index, and culture-specific information.
        //
        // Parameters:
        //   obj:
        //     The object whose property value will be returned.
        //
        //   invokeAttr:
        //     A bitwise combination of the following enumeration members that specify the
        //     invocation attribute: InvokeMethod, CreateInstance, Static, GetField, SetField,
        //     GetProperty, and SetProperty. You must specify a suitable invocation attribute.
        //     For example, to invoke a static member, set the Static flag.
        //
        //   binder:
        //     An object that enables the binding, coercion of argument types, invocation
        //     of members, and retrieval of System.Reflection.MemberInfo objects through
        //     reflection. If binder is null, the default binder is used.
        //
        //   index:
        //     Optional index values for indexed properties. This value should be null for
        //     non-indexed properties.
        //
        //   culture:
        //     The culture for which the resource is to be localized. If the resource is
        //     not localized for this culture, the System.Globalization.CultureInfo.Parent
        //     property will be called successively in search of a match. If this value
        //     is null, the culture-specific information is obtained from the System.Globalization.CultureInfo.CurrentUICulture
        //     property.
        //
        // Returns:
        //     The property value of the specified object.
        //
        // Exceptions:
        //   System.ArgumentException:
        //     The index array does not contain the type of arguments needed.-or- The property's
        //     get accessor is not found.
        //
        //   System.Reflection.TargetException:
        //     The object does not match the target type, or a property is an instance property
        //     but obj is null.
        //
        //   System.Reflection.TargetParameterCountException:
        //     The number of parameters in index does not match the number of parameters
        //     the indexed property takes.
        //
        //   System.MethodAccessException:
        //     There was an illegal attempt to access a private or protected method inside
        //     a class.
        //
        //   System.Reflection.TargetInvocationException:
        //     An error occurred while retrieving the property value. For example, an index
        //     value specified for an indexed property is out of range. The System.Exception.InnerException
        //     property indicates the reason for the error.
        public abstract object GetValue(object obj, BindingFlags invokeAttr, Binder binder, object[] index, CultureInfo culture);

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 | Cookies | Terms of Use | Mobile
Web01 | 2.8.190518.1 | Last Updated 3 Sep 2014
Article Copyright 2014 by debashishPaul
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid