none
Convert query result to DataTable

    Question

  • Hi. I have a problem - I don't know how to convert result of my LINQ2Entity query to a DataTable. I really need DataTable. So if query like this:

     

    Code Snippet

    var query = from pList in eSaleEntities.PriceLists

                select new

                {

           Name = pList.Name,

      GroupName = pList.GoodGroups.Name,

      Measurement = pList.Measurements.ShortName

    };

     

     

    what I need to write down to get result as DataTable object?

     

    Friday, September 07, 2007 1:33 PM

Answers

  • [Note: apparenlty I was writing this while Danny was writing his, so this doesn't take into account his explanation.]

     

    This may be a seriuosly overkill solution, but i had fun figuring it out!

     

    Just as an FYI, LINQ to Datasets does allow for conversions from queries to Datatables, but it doesn't seem to be able to deal with ObjectQueries. I then queried my objectquery to return an IQueryable and also an IEnumerable, but I couldn't get the datatable conversion to work. Look for CopyToDataTable in the VS2008 documentation for more info on that.

     

    So since I couldn't get at it I rolled my own using Mike Taulty's previous example (not for ObjectQueries though) as a start.

     

    This example uses the ObjectStateManager so I can dynamically get at the values. As long as I'm already using the ObjetStateManager, I also use it to get some metadata even though I could have used the MetadataWorkspace for that.

     

    Assuming we are starting with nwrows which is an ObjectQuery for a simple Linq to Entities query...

     

    Code Snippet

      Dim dt As New DataTable

     

      Dim currow As Integer = 0  'just a counter to keep track of my for each position


      For Each r In nwrows

      'get statemanager for current row
       Dim stateEntry = nwts.ObjectStateManager.GetObjectStateEntry(CType(r, Data.Objects.DataClasses.IEntityWithKey).EntityKey)

     

       'this will only happen once, build the dataColumns, I'm not bothering with their names


       If dt.Columns.Count = 0 Then
        For i = 0 To stateEntry.CurrentValues.FieldCount - 1
         dt.Columns.Add()
        Next
       End If

     

       'now we can add data, grabbing current values
       Dim dtrow As DataRow = dt.NewRow

       For idata = 0 To stateEntry.CurrentValues.FieldCount - 1
        dtrow.Item(idata) = stateEntry.CurrentValues(idata)
       Next

     

       dt.Rows.Add(dtrow)

     

       currow += 1


      Next

     

    This definitely works! Again, maybe overkill, but at least it's a good introduction to statemanager!

     

    Friday, September 07, 2007 4:46 PM
  • Unfortunately there is no easy/automatic way to do this.  LINQ to Entities is focused on scenarios where the goal is custom objects not DataSets/DataTables.  Certainly you could take the code above, iterate over the created objects and copy data out into a DataTable but it would be ugly and inefficient.

     

    We have talked about the possibility of creating a DataAdapter that runs directly on top of EntityClient so that you could query your conceptual model with eSQL and then load results into a DataTable, but in the general case this requires either a DataSet that fully supports EDM constructs (including nesting, polymorphism, relationships, etc.) which is clearly not the case right now or some kind of mapping between EDM and some conventions in the DataSet which begins to make this whole thing look less valuable (map from relational database to a conceptual EDM model and then back to something relational for my in memory structure).

     

    For simple cases like you have above where you project into something that's just a flat list, this may seem a little funny, but unfortunately that's the way it is for the first release.

     

    - Danny

    Friday, September 07, 2007 4:27 PM
  • Hi all, I saw this thread earlier and just ran across a cool post... thought Id post a link. Uses IEnumerable<T> extension methods to copy T values into a datatable based on a schema of T. Looks like it will automagically add columns for sub-types of T as well.

     

    http://blogs.msdn.com/aconrad/archive/2007/09/07/science-project.aspx

     

     

     

     

     

    Saturday, September 08, 2007 6:48 AM

All replies

  • Unfortunately there is no easy/automatic way to do this.  LINQ to Entities is focused on scenarios where the goal is custom objects not DataSets/DataTables.  Certainly you could take the code above, iterate over the created objects and copy data out into a DataTable but it would be ugly and inefficient.

     

    We have talked about the possibility of creating a DataAdapter that runs directly on top of EntityClient so that you could query your conceptual model with eSQL and then load results into a DataTable, but in the general case this requires either a DataSet that fully supports EDM constructs (including nesting, polymorphism, relationships, etc.) which is clearly not the case right now or some kind of mapping between EDM and some conventions in the DataSet which begins to make this whole thing look less valuable (map from relational database to a conceptual EDM model and then back to something relational for my in memory structure).

     

    For simple cases like you have above where you project into something that's just a flat list, this may seem a little funny, but unfortunately that's the way it is for the first release.

     

    - Danny

    Friday, September 07, 2007 4:27 PM
  • [Note: apparenlty I was writing this while Danny was writing his, so this doesn't take into account his explanation.]

     

    This may be a seriuosly overkill solution, but i had fun figuring it out!

     

    Just as an FYI, LINQ to Datasets does allow for conversions from queries to Datatables, but it doesn't seem to be able to deal with ObjectQueries. I then queried my objectquery to return an IQueryable and also an IEnumerable, but I couldn't get the datatable conversion to work. Look for CopyToDataTable in the VS2008 documentation for more info on that.

     

    So since I couldn't get at it I rolled my own using Mike Taulty's previous example (not for ObjectQueries though) as a start.

     

    This example uses the ObjectStateManager so I can dynamically get at the values. As long as I'm already using the ObjetStateManager, I also use it to get some metadata even though I could have used the MetadataWorkspace for that.

     

    Assuming we are starting with nwrows which is an ObjectQuery for a simple Linq to Entities query...

     

    Code Snippet

      Dim dt As New DataTable

     

      Dim currow As Integer = 0  'just a counter to keep track of my for each position


      For Each r In nwrows

      'get statemanager for current row
       Dim stateEntry = nwts.ObjectStateManager.GetObjectStateEntry(CType(r, Data.Objects.DataClasses.IEntityWithKey).EntityKey)

     

       'this will only happen once, build the dataColumns, I'm not bothering with their names


       If dt.Columns.Count = 0 Then
        For i = 0 To stateEntry.CurrentValues.FieldCount - 1
         dt.Columns.Add()
        Next
       End If

     

       'now we can add data, grabbing current values
       Dim dtrow As DataRow = dt.NewRow

       For idata = 0 To stateEntry.CurrentValues.FieldCount - 1
        dtrow.Item(idata) = stateEntry.CurrentValues(idata)
       Next

     

       dt.Rows.Add(dtrow)

     

       currow += 1


      Next

     

    This definitely works! Again, maybe overkill, but at least it's a good introduction to statemanager!

     

    Friday, September 07, 2007 4:46 PM
  • Hi all, I saw this thread earlier and just ran across a cool post... thought Id post a link. Uses IEnumerable<T> extension methods to copy T values into a datatable based on a schema of T. Looks like it will automagically add columns for sub-types of T as well.

     

    http://blogs.msdn.com/aconrad/archive/2007/09/07/science-project.aspx

     

     

     

     

     

    Saturday, September 08, 2007 6:48 AM
  • Great thanks to everyone. Now I see the way to solve my problems. Post of the Jarod was very helpful.

    Saturday, September 08, 2007 7:25 AM
  • Hi everyone,

     

    This is so confusing , bascially its running query twice, first through Linq and then through SqlCommand.

    I am using Linq but I want to convert the result in var,  to DataTable without looping and adding colums to new DataTable.

    I hope there must be some way to cast the linq result to DataTable or populate DataTable from var, otherwise Linq is just a hype.

     

    Pease help.

     

    thanks,

    Been

     

     

    Wednesday, August 06, 2008 4:41 PM
  • Sorry this is confusing.  The main point here is that using LINQ to query a database and load the results into a DataTable was explicitly not a goal of the Entity Framework (or other LINQ efforts such as LINQ to SQL).  So there are some work arounds you can use, but the product is not attempting to solve that scenario in a first-class, baked-in way.

     

    There are two main things we did set out to do with the products:

     

    1) You can use the Entity Framework to query a database and materialize the results into custom objects.  For many cases, putting the results into custom objects really meets all the needs that someone would otherwise address with the DataSet.  You do have to write code differently, so if you are used to using DataTables it is some adjustment, but you can do databinding, serialization, accessing properties and many other sorts of things with the custom objects and just not use a DataTable at all.

    2) You can query data from a database into a DataTable using the same mechanisms you have always used (not LINQ) and then use LINQ to DataSet in order to perform queries over the in-memory data in the DataTable.  This addresses some common requests we've had for better query options over the DataSet for some time.

     

    There are two main reasons why we didn't tackle the scenario of using LINQ to query from a database into a DataSet.  First off, for all of DataSet's strengths, there are a number of real limitations to it that can't be directly solved by further changes to the technology without a major rethink in strategy--that rethink leads you to using custom objects.  Secondly, LINQ was designed around the idea of queries being specified using an object model, leveraging intellisense over the object shapes, etc.  While it is possible to mesh this with DataSet somewhat (as in LINQ to DataSet), it's not as clean a match when bringing data from a database into the DataSet where the database data isn't present yet, and in the general case you don't even have the schema of the data to work with.

     

    - Danny

     

    Wednesday, August 06, 2008 6:57 PM
  • hi Danny,

     

    Thank you , for your detailed reply. I like using Linq. But, I am just wondering how you return data after geting result in var using Linq. We are used to work on n-tier application and dont write Data Base stuff in our aspx.cs pages. When we cannot return var object from Data Base layer, we cannot take advantage of data binding.  Actually,we are bound to bind data table to grid view and lists on axps pages. For example

     

    DataContext db = new DataContext();

    var news = from CN in db.CompanyNews

                     select CN;

     

    Now what , I am stuck here using Linq, I dont want to use loops to build the DataTable , thats not a good programming practice. That makes system slow. 

    I dont wanna use old approach using SQL Command that gives me DataSet and that full fill the purpose of DataBinding to controls in aspx pages. To my understanding , I cannot use Linq, to bind data table to controls, thats the requirement of most of the controls used on aspx pages.

     

    If I can use Linq, please explain with examples.

     

    thank you ,

    Been

     

    Wednesday, August 06, 2008 8:05 PM
  •  

    Hi Been,

    Given your example, you may want to check out the EntityDataSource.

     

    Jarod

    Wednesday, August 06, 2008 8:09 PM
  •  

    Jarod's right, definitley look first at the EntityDataSource to see if it will do what you want. It's not quite the same as just doing a LINQ query and then binding the results to the controls on your page, but if you want the user to modify data, the EntityDataSource will make your life the easiest. There are a bunch of blog posts and some screencasts about the EntityDataSource. Just google it. Oh wait, this is a microsoft site. I guess I"m supposed to say "just LIVE it".

     

    If you only want to display data, you can literally do a LINQ to Entities or ObjectQuery query and bind the results of that to the controls. If you go this route, be sure to bind the results, not the query itself or you will get an error.

     

     

    Wednesday, August 06, 2008 8:24 PM
  • Hi Julie and Jarod,

     

    Thanks for your response. EntityDataSource is not the solution when  data layer is saperate from presentation layer.

     

    As I mentioned earlier ,  I am using the presentation layer and data layer discretely. Please let me know, how I can return  data , from data layer to presentation layer using LINQ.

     

    Thank you ,

    Been

    Thursday, August 07, 2008 2:05 PM
  • Are you serializing the ds over a boundary?  Or is it just a data 'layer'?

     

    Given you want to have your data and presentation seperate (good),  I think you are posed with a choice here: do you use the familiarity of your databinding experience with datasets in the UI, or do you opt for the convenience of EF & linq in the data side? Converting entities to datasets doesnt make sense from my perspective. It will end up being more work than it is worth.

     

    If it were my choice, I would use EF & Linq. You should be able to return entity objectqueries from your data layer, and bind them to a datasource, as they are IEnumerable.

     

    So in your data layer something like this: (this is just an example DAL, youll want to manage your context, do your linq statements, dispose accordingly, etc)

     

    public IEnumerable<CompanyNews> GetNews()

    {

    return  new DataContext().CompanyNews;

    }

     

     

    YourDataGrid.DataSource = YourDataLayer.GetNews().ToList();

    YourDataGrid.DataBind();

     

     

     

     

    Thursday, August 07, 2008 5:59 PM
  • Since you are used to doing this with a dataset, you probably know that once you've bound the data to a control and have done a postback (clicking a SAVE button or something) all you have left is the values in the control, No dataset. No entities.

     

    As with a dataset, you can persist the entities into viewstate (and it's potentially expensive, just like a dataset) and keep them updated as you modify data in the control(s).

     

    But unlike a DataSet, these entities do not self-contain their state and you won't be able to simply send the entity back to the DAL and call SaveChanges.

     

    If you create a web page then instantiate your DAL, get your data then spit out the html, at that point the page and anything it owns will get disposed.

     

    So if you plan to take those entities and sent them back to your data layer, you will have a brand new ObjectContext. The entites themselves have no state and when you attach them to this new context, it will only know their current values and that they are UnChanged. You won't have any of the information that SaveChanges uses to dynamically build it's insert, update and delete commands.

     

    This is an entirely different set of problems and while I am not a big user of DataSources and I definitely don't want my data access to be tied to my UI, the EntityDataSource solves the problem. I am VERY torn by this becaue it means that we need to come up with our own soluitons for keeping track of state.

     

    We have to start looking at things like doing an extra query to get server values , then apply the values coming from the client and update - but then you don't get to take care of concurrency issues. Or you can persist the set of original entities in session state and use those to help you rebuild state but that could be VERY expensive in terms of resources. What if you have 1000 users accessing your site and trying to remember the original values of 10 or 20 or 100 or more entities each?

     

    ANother option is to use Astoria as  your data layer.

     

    I'm not trying to be all doom and gloom here, but I just want you to be aware that unfortunately, things aren't quite as easy with Entity Framework v1 when you start dealing with tiers and in ASP.NET, even though viewstate and session hide alot of the state issues, you are still going to encounter these problems.. They are doable but it's gonig to be a little more effort than just passing datatables around. This is why Microsoft keeps saying to use the EntityDataSource because they have solved the problem with that control except that - yeah it's in the UI.

     

    YOu might be totally willing to do the extra work, but if not, you may just want to stick with datasets,but then you are only doing the extra work in a different place - creating sql connections, commands etc.

     

    Julie

    Thursday, August 07, 2008 6:31 PM
  •  

    I think it will be helpful .If any problem in code must reply, it must run but efficiency is a little decreased.


    IDbCommand
    cmd = DataContext.GetCommand(query as IQueryable);

     

    SqlDataAdapter adapter = new SqlDataAdapter();

    adapter.SelectCommand = (

    SqlCommand)cmd;

     

    DataTable dt = new DataTable("sd");

    adapter.FillSchema(dt,

    SchemaType.Source);

    adapter.Fill(dt);



    SAJID KHAN
    Software Develper
    Moftak Solution

    Monday, August 24, 2009 12:53 PM
  • Try this link it is exactly what you need.

    http://www.codeproject.com/Tips/171006/Convert-LINQ-to-Entity-Result-to-a-DataTable.aspx

    Tuesday, March 22, 2011 2:13 PM