Welcome to MSDN Blogs Sign in | Join | Help

Man vs Code

invaluable minutiae

Syndication

Science Project

In the original Linq CTP and the first Orcas Beta, we included a DataSet specific Linq operator called CopyToDataTable<T> (It was called ToDataTable at one point also).  For Beta 2 of Orcas, we ended up restricting this method to only work with DataRows (or some derived type) via a generic constraint on the method. 

The reason for this was simply resource constraints.  When we started to design how the real version of CopyToDataTable<T> should work, we realized that there are a number of potentially interesting mappings between objects and DataRows and didn't have the resources to come up with a complete solution.  Hence, we decided to cut the feature and release the source as a sample.

Surprising to us, a lot of folks noticed this and were wondering where the feature had gone.  It does make a nice solution for dealing with projections in Linq in that one can load instances of anonymous types into DataRows. 

So as promised, below is sample code of how to implement CopyToDataTable<T> when the generic type T is not a DataRow. 

A few notes about this code:

1.  The initial schema of the DataTable is based on schema of the type T.  All public property and fields are turned into DataColumns.

2.  If the source sequence contains a sub-type of T, the table is automatically expanded for any addition public properties or fields.

3.  If you want to provide a existing table, that is fine as long as the schema is consistent with the schema of the type T.

4.  Obviously this sample probably needs some perf work.  Feel free to suggest improvements.

5.  I only included two overloads - there is no technical reason for this, just Friday afternoon laziness.

 

UPDATE 9/14 - Based on some feedback from akula, I have fixed a couple of issues with the code:

1) The code now supports loading sequences of scalar values.

2) Cases where the developer provides a datatable which needs to be completely extended based on the type T is now supported.

UPDATE 12/17 - In the comments, Nick Lucas has provided a solution to handling Nullable types in the input sequence.  I have not tried it yet, but it look like it works.

    class Sample
    {
        static void Main(string[] args)
        {
            // create sequence 
            Item[] items = new Item[] { new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Jim Bob"}, 
                                        new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "John Fox"},  
                                        new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Phil Funk"},
                                        new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Eddie Jones"}};

                        
            var query1 = from i in items
                         where i.Price > 9.99
                         orderby i.Price
                         select i;

            // load into new DataTable
            DataTable table1 = query1.CopyToDataTable();

            // load into existing DataTable - schemas match            
            DataTable table2 = new DataTable();
            table2.Columns.Add("Price", typeof(int));
            table2.Columns.Add("Genre", typeof(string));

            var query2 = from i in items
                         where i.Price > 9.99
                         orderby i.Price
                         select new {i.Price, i.Genre};

            query2.CopyToDataTable(table2, LoadOption.PreserveChanges);


            // load into existing DataTable - expand schema + autogenerate new Id.
            DataTable table3 = new DataTable();
            DataColumn dc = table3.Columns.Add("NewId", typeof(int));
            dc.AutoIncrement = true;
            table3.Columns.Add("ExtraColumn", typeof(string));

            var query3 = from i in items
                         where i.Price > 9.99
                         orderby i.Price
                         select new { i.Price, i.Genre };

            query3.CopyToDataTable(table3, LoadOption.PreserveChanges);

            // load sequence of scalars.

            var query4 = from i in items
                         where i.Price > 9.99
                         orderby i.Price
                         select i.Price;

            var DataTable4 = query4.CopyToDataTable();
        }

        public class Item
        {
            public int Id { get; set; }
            public double Price { get; set; }
            public string Genre { get; set; }   
        }

        public class Book : Item
        {
            public string Author { get; set; }
        }

        public class Movie : Item
        {
            public string Director { get; set; }
        }
        
    }

    public static class DataSetLinqOperators
    {
        public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)
        {
            return new ObjectShredder<T>().Shred(source, null, null);
        }

        public static DataTable CopyToDataTable<T>(this IEnumerable<T> source, 
                                                    DataTable table, LoadOption? options)
        {
            return new ObjectShredder<T>().Shred(source, table, options);
        }

    }

    public class ObjectShredder<T>
    {
        private FieldInfo[] _fi;
        private PropertyInfo[] _pi;
        private Dictionary<string, int> _ordinalMap;
        private Type _type;

        public ObjectShredder()
        {
            _type = typeof(T);
            _fi = _type.GetFields();
            _pi = _type.GetProperties();
            _ordinalMap = new Dictionary<string, int>();
        }

        public DataTable Shred(IEnumerable<T> source, DataTable table, LoadOption? options)
        {
            if (typeof(T).IsPrimitive)
            {
                return ShredPrimitive(source, table, options);   
            }
    

            if (table == null)
            {
                table = new DataTable(typeof(T).Name);
            }

            // now see if need to extend datatable base on the type T + build ordinal map
            table = ExtendTable(table, typeof(T));

            table.BeginLoadData();
            using (IEnumerator<T> e = source.GetEnumerator())
            {
                while (e.MoveNext())
                {
                    if (options != null)
                    {
                        table.LoadDataRow(ShredObject(table, e.Current), (LoadOption)options);
                    }
                    else
                    {
                        table.LoadDataRow(ShredObject(table, e.Current), true);
                    }
                }
            }
            table.EndLoadData();
            return table;
        }

        public DataTable ShredPrimitive(IEnumerable<T> source, DataTable table, LoadOption? options)
        {
            if (table == null)
            {
                table = new DataTable(typeof(T).Name);
            }

            if (!table.Columns.Contains("Value"))
            {
                table.Columns.Add("Value", typeof(T));
            }

            table.BeginLoadData();
            using (IEnumerator<T> e = source.GetEnumerator())
            {
                Object[] values = new object[table.Columns.Count];
                while (e.MoveNext())
                {
                    values[table.Columns["Value"].Ordinal] = e.Current;

                    if (options != null)
                    {
                        table.LoadDataRow(values, (LoadOption)options);
                    }
                    else
                    {
                        table.LoadDataRow(values, true);
                    }
                }
            }
            table.EndLoadData();  
            return table; 
        }

        public DataTable ExtendTable(DataTable table, Type type)
        {
            // value is type derived from T, may need to extend table.
            foreach (FieldInfo f in type.GetFields())
            {
                if (!_ordinalMap.ContainsKey(f.Name))
                {
                    DataColumn dc = table.Columns.Contains(f.Name) ? table.Columns[f.Name]
                        : table.Columns.Add(f.Name, f.FieldType);
                    _ordinalMap.Add(f.Name, dc.Ordinal);               
                }
            }
            foreach (PropertyInfo p in type.GetProperties())
            {
                if (!_ordinalMap.ContainsKey(p.Name))
                {
                    DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name]
                        : table.Columns.Add(p.Name, p.PropertyType);
                    _ordinalMap.Add(p.Name, dc.Ordinal);
                }
            }
            return table;
        }

        public object[] ShredObject(DataTable table, T instance)
        {

            FieldInfo[] fi = _fi;
            PropertyInfo[] pi = _pi;

            if (instance.GetType() != typeof(T))
            {
                ExtendTable(table, instance.GetType());
                fi = instance.GetType().GetFields();
                pi = instance.GetType().GetProperties();
            }

            Object[] values = new object[table.Columns.Count];
            foreach (FieldInfo f in fi)
            {
                values[_ordinalMap[f.Name]] = f.GetValue(instance);
            }

            foreach (PropertyInfo p in pi)
            {
                values[_ordinalMap[p.Name]] = p.GetValue(instance, null);
            }
            return values;
        }
    }

Published Friday, September 07, 2007 5:03 PM by aconrad

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# re: Science Project @ Saturday, September 08, 2007 3:22 AM

Great thanks!!! This is good way!

Moristar

# re: Science Project @ Saturday, September 08, 2007 1:33 PM

Pingback from http://oakleafblog.blogspot.com/2007/09/linq-and-entity-framework-updates-for.html.

rogerj

# re: Science Project @ Saturday, September 08, 2007 3:19 PM

Sorry, entry moved to http://oakleafblog.blogspot.com/2007/09/linq-and-entity-framework-posts-for.html

--rj

rogerj

# re: Science Project @ Sunday, September 09, 2007 1:02 AM

This is great and was exactly what I needed.  I do get an parameter mismatch error in

           foreach (PropertyInfo p in pi)

           {

               values[_ordinalMap[p.Name]] = p.GetValue(instance,null);

           }

if I use:

           var a = (from m_var in dc.Ptabs

                    select m_var.CAR ).Distinct();

           DataSet ds = new DataSet();

           ds.Tables.Add(a.CopyToDataTable());

but not if I do this

           var a = (from m_var in dc.Ptabs

                    select new { Car = m_var.CAR }).Distinct();

           DataSet ds = new DataSet();

           ds.Tables.Add(a.CopyToDataTable());

akula

# re: Science Project @ Monday, September 10, 2007 9:50 PM

thanks - there are a couple of problems here:

1)  The code is not catching the error case when the type T of the source sequence does not match the schema of the provided datatable.  I suppose I could extend the table automatically in this case.

2)  The results of your query is just a sequence of scaler values.  The code wasn't really designed for this and I am not sure I see much value, but I suppose I could just make a table with a single column.

I will update the sample code to fix these issues.

aconrad

# re: Science Project @ Wednesday, September 12, 2007 2:25 AM

Thanks

akula

# re: Science Project @ Wednesday, September 19, 2007 8:46 AM

This seems very helpful, is there a straightforward way to implement in vb.net?

jonm

# re: Science Project @ Monday, November 26, 2007 3:10 PM

Hi, this post was helpful.

However it seems to have problem when used with nullable types.

I am using LINQ to SQL data context calss to store data base tables.Then I query these tables and get result of type "var" and then convert it to datatable using this code.

Some of the tables are of nullable type.So while conversion I receive an error saying "DataSet does not support System.Nullable<>"

inside the ExtendTable method, when the code tries to add columns to the "table"

Pls let me know if you have any suggestions/workaround to this problem.

Thanks in advance

Regards,

Neeta

Neeta

# re: Science Project @ Tuesday, December 11, 2007 1:32 AM

ah - I will try to get the code working with nullable types over the holidays.

aconrad

# re: Science Project @ Wednesday, December 19, 2007 6:08 AM

Change the code to be this in order to handle nullable types:

foreach (PropertyInfo p in type.GetProperties())

           {

               if (!_ordinalMap.ContainsKey(p.Name))

               {

                   Type colType = p.PropertyType;

                   if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))

                   {

                       colType = colType.GetGenericArguments()[0];

                   }

                   DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name]

                       : table.Columns.Add(p.Name, colType);

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

               }

           }

Nick Lucas

# re: Science Project @ Thursday, December 27, 2007 4:43 PM

Wow thank you so much! This allows me to use a LINQ query in my DAL and then bind an Object Data Source control to it. Then I can bind the GridView to the Object Data Source control and turn on sorting and paging and it all works!

adefwebserver

# re: Science Project @ Wednesday, January 09, 2008 4:13 AM

Thank you all.Is there a VB version of the complete code anywhere else?Any help appreciated much.

Yigit Ozmen

# re: Science Project @ Wednesday, January 09, 2008 4:01 PM

I am trying to use the above idea to convert entities into datatables. However, the linq query does not expose the CopyToDataTable() method. Am I missing something here?

eyunni

# re: Science Project @ Friday, January 11, 2008 1:42 PM

Make sure you use the namespace the you defined the DataSetLinqOperators in.

Andrew Conrad

# Pathetic Plea for help @ Friday, January 11, 2008 2:12 PM

A number of people have asked me for a VB version of the CopyToDataTable&lt;T&gt; sample I wrote a few

Man vs Code

# Pathetic Plea for help @ Friday, January 11, 2008 2:54 PM

A number of people have asked me for a VB version of the CopyToDataTable&lt;T&gt; sample I wrote a few

Noticias externas

# re: Science Project @ Wednesday, January 16, 2008 5:35 AM

I cannot find any CopyToDataTable() method......

btw, i think if we just want a datatable , using the code above is

sooooooooooooo complex.

Ming

# re: Science Project @ Tuesday, January 22, 2008 12:09 PM

Awesome.

I added in the change for Nullable types by Nick and the whole thing is working beautifully. Only thing of note is that I had to change the method names due to a conflict. I am going to check that out.

Cheers.

Viper

# LINQ CopytoDataTable in C# and in VB @ Tuesday, January 22, 2008 3:14 PM

There was a CopytoDataTable method in early betas of LINQ but then it disappeared. C#: Andy Conrad on

Hot Topics

# re: Science Project @ Wednesday, February 06, 2008 4:01 PM

any code out there that can help load a dlinq object FROM a datatable? Im workin with webservices and still passing datasets, so would like to load up a bunch of dlinq entities from the datatables and commit them to the db... probably easier to just use the datatables, i guess...

boomhauer

# 实现CopyToDataTable @ Wednesday, April 16, 2008 11:55 AM

LINQ to DataSet中实现CopyToDataTable

works guo

# LinQ to StimulReport ? lol (Part 1) @ Thursday, April 17, 2008 4:53 PM

A utilização do LinQ em projetos dentro do TJMT forçou uma estrutura de projeto, mas ainda não estamos

Desenvolvimento

# re: Science Project @ Wednesday, April 23, 2008 4:19 AM

I was looking the same thing.

I tried the above solution but for various reasons I was not satisfied.

One of the biggest reasons was that I like using Typed Datasets.

So I tried to create my own convertion method.It stated as a proof o concept and later became something that could be done.

Here is the solution I propose

http://sarafianalex.wordpress.com/2008/04/21/typed-dataset-linq-entities/

Alex Sarafian

# re: Science Project @ Thursday, July 24, 2008 7:54 PM

Hi,

There seems to be a problem with the ExtendTable routine and I'm not sure how to solve it. In the routine ExtendTable there is the following code:

       For Each p As PropertyInfo In type.GetProperties()

           If Not _ordinalMap.ContainsKey(p.Name) Then

               Dim colType As Type = p.PropertyType

               If (colType.IsGenericType) AndAlso (colType.GetGenericTypeDefinition() Is GetType(Nullable(Of ))) Then

                   colType = colType.GetGenericArguments()(0)

               End If

               Dim dc As DataColumn = IIf(table.Columns.Contains(p.Name), table.Columns(p.Name), table.Columns.Add(p.Name, colType))

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

           End If

       Next

The issue seems to be that type.GetProperties() returns the columns in alphabetical order instead of the order returned from the query. Can anyone offer some ideas on how to get them back in the right order. Or, at least to be able to contruct the datatable with the columns in the right order.

Thanks ... Ed

Ed Staffin

# re: Science Project @ Thursday, November 06, 2008 3:07 PM

I'm having the same issue as Ed.  Has anyone figured how to return columns in the same order as the query?

Thanks,

Scott

Scott Kleinert

# re: Science Project @ Thursday, November 20, 2008 3:55 PM

A note on the column order...I've noticed that on my vista laptop, the order of the columns is returned as expected.  On my XP desktop, the columns are returned alphabetically.  Still don't know why...though it might help someone smarter than I.

Scott

Scott Kleinert

# re: Science Project @ Thursday, November 20, 2008 3:55 PM

A note on the column order...I've noticed that on my vista laptop, the order of the columns is returned as expected.  On my XP desktop, the columns are returned alphabetically.  Still don't know why...though it might help someone smarter than I.

Scott

Scott Kleinert

# Doesn't compile on VS 2008 @ Thursday, August 13, 2009 5:25 PM

For me it throws exactly the same compilation error

error CS0311: The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable<T>(System.Collections.Generic.IEnumerable<T>, System.Data.DataTable, System.Data.LoadOption)'. There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'.

My code is :

           DataTable tableSimCnfCopy = tableSimCnf.Clone ();

           var varLst2 =

               from car in tableSimCnf.AsEnumerable()

               select new

               {

                   ModelID = car.ModelID,

                   VehiclePrice = car.VehiclePrice,

                   APR24PercDown = car.APR24PercDown,

                   APR36PercDown = car.APR36PercDown,

                   APR48PercDown = car.APR48PercDown,

                   APR60PercDown = car.APR60PercDown,

                   APR72PercDown = car.APR72PercDown

               };

           varLst2.CopyToDataTable(tableSimCnfCopy, LoadOption.PreserveChanges);

The last line is the line I'm getting this compile error

Nikolay Sonin

Leave a Comment

(required) 
required 
(required) 

  
Enter Code Here: Required
Page view tracker