Can I convert linq query result to a dataset?

Question

Hello,

I am doing a asp.net webservice project and want to use linq to invoke the stored procedure in the database. Also, I want the return value to be a DataSet. Can I convert the linq query result to a DataSet object?

Thanks.

Answer 1

Yes you can. See this solution.

Answer 2

But a problem occurred,

My code is like this:

public DataSet test()
    {
        ProDataContext pdc = new ProDataContext(proConnectionString);
        var temp = from p in pdc.Courses
                   select p;
        DataSet d = new DataSet();
        d.Tables.Add(temp.ToDataTable());
     

        return d;

       
       
    }

(I changed the method name to ToDataTable)

 

The exception message:

System.InvalidOperationException: There was an error generating the XML document. ---> System.InvalidOperationException: There was an error reflecting type 'System.Data.Linq.EntitySet`1[Lesson]'. ---> System.InvalidOperationException: There was an error reflecting type 'Lesson'. ---> System.InvalidOperationException: There was an error reflecting property 'Course'. ---> System.InvalidOperationException: There was an error reflecting type 'Course'. ---> System.InvalidOperationException: Cannot serialize member 'Course.TeacherPic' of type 'System.Data.Linq.Binary', see inner exception for more details. ---> System.InvalidOperationException: System.Data.Linq.Binary cannot be serialized because it does not have a parameterless constructor.

 

Why?

Answer 3

See this solution..
it is really cool..
http://www.entityspaces.net/blog/2008/05/24/EntitySpaces+2008+LINQ+To+DataTable.aspx

Answer 4

Also, so no one accuses me of not contributing an easy solution.  You can easily convert your LINQ query to an sql query and then return the results as a datatable...

        [WebMethod]

        public DataTable testQuery2()

        {

            OrderWorkflowDataContext db = new OrderWorkflowDataContext();

            var r = from u in db.tbl_Users

                    select u;

            SqlCommand sqlQuery = db.GetCommand(r) as SqlCommand;

            DataTable dt = new DataTable();

            SqlDataAdapter ad = new SqlDataAdapter(sqlQuery);

            ad.Fill(dt);

            dt.TableName = "Users";

            return dt;

        }

Answer 5

Another method to handle this is to use this method which works very well and is a little more what we are looking for...

//From http://www.c-sharpcorner.com/uploadfile/VIMAL.LAKHERA/convert-a-linq-query-resultset-to-a-datatable/

public static 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;

}