Home > Forums > .NET Framework >


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

Posted by Sharpcnet under .NET Framework on 10/29/2013 | Points: 10 | Views : 14023 | Status : [Member] | Replies : 10
New to linq and trying to acheive
select * from tblCountries order by CountryName
I followed the procedre in http://msdn.microsoft.com/en-us/library/bb669096%28v=vs.110%29.aspx , and can now access the CopyToDataTable method.

But now there is a syntax error at the orderby part-

cannot convert IOrderedQueryable<Context.tblCountry> to Ienumerable<system.data.datarow>

public DataTable GetAllRecords(bool bDeleted)

{
IEnumerable<DataRow> query = (from p in MedianDB.tblCountries
select p).OrderBy(p => p.CountryName);//error here
DataTable dt = query.CopyToDataTable<DataRow>();
if (dt != null)
return dt;
else
return dt;
}


I'm using C# and .net framework 4.0.Thank you.




Responses

Posted by: Bandi on: 10/29/2013 [Member] [MVP] Diamond | Points: 25
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
Posted by: Sharpcnet on: 10/29/2013 [Member] Starter | Points: 25
@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
Posted by: Bandi on: 10/29/2013 [Member] [MVP] Diamond | Points: 25
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
Posted by: Sharpcnet on: 10/29/2013 [Member] Starter | Points: 25
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
Posted by: Bandi on: 10/29/2013 [Member] [MVP] Diamond | Points: 50

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
Posted by: Sharpcnet on: 10/29/2013 [Member] Starter | Points: 25
@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
Posted by: Bandi on: 10/29/2013 [Member] [MVP] Diamond | Points: 25
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
Posted by: Sharpcnet on: 10/29/2013 [Member] Starter | Points: 25
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
Posted by: Bandi on: 10/29/2013 [Member] [MVP] Diamond | Points: 50

Resolved
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
Posted by: vishalneeraj-24503 on: 11/5/2013 [Member] [MVP] Platinum | Points: 25
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
 

  Have you tried Aspose: http://aspose.com/file-tools

Login to post response