CODEWITHSUNDEEP
c#.netlinqdatatable.net-3.5
Calanus
Calanus

Reputation: 26317

LINQ query on a DataTable

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!

Upvotes: 1152

Views: 1161321

Answers (22)

Collin K
Collin K

Reputation: 15478

You can't query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. You need to use the AsEnumerable() extension for DataTable. Like so:

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

And as @Keith says, you'll need to add a reference to System.Data.DataSetExtensions

AsEnumerable() returns IEnumerable<DataRow>. If you need to convert IEnumerable<DataRow> to a DataTable, use the CopyToDataTable() extension.

Below is query with Lambda Expression,

var result = myDataTable
    .AsEnumerable()
    .Where(myRow => myRow.Field<int>("RowNo") == 1);

Upvotes: 1419

vandsh
vandsh

Reputation: 1532

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, does not require System.Data.DataSetExtensions or any other assemblies (Reference)

Upvotes: 47

sushil pandey
sushil pandey

Reputation: 762

//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<DataRow>


// Now Query DataTable to find Row whoes ID=1

DataRow drow = dt.AsEnumerable().Where(p=>p.Field<Int32>(0)==1).FirstOrDefault();
 //

Upvotes: 30

Ravi
Ravi

Reputation: 449

var query = from p in dt.AsEnumerable()
                    where p.Field<string>("code") == this.txtCat.Text
                    select new
                    {
                        name = p.Field<string>("name"),
                        age= p.Field<int>("age")                         
                    };

the name and age fields are now part of the query object and can be accessed like so: Console.WriteLine(query.name);

Upvotes: 41

Alan
Alan

Reputation: 976

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 *

Upvotes: 0

Gabriel Martinez Bustos
Gabriel Martinez Bustos

Reputation: 511

you can try this, but you must be sure the type of values for each Column

List<MyClass> result = myDataTable.AsEnumerable().Select(x=> new MyClass(){
     Property1 = (string)x.Field<string>("ColumnName1"),
     Property2 = (int)x.Field<int>("ColumnName2"),
     Property3 = (bool)x.Field<bool>("ColumnName3"),    
});

Upvotes: 5

xadriel
xadriel

Reputation: 191

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<CustomSet.CustomTableRow>()
             where myRow.RowNo == 1
             select myRow;

Or (as I prefer)

var result = myDataTable.Rows.OfType<CustomSet.CustomTableRow>().Where(myRow => myRow.RowNo);

Upvotes: 11

Mohit Verma
Mohit Verma

Reputation: 5294

Try this simple line of query:

var result=myDataTable.AsEnumerable().Where(myRow => myRow.Field<int>("RowNo") == 1);

Upvotes: 25

Ryan Gavin
Ryan Gavin

Reputation: 729

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 => new
              {
                 ID = i["ID"],
                 Name = i["Name"]
               }).ToList();

Upvotes: 8

Uthaiah
Uthaiah

Reputation: 1303

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<String>( dt.Columns[1] );
foreach( var name in names )
{
    Console.WriteLine( name );
}

Upvotes: 6

Iman
Iman

Reputation: 18956

IEnumerable<string> result = from myRow in dataTableResult.AsEnumerable()
                             select myRow["server"].ToString() ;

Upvotes: 7

Matt Kemp
Matt Kemp

Reputation: 2920

This is a simple way that works for me and uses lambda expressions:

var results = myDataTable.Select("").FirstOrDefault(x => (int)x["RowNo"] == 1)

Then if you want a particular value:

if(results != null) 
    var foo = results["ColName"].ToString()

Upvotes: 14

LandedGently
LandedGently

Reputation: 703

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<JToken> 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<DataTable>(jsonResults);

Upvotes: 8

Salim
Salim

Reputation: 349

Using LINQ to manipulate data in DataSet/DataTable

var results = from myRow in tblCurrentStock.AsEnumerable()
              where myRow.Field<string>("item_name").ToUpper().StartsWith(tbSearchItem.Text.ToUpper())
              select myRow;
DataView view = results.AsDataView();

Upvotes: 34

Abdul Saboor
Abdul Saboor

Reputation: 4127

For VB.NET The code will look like this:

Dim results = From myRow In myDataTable  
Where myRow.Field(Of Int32)("RowNo") = 1 Select myRow

Upvotes: 7

Vinay
Vinay

Reputation: 179

var results = from myRow in myDataTable
where results.Field<Int32>("RowNo") == 1
select results;

Upvotes: 9

AuthorProxy
AuthorProxy

Reputation: 8047

You can get it work elegant via linq like this:

from prod in TenMostExpensiveProducts().Tables[0].AsEnumerable()
where prod.Field<decimal>("UnitPrice") > 62.500M
select prod

Or like dynamic linq this (AsDynamic is called directly on DataSet):

TenMostExpensiveProducts().AsDynamic().Where (x => x.UnitPrice > 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

Upvotes: 5

midhun sankar
midhun sankar

Reputation: 119

Try this

var row = (from result in dt.AsEnumerable().OrderBy( result => Guid.NewGuid()) select result).Take(3) ;

Upvotes: 11

JoelFan
JoelFan

Reputation: 38714

var results = from DataRow myRow in myDataTable.Rows
    where (int)myRow["RowNo"] == 1
    select myRow

Upvotes: 142

Keith
Keith

Reputation: 155822

As @ch00k said:

using System.Data; //needed for the extension methods to work

...

var results = 
    from myRow in myDataTable.Rows 
    where myRow.Field<int>("RowNo") == 1 
    select myRow; //select the thing you want, not the collection

You also need to add a project reference to System.Data.DataSetExtensions

Upvotes: 54

David Wengier
David Wengier

Reputation: 10179

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;

Upvotes: 17

Jon Limjap
Jon Limjap

Reputation: 95482

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.

Upvotes: 75

Related Questions