LINQ query on a DataTable

Question

I m trying to perform a LINQ query on a DataTable object and bizarrely I am finding that performing such queries on DataTables is not straightforward  For example   var results   from myRow in myDataTable where results Field  RowNo      1 select results    This is not allowed  How do I get something like this working   I m amazed that LINQ queries are not allowed on DataTables

User · Answer

I realize this has been answered a few times over, but just to offer another approach:

I like to use the .Cast<T>() method, it helps me maintain sanity in seeing the explicit type defined and deep down I think .AsEnumerable() calls it anyways:

var results = from myRow in myDataTable.Rows.Cast<DataRow>() 
                  where myRow.Field<int>("RowNo") == 1 select myRow;

or

var results = myDataTable.Rows.Cast<DataRow>()
                      .FirstOrDefault(x => x.Field<int>("RowNo") == 1);

As noted in comments, no other assemblies needed as it's part of Linq (Reference)

User · Answer

In my application I found that using LINQ to Datasets with the AsEnumerable   extension for DataTable as suggested in the answer was extremely slow   If you re interested in optimizing for speed  use James Newtonking s Json Net library  http   james newtonking com json help index html      Serialize the DataTable to a json string string serializedTable   JsonConvert SerializeObject myDataTable       Jarray dataRows   Jarray Parse serializedTable       Run the LINQ query List lt JToken gt  results    from row in dataRows                     where  int  row  ans key      42                     select row  ToList        If you need the results to be in a DataTable string jsonResults   JsonConvert SerializeObject results   DataTable resultsTable   JsonConvert DeserializeObject lt DataTable gt  jsonResults

User · Answer

Most likely  the classes for the DataSet  DataTable and DataRow are already defined in the solution  If that s the case you won t need the DataSetExtensions reference   Ex  DataSet class name-  CustomSet  DataRow class name-  CustomTableRow  with defined columns  RowNo        var result   from myRow in myDataTable Rows OfType lt CustomSet CustomTableRow gt                 where myRow RowNo    1              select myRow    Or  as I prefer   var result   myDataTable Rows OfType lt CustomSet CustomTableRow gt    Where myRow   gt  myRow RowNo

User · Answer

you can try this  but you must be sure the type of values for each Column  List lt MyClass gt  result   myDataTable AsEnumerable   Select x  gt  new MyClass         Property1    string x Field lt string gt   ColumnName1         Property2    int x Field lt int gt   ColumnName2         Property3    bool x Field lt bool gt   ColumnName3

User · Answer

var query   from p in dt AsEnumerable                       where p Field lt string gt   code      this txtCat Text                     select new                                               name   p Field lt string gt   name                            age  p Field lt int gt   age                                                     the name and age fields are now part of the query object and can be accessed like so  Console WriteLine query name

User · Answer

It s not that they were deliberately not allowed on DataTables  it s just that DataTables pre-date the IQueryable and generic IEnumerable constructs on which Linq queries can be performed    Both interfaces require some sort type-safety validation  DataTables are not strongly typed  This is the same reason why people can t query against an ArrayList  for example   For Linq to work you need to map your results against type-safe objects and query against that instead

User · Answer

Try this  var row    from result in dt AsEnumerable   OrderBy  result   gt  Guid NewGuid    select result  Take 3

User · Answer

You can use LINQ to objects on the Rows collection  like so   var results   from myRow in myDataTable Rows where myRow Field  RowNo      1 select myRow

User · Answer

var results   from DataRow myRow in myDataTable Rows     where  int myRow  RowNo      1     select myRow

User · Answer

Try this      SqlCommand cmd   new SqlCommand   Select   from Employee  con   SqlDataReader dr   cmd ExecuteReader     DataTable dt   new DataTable   Employee     dt Load  dr    var Data   dt AsEnumerable     var names   from emp in Data select emp Field lt String gt   dt Columns 1     foreach  var name in names         Console WriteLine  name

User · Answer

I propose following solution   DataView view   new DataView myDataTable    view RowFilter    RowNo   1   DataTable results   view ToTable true     Looking at the DataView Documentation  the first thing we can see is this      Represents a databindable  customized view of a DataTable for sorting  filtering  searching  editing  and navigation    What I am getting from this is that DataTable is meant to only store data and DataView is there enable us to  query  against the DataTable    Here is how this works in this particular case   You try to implement the SQL Statement  SELECT   FROM myDataTable WHERE RowNo   1   in  DataTable language   In C  we would read it like this   FROM myDataTable WHERE RowNo   1 SELECT     which looks in C  like this   DataView view   new DataView myDataTable      FROM myDataTable view RowFilter    RowNo   1      WHERE RowNo   1 DataTable results   view ToTable true      SELECT

User · Answer

var results   from myRow in myDataTable where results Field lt Int32 gt   RowNo      1 select results

User · Answer

Create DataTable  DataTable dt  new DataTable    dt Columns AddRange new DataColumn        new DataColumn  ID  typeof System Int32       new DataColumn  Name  typeof System String           Fill with data  dt Rows Add new Object   1  Test1     dt Rows Add new Object   2  Test2        Now  Query DataTable with linq   To work with linq it should required our source implement IEnumerable interface    But DataTable not Implement IEnumerable interface   So we call DataTable Extension method  i e AsEnumerable   this will return EnumerableRowCollection lt DataRow gt       Now Query DataTable to find Row whoes ID 1  DataRow drow   dt AsEnumerable   Where p  gt p Field lt Int32 gt  0   1  FirstOrDefault

User · Answer

As  ch00k said   using System Data    needed for the extension methods to work       var results        from myRow in myDataTable Rows      where myRow Field lt int gt   RowNo      1      select myRow    select the thing you want  not the collection   You also need to add a project reference to System Data DataSetExtensions

User · Answer

You can t query against the DataTable s Rows collection  since DataRowCollection doesn t implement IEnumerable lt T gt   You need to use the AsEnumerable   extension for DataTable  Like so   var results   from myRow in myDataTable AsEnumerable   where myRow Field lt int gt   RowNo      1 select myRow    And as  Keith says  you ll need to add a reference to System Data DataSetExtensions  AsEnumerable   returns IEnumerable lt DataRow gt   If you need to convert IEnumerable lt DataRow gt  to a DataTable  use the CopyToDataTable   extension   Below is query with Lambda Expression   var result   myDataTable      AsEnumerable        Where myRow   gt  myRow Field lt int gt   RowNo      1

User · Answer

This is a simple way that works for me and uses lambda expressions   var results   myDataTable Select     FirstOrDefault x   gt   int x  RowNo      1    Then if you want a particular value   if results    null       var foo   results  ColName   ToString

User · Answer

Json Formating code                       DT is DataTable                     var filter    from r1 in DT AsEnumerable                                        Grouping by multiple columns                                    group r1 by new                                                                           EMPID   r1 Field lt string gt   EMPID                                          EMPNAME   r1 Field lt string gt   EMPNAME                                         into g                                     Selecting as new type                                   select new                                                                            EMPID   g Key EMPID                                        MiddleName   g Key EMPNAME

User · Answer

Try this simple line of query   var result myDataTable AsEnumerable   Where myRow   gt  myRow Field lt int gt   RowNo      1

User · Answer

Example on how to achieve this provided below    DataSet dataSet   new DataSet      Create a dataset dataSet    DataEntryDataLayer ReadResults      Call to the dataLayer to return the data    LINQ query on a DataTable var dataList   dataSet Tables  DataTable                  AsEnumerable                  Select i   gt  new                                  ID   i  ID                     Name   i  Name                     ToList

User · Answer

Using LINQ to manipulate data in DataSet DataTable  var results   from myRow in tblCurrentStock AsEnumerable                 where myRow Field lt string gt   item name   ToUpper   StartsWith tbSearchItem Text ToUpper                  select myRow  DataView view   results AsDataView

User · Answer

For VB NET The code will look like this   Dim results   From myRow In myDataTable   Where myRow Field Of Int32   RowNo     1 Select myRow

User · Answer

IEnumerable lt string gt  result   from myRow in dataTableResult AsEnumerable                                select myRow  server   ToString

User · Answer

You can get it work elegant via linq like this   from prod in TenMostExpensiveProducts   Tables 0  AsEnumerable   where prod Field lt decimal gt   UnitPrice    gt  62 500M select prod   Or like dynamic linq this  AsDynamic is called directly on DataSet    TenMostExpensiveProducts   AsDynamic   Where  x   gt  x UnitPrice  gt  62 500M    I prefer the last approach while is is the most flexible  P S   Don t forget to connect System Data DataSetExtensions dll reference

[c#] LINQ query on a DataTable

Examples related to c#

Examples related to .net

Examples related to linq

Examples related to datatable

Examples related to .net-3.5