locked
Pumping the result of a LINQ to Entity query into a DataSet RRS feed

  • 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() ). This is also not ideal because it entails using LINQ to SQL which is not typed, and having to convert the tables one at a time while disregarding their relationships.

    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. Has anyone had any success in this area?

    Thx!

    Thursday, May 19, 2011 6:09 PM

Answers

  • Hello David,

    Thanks for your post.

    According to your description, You would like to convert the result of linq to entity query into dataset. I found some article about this:

    This article helps converting LINQ to Entity result to a data table. This can be very useful if you are moving to entity framework and still want to support existing methods, write a normal Linq to entity syntax, then with a simple extension method convert it to a data table. Note that the Query is executed once on the server and there is no need to loop the result and create the data table which takes too much time.

    There is also a CopyToDataTable : http://msdn.microsoft.com/en-us/library/bb386921.aspx 
    And for the record here is a C# version:

    protected DataTable EntityToDatatable(IQueryable Result, ObjectContext ctx)
        {
          try
          {
            EntityConnection conn = ctx.Connection as EntityConnection;
            using (SqlConnection SQLCon = new SqlConnection(conn.StoreConnection.ConnectionString))
            {
              ObjectQuery query = Result as ObjectQuery;
              using (SqlCommand Cmd = new SqlCommand(query.ToTraceString(), SQLCon))
              {
                foreach (var param in query.Parameters)
                {
                  Cmd.Parameters.AddWithValue(param.Name, param.Value);
                }
                using (SqlDataAdapter da = new SqlDataAdapter(Cmd))
                {
                  using (DataTable dt = new DataTable())
                  {
                    da.Fill(dt);
                    return dt;
                  }
                }
              }
            }
          }
          catch (Exception)
          {
            throw;
          }
        }
    More information, please read this.
    I hope it can help you.
    Have a nice day,

    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jackie-Sun Tuesday, May 31, 2011 3:25 AM
    Monday, May 23, 2011 5:25 AM