CopyToDataTable does not show up as a method in linq [Resolved]

Hi,

I have done the following sample code for you to do sorting on a result set and return a DataTable

private DataTable GetDataByOrder()
        {
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("JOB_ID");
            dataTable.Columns.Add("JOB_TITLE");
            dataTable.Columns.Add("MIN_SALARY");
            dataTable.Columns.Add("MAX_SALARY");
            DataRow row = null;

            studyEntities1 sdContext = new studyEntities1();  // access DBContext object for your database
            var query = (from j in sdContext.JOBS.AsEnumerable() orderby j.MIN_SALARY ascending select j);

            foreach (var rowObj in query)
            {
                row = dataTable.NewRow();
                dataTable.Rows.Add(rowObj.JOB_ID, rowObj.JOB_TITLE, rowObj.MIN_SALARY, rowObj.MAX_SALARY);
            }

            return dataTable;
        }
 

NOTE: I have a table called JOBS with columns JOB_ID, JOB_TITLE, MIN_SALARY and MAX_SALARY....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

http://documentation.devexpress.com/#CodeRush/CustomDocument10231
http://social.msdn.microsoft.com/Forums/en-US/cd8e7c16-ff72-4754-aab2-cea98a786b7b/extension-methods-must-be-defined-in-a-top-level-static-class?forum=sharepointdevelopmentlegacy

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

CopyToDataTable() only works when your query returns an IEnumerable<'DataRow>. In your query, you are returning an anonymous type. Anonymous types don't carry the extension method for CopyToDataTable(). You could just select the entire row like this, assuming Result is a DataTable. Then create your anonymous type.

Refer the same in the following link
http://stackoverflow.com/questions/12437207/not-having-copytodatatable-method
http://stackoverflow.com/questions/1595350/why-am-i-not-getting-copytodatatable-in-linq-query

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

@Chandu, Thank you.
But I visited the links already and being a novice, I find them very complicated to follow. Hence, looking for simpler ways. What about the second method that I tried. Why does it return null rows.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Refer this link for adding System.Data.DateSetExtensions
http://www.codeproject.com/Questions/265964/Unable-to-Using-system-data-datasetextensions
http://msdn.microsoft.com/en-us/library/bb552414.aspx

and then try first code.....
Let me know further updates...

EDIT:
http://www.dzone.com/snippets/linq-query-get-records-and

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

I do have the data and datasetextension references already. I have updated my question with a new finding

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

@Chandu, Thank you.That worked. But if there are numerous columns, then we have to name every column in code. Could you please try fixing the error in my question.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

May be try this

IEnumerable<DataRow> query = ((from p in MedianDB.tblCountries

                              select p).OrderBy(p => p.CountryName)) as IEnumerable<DataRow>;

EDIT:
http://stackoverflow.com/questions/41244/dynamic-linq-orderby-on-ienumerablet

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Thank you, I no more have that error. But I guess, I messed up with creating the Extension class as shown in the msdn link.
What I Did - Created a new class 'Extensions' and then pasted both the classes in that link into it - 'CustomLINQtoDataSetMethods', ObjectShredder<T>.
It shows an error for CopyToDataTable<T> which is inside 'CustomLINQtoDataSetMethods'.
The error says - "Extension method must be defined in a top level static class;CustomLINQtoDataSetMethodsis a nested class".
Should I create individual classes for both of those? I really dont want to put them in separate classes.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

You can use Datatable Select method as

Private DataTable get_Data(string ID,DataTable dt)
{
DataTable new_dt = new DataTable();

if(dt!=null)
{
//dt.Select method will return you array of datarows

Datarow dr[] = dt.Select("Id = '"& ID &"') //here Id is your column name

//create new datatable as

if(dr!=null && dr.Length>0)
{

new_dt = dr.CopyToDatatable();
}
}
return new_dt;
}

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator