none
How to get DataTable from LINQ query?

    Question

  • Hi everybody,

    I am working on a translation of VFP code into C#. I have the following VFP code:

    if prefs_bk.prior_sort=2
             lcOrder="i.requested, i.pricount, i.crtcount desc, i.priority1 desc, i.priority2 desc"
          else
             lcOrder="i.requested, i.pricount, i.crtcount desc, i.priority1, i.priority2"
          endif
    
          select I.instr_id as resource1, I.fname, I.lname, I.CheckedIn, ;
             b.booking_id, b.start_time, b.end_time, b.flex_tspan, ;
             b.layer, b.backcolor, b.forecolor, b.clip_sp, b.clip_loc, ;
             padr(space(iif(b.locked,2,0))+iif(empty(b.department+b.category+b.item), ;
             replicate('**'+alltrim(b.lsndescrip)+'** ',40), ;
             alltrim(b.modifiers)+' '+proper(alltrim(b.gst_name))+' '+alltrim(b.lsndescrip)+' '+ ;
             alltrim(str(b.booking_id,16))),200) as display_msg, 'PVT' as bartype, b.locked, ;
             iif(nvl(b.notelength,0)>0, .t., .f.) as isnotes;
             from csrInstr2 I;
             left outer join csrBookInfo b on I.instr_id==b.resource1 ;
             into cursor csrRetCursor ;
             order by &lcOrder

    which I translated for now (without order by) as 

     var resultingQuery = from instructors in dtResources.AsEnumerable()
                                        join bookings in dtBookingInfo.AsEnumerable()
                                        on instructors.Field<String>("resource1") equals bookings.Field<String>("resource1")
                                        into AllInfo
                                        from bookings in AllInfo.DefaultIfEmpty()
                                        select new
                                        {
                                           resource1 = instructors.Field<String>("resource1")
                                        };

    (I obviously only created 1 column instead of all since I am playing with it).

    As you see, I have 2 datatables: dtResources (which is csrInstr2 in the VFP code) and dtBookingInfo (which is csrBookInfo) in the above code. So, I need to LEFT JOIN Instructors to their bookings.

    Assuming my LINQ is correct, how can I get the datatable as my final result? I found this blog post http://blogs.msdn.com/b/aconrad/archive/2007/09/07/science-project.aspx which sounds like a complex method.

    Is there anything else that will allow me to get my results as a DataTable and also sort it in the desired way?

    Thanks a lot in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, August 15, 2013 10:37 PM

Answers

  • Yeah, I was annoyed when there wasn't something in the BCL that would help translate between typed collections and ADO.NET DataTable/DataReader.  I initially needed it to use SqlBulkCopy with in-memory collections.  

    So I wrote this: LINQ Entity Data Reader


    It allows you to take an IEnumerable<T> (which all LINQ query results are), and adapt it to a DataReader or a DataTable.

    eg:

     static void Main(string[] args)
        {
          using (var db = new testDataContext())
          {
            var q = from o in db.sales_facts
                    select o;
     
            DataTable t = q.ToDataTable();
            
     
            t.WriteXml(Console.Out);
          }
          Console.ReadKey();
        }
      }

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Naomi N Friday, August 16, 2013 2:07 AM
    Friday, August 16, 2013 12:18 AM
  • >In particular, what library should I add as a reference to resolve these errors? 

      System.Data.Objects.DataClasses;
      System.Data.Metadata.Edm;
      System.Data.Objects;

    Are all in System.Data.Entity.dll.  The EntityDataReader has some special support for EntityFramework types, which required importing those namespaces.  If you have EntityFramework in your project you'll have all the required assemblies referenced.

    Otherwise the required assemblies are

    System.dll

    System.Data.dll

    System.Data.Entity.dll

    System.XML.dll

    And feel free to ping me directly.  My microsoft email alias is dbrowne.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Naomi N Friday, August 16, 2013 7:31 PM
    Friday, August 16, 2013 5:23 AM
  • That link shows a good way to do it.

    I don't believe there is a simple way to do it, other than rebuilding the DataTable.

    It is documented officially here also. And here.

     

    Regards,
    Pete


    #PEJL Got a good solution? If you invest your time in coding an elegant/novel or large answer on these MSDN forums, why not copy it over to our beloved TechNet Wiki, for future generations to benefit from!


    • Edited by XAML guyEditor Thursday, August 15, 2013 11:31 PM typo
    • Marked as answer by Naomi N Friday, August 16, 2013 2:05 AM
    Thursday, August 15, 2013 11:28 PM

All replies

  • That link shows a good way to do it.

    I don't believe there is a simple way to do it, other than rebuilding the DataTable.

    It is documented officially here also. And here.

     

    Regards,
    Pete


    #PEJL Got a good solution? If you invest your time in coding an elegant/novel or large answer on these MSDN forums, why not copy it over to our beloved TechNet Wiki, for future generations to benefit from!


    • Edited by XAML guyEditor Thursday, August 15, 2013 11:31 PM typo
    • Marked as answer by Naomi N Friday, August 16, 2013 2:05 AM
    Thursday, August 15, 2013 11:28 PM
  • Yeah, I was annoyed when there wasn't something in the BCL that would help translate between typed collections and ADO.NET DataTable/DataReader.  I initially needed it to use SqlBulkCopy with in-memory collections.  

    So I wrote this: LINQ Entity Data Reader


    It allows you to take an IEnumerable<T> (which all LINQ query results are), and adapt it to a DataReader or a DataTable.

    eg:

     static void Main(string[] args)
        {
          using (var db = new testDataContext())
          {
            var q = from o in db.sales_facts
                    select o;
     
            DataTable t = q.ToDataTable();
            
     
            t.WriteXml(Console.Out);
          }
          Console.ReadKey();
        }
      }

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Naomi N Friday, August 16, 2013 2:07 AM
    Friday, August 16, 2013 12:18 AM
  • Hi David,

    Is there a way to contact you by email? 

    Thanks again and I am going to study your method also.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, August 16, 2013 2:07 AM
  • I downloaded the class and included it into the main project, but I immediately got many errors. In particular, what library should I add as a reference to resolve these errors?

      using System.Data.Objects.DataClasses;
      using System.Data.Metadata.Edm;
      using System.Data.Objects;

    Also, I am targeting this project for .NET 4.0 and I don't see System.Data.Objects as a library to include when I try to add a reference. What should I include as a reference? I tried to search MS but as always I didn't get answer on this simple question.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Friday, August 16, 2013 2:34 AM
    Friday, August 16, 2013 2:23 AM
  • >In particular, what library should I add as a reference to resolve these errors? 

      System.Data.Objects.DataClasses;
      System.Data.Metadata.Edm;
      System.Data.Objects;

    Are all in System.Data.Entity.dll.  The EntityDataReader has some special support for EntityFramework types, which required importing those namespaces.  If you have EntityFramework in your project you'll have all the required assemblies referenced.

    Otherwise the required assemblies are

    System.dll

    System.Data.dll

    System.Data.Entity.dll

    System.XML.dll

    And feel free to ping me directly.  My microsoft email alias is dbrowne.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Naomi N Friday, August 16, 2013 7:31 PM
    Friday, August 16, 2013 5:23 AM
  • David,

    Thanks a lot. Yesterday I already went with the first solution. Do you know the advantages of your class over the documented solution?

    Also, I am thinking that I am over-complicating the problem. In my case at the end I need to return the XML like string. We have several methods in our class that create that string but they accept datatable. So, I need to get the datatable in order to pass it to these methods that will loop through each row to create that string. 

    So, I will be doing double work.



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, August 16, 2013 12:34 PM
  • >Do you know the advantages of your class over the documented solution?

     System.Data.DataTableExtensions is part of the LINQ enablement work for ADO.NET, and works only with DataReaders or LINQ qeries that return DataRow objects.

    For instance if you try to take an arbitrary IEnumerable<T> and use that method:

          var q = Enumerable.Range(1, 100).Select(a => new { id = a, name = a.ToString() });
          var dt = q.CopyToDataTable(); 


    It fails with: 

    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>)'. There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'.

    Since it only knows how to copy DataRow objects into a DataTable.  

    The other option documented here How to: Implement CopyToDataTable<T> Where the Generic Type T Is Not a DataRow is similar to the LINQ Entity Data Reader sample.  Mine has more functionality, like it can project a DataReader for streaming access, and it uses dynamically compiled expressions for the property accessors, so it performs with bulk data.  It also supports flattening related records, and handles some subtle nastiness in how the reflection APIs return property collections.  Also I wrote mine first :)

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, August 16, 2013 1:34 PM
  • Hi Naomi!

    Have you tried using the .CopyToDataTable(0) method? I've used it without problems:

    http://msdn.microsoft.com/en-us/library/bb386921(v=vs.90).aspx


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, August 16, 2013 4:39 PM
  • You can not use CopyToDataTable method when you do a join between different tables (as in my case) and return a new anonymous type. That link has a section down below that describes how to create a custom CopyToDataTable method. I've tried that and I tried David's class, but in both cases I am getting a warning that requires me to set ComVisible(false) attribute in the assembly. I am wondering, is it possible to do only for David's class and still use it in my main dll and be able to access it through com if needed?

    If so, what should I do?

    UPDATE. Changing David's class from public to internal got rid of the warning. I almost have it working now, but I am a bit worried about ordering. See my question in UT.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Friday, August 16, 2013 7:33 PM
    Friday, August 16, 2013 4:49 PM
  • You can not use CopyToDataTable method when you do a join between different tables (as in my case) and return a new anonymous type. <

    Sorry, Naomi ... I guess I missed that part.

    UPDATE. Changing David's class from public to internal got rid of the warning. I almost have it working now, but I am a bit worried about ordering. See my question in UT.

    I've been bad, I haven't been to the UT in months! I keep meaning to get back there, but I spend most of my "free" time here. I think I'll head over there sometime today and see what's up. =0)


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, August 17, 2013 3:42 PM