Converting an entity model to a dataset - can this be done?

Question

Very frustrated here ... I can usually find an answer of some kind to complex issues in .Net somewhere on the net, but this one eludes me. I'm in a scenario where I have to convert the result of a LINQ to Entity query into a DataSet so the data can then be processed by existing business logic, and I can't find a single working solution out ther for this.

I've tried basic approaches like the EntityCommand generating a reader, but this one does not work because DataTable.Load() thorws an excpetion (the reader generated by EntityCommand does not support GetSchemaTable() ).

I've also tried more [supposedly] friendly approaches like Entity to IDataReader(http://l2edatareaderadapter.codeplex.com/), but this one throws exceptions, has very little docs, and hasn't been touched since 2008.

Another approach I found is here (http://blogs.msdn.com/b/alexj/archive/2007/11/27/hydrating-an-entitydatareader-into-a-datatable-part-1.aspx), but does not have a working copy of the code; only snippets.

I find it hard to believe that first of all MS would not have offered this backwards-compatibility item out of the box, and second, that it would not have been created by the community either.

I'm willing to look at commercial solutions as well if any are available.

Thx!

score 3 · Answer 1 · answered Nov 29 '12 at 22:43

You can convert the result into a list and use the following to convert the list to a datatable.

public DataTable ConvertToDataTable<T>(IList<T> data)
    {
        PropertyDescriptorCollection properties =
           TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;

    }

http://social.msdn.microsoft.com/Forums/br/csharpgeneral/thread/6ffcb247-77fb-40b4-bcba-08ba377ab9db

Hope this helps

Preetam

Interesting approach. Will have to try it out and get back to you. Thx for the idea. — David Catriel, Dec 01 '12 at 00:41

score 0 · Answer 2 · answered Jun 27 '13 at 06:29

This is a flexible code and should handle most of your needs:

public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
    {
        DataTable dtReturn = new DataTable();
        // column names
        PropertyInfo[] oProps = null;
        if (varlist == null) return dtReturn;
        foreach (T rec in varlist)
        {
            // Use reflection to get property names, to create table, Only first time, others will follow
            if (oProps == null)
            {
                oProps = ((Type)rec.GetType()).GetProperties();
                foreach (PropertyInfo pi in oProps)
                {
                    Type colType = pi.PropertyType;
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }

                    dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                }
            }

            DataRow dr = dtReturn.NewRow();
            foreach (PropertyInfo pi in oProps)
            {
                dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                (rec, null);
            }

            dtReturn.Rows.Add(dr);
        }

        return dtReturn;
    }

score 0 · Answer 3 · answered May 19 '11 at 19:45

0

This might not be the greatest solution, but if your scenario have only one or two table that you need to add to the DataSet, why not build them directly manually.

var result = db.YourTable; // get your Linq To Entities result.

DataSet ds = new DataSet();
DataTable tbl = new DataTable();
tbl.Columns.Add("col1", typeof(string));
tbl.Columns.Add("col2", typeof(int));

foreach (var r in result)
{
  var row = tbl.NewRow();
  row[0] = r.Col1;
  row[1] = r.Col2;

  tbl.Rows.Add(r);

}

ds.Tables.Add(tbl);

The Col1 and Col2 comes from your Linq To Entity objects, you can create all the table you need like this and return your DataSet.

answered May 19 '11 at 19:45

Dominic St-Pierre

2,429
3
27
35

Thx Dominic. Tried something similar. One huge issue - large entities (i.e. that contain a lot of data). To get around this, I had a recursive method going down the entity tree and starting a new copy of itself (in a new thread) for every new level it started copying, but guess what - DataTable objects are not thread safe. Their internal indexes get corrupted very easily. As soon as you try to add rows into them from multiple threads, you get exceptions like http://stackoverflow.com/questions/450675/datatable-internal-index-is-corrupted. – David Catriel May 21 '11 at 12:55
I was not aware of DataTable not being thread safe. In that case I guess the easiest thing to do would be to fill the DataSet from a SqlCommand and not passing from your entities. – Dominic St-Pierre Jun 02 '11 at 09:26

Converting an entity model to a dataset - can this be done?

3 Answers3