By Calanus


2008-08-14 10:08:27 8 Comments

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!

23 comments

@Ravi 2010-05-23 04:03:29

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);

@user1372430 2014-09-30 22:54:44

How I use name? For example, MessageBox.Show(name) is undefined.

@vandsh 2016-02-02 21:22:20

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)

@user423430 2017-12-19 15:09:13

This works without referencing System.Data.DataSetExtensions.

@Alan 2019-08-19 14:18:58

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 *

@chandra rv 2019-08-19 09:31:01

                ICommonExtService commonExtService = ProxyHelper.GetCommonExtServiceProxy();

                filledJSON = commonExtService.GetCandidateData(candidateCode.Trim());
                JObject CanidateDataObj = new JObject();
                if (string.IsNullOrEmpty(filledJSON) == false)
                {
                    DataTable dt = new DataTable();

                    dt = JsonConvert.DeserializeObject<DataTable>(filledJSON);

                    DataTable DtC = dt.Clone();

                    foreach (DataColumn column in DtC.Columns)
                    {
                        column.DataType = typeof(string);
                    }
                    foreach (DataRow row in dt.Rows)
                    {
                        DtC.ImportRow(row);
                    }

                    try
                    {
                        if (DtC != null && DtC.Columns.Count > 0 && DtC.Rows.Count > 0)
                        {
                            //Json Formating code
                            var filter = (from r1 in DtC.AsEnumerable()
                                          group r1 by new
                                          {
                                              CandidateCode = r1.Field<string>("CandidateCode"),
                                              FirstName = r1.Field<string>("FirstName"),
                                              MiddleName = r1.Field<string>("MiddleName"),

                                              LastName = r1.Field<string>("LastName"),
                                              FullName = r1.Field<string>("FullName"),

                                              DistributionCode = r1.Field<string>("DistributionCode"),
                                              GoCode = r1.Field<string>("GoCode"),
                                              SSN = r1.Field<string>("SSN")

                                          } into g
                                          select new
                                          {

                                              FirstName = g.Key.FirstName,
                                              MiddleName = g.Key.MiddleName,
                                              LastName = g.Key.LastName,
                                              FullName = g.Key.FullName,
                                              CandidateCode = g.Key.CandidateCode,
                                              DistributionCode = g.Key.DistributionCode,
                                              GoCode = g.Key.GoCode,
                                              SSN = g.Key.SSN,

                                              AddressList = from a1 in g.ToList().
                                                          Where(e1 => (e1.Field<string>("AddressLine1") == ""
                                                          && e1.Field<string>("State") == ""
                                                          && e1.Field<string>("City") == ""
                                                          && e1.Field<string>("ZipCode") == ""
                                                          && e1.Field<string>("County") == ""
                                                          && e1.Field<string>("Country") == "") == false
                                                          )
                                                            group a1 by new
                                                            {
                                                                AddressLine1 = a1.Field<string>("AddressLine1"),
                                                                State = a1.Field<string>("State"),
                                                                City = a1.Field<string>("City"),
                                                                ZipCode = a1.Field<string>("ZipCode"),
                                                                County = a1.Field<string>("County"),
                                                                Country = a1.Field<string>("Country")
                                                            } into f
                                                            select new
                                                            {
                                                                AddressLine1 = f.Key.AddressLine1,
                                                                State = f.Key.State,
                                                                City = f.Key.City,
                                                                ZipCode = f.Key.ZipCode,
                                                                County = f.Key.County,
                                                                Country = f.Key.Country
                                                            },

                                              EmploymentHistoryList = from e1 in g.ToList().
                                                          Where(e1 => (e1.Field<string>("EmployerName") == ""
                                                          && e1.Field<string>("JobTitle") == ""
                                                          && e1.Field<string>("City") == ""
                                                          && e1.Field<string>("State") == ""
                                                          && e1.Field<string>("County") == ""
                                                          && e1.Field<string>("ZipCode") == ""
                                                          && e1.Field<string>("ManagerFirstName") == ""
                                                          && e1.Field<string>("ManagerLastName") == "") == false
                                                          )
                                                                      group e1 by new
                                                                      {
                                                                          EmployerName = e1.Field<string>("EmployerName"),
                                                                          JobTitle = e1.Field<string>("JobTitle"),
                                                                          City = e1.Field<string>("City"),
                                                                          State = e1.Field<string>("State"),
                                                                          County = e1.Field<string>("County"),
                                                                          ZipCode = e1.Field<string>("ZipCode"),
                                                                          ManagerFirstName = e1.Field<string>("ManagerFirstName"),
                                                                          ManagerLastName = e1.Field<string>("ManagerLastName")
                                                                      } into h
                                                                      select new
                                                                      {
                                                                          EmployerName = h.Key.EmployerName,
                                                                          JobTitle = h.Key.JobTitle,
                                                                          City = h.Key.City,
                                                                          State = h.Key.State,
                                                                          County = h.Key.County,
                                                                          ZipCode = h.Key.ZipCode,
                                                                          ManagerFirstName = h.Key.ManagerFirstName,
                                                                          ManagerLastName = h.Key.ManagerLastName
                                                                      }

                                          });

                            if (filter != null)
                            {
                                JArray jr = JArray.FromObject(filter);
                                CanidateDataObj = (JObject)jr[0];
                            }
                            if (_responsemsg.Content == null && CanidateDataObj.HasValues == true)
                            {

                                ResponseData = new JObject();
                                ResponseData.Add("ResponseCode", ResponseCode.Success.ToString());
                                ResponseData.Add("ResponseMessage", "Data present");
                                ResponseData.Add("Data", CanidateDataObj);
                                _responsemsg.StatusCode = System.Net.HttpStatusCode.OK;
                                _responsemsg.Content = new ObjectContent<JObject>(ResponseData, formatter, "application/json");
                                return _responsemsg;
                            }
                        }
                        else
                        {
                            ResponseData = new JObject();
                            ResponseData.Add("responseCode", ResponseCode.Failure.ToString());
                            ResponseData.Add("responseMessage", "No candidate found with given Code");
                            _responsemsg.StatusCode = System.Net.HttpStatusCode.NotFound;
                            _responsemsg.Content = new ObjectContent<JObject>(ResponseData, formatter, "application/json");
                            return _responsemsg;
                        }
                    }
                    catch (Exception Ex)
                    {
                        ResponseData = new JObject();
                        ResponseData.Add("responseCode", ResponseCode.Failure.ToString());
                        ResponseData.Add("responseMessage", Ex.Message.ToString());
                        _responsemsg.StatusCode = System.Net.HttpStatusCode.InternalServerError;
                        _responsemsg.Content = new ObjectContent<JObject>(ResponseData, formatter, "application/json");
                        return _responsemsg;
                    }

                }
                else if(string.IsNullOrEmpty(candidateCode)==false)
                {
                    ResponseData = new JObject();
                    ResponseData.Add("responseCode", ResponseCode.Failure.ToString());
                    ResponseData.Add("responseMessage", "No candidate found with given Code");
                    _responsemsg.StatusCode = System.Net.HttpStatusCode.NotFound;
                    _responsemsg.Content = new ObjectContent<JObject>(ResponseData, formatter, "application/json");
                    return _responsemsg;

                }
                else
                {
                    ResponseData = new JObject();
                    ResponseData.Add("responseCode", ResponseCode.Failure.ToString());
                    ResponseData.Add("responseMessage", "Please provide valid candidate code");
                    _responsemsg.StatusCode = System.Net.HttpStatusCode.BadRequest;
                    _responsemsg.Content = new ObjectContent<JObject>(ResponseData, formatter, "application/json");
                    return _responsemsg;
                }

@Collin K 2008-08-14 19:45:01

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);

@Jeff 2009-07-29 20:46:28

VB Version: Dim results = From myRow In myDataTable.AsEnumerable _ Where myRow.Field("RowNo") = 1 _ Select myRow

@Luke Duddridge 2011-05-31 10:37:17

I already had a reference to the dll mentioned, but was missing using System.Data;

@Cannon 2011-08-11 17:50:56

How do I get DataTable back from var results ?

@yougotiger 2012-06-18 22:26:27

VB Version needs to insert (Of String) between myRow.Field and ("RowNo"). That part should read: myRow.Field(Of String)("RowNo") = 1 - Reference @Cros comment.

@The Conspiracy 2014-06-25 18:46:35

this solution is needlessly complicated. Use myDataTable.Rows instead as @JoelFan suggested.

@Collin K 2014-06-26 16:29:21

@Markus Just to clarify, the reason that @JoelFan's solution works with myDataTable.Rows is because the myRow variable is explicitly cast to DataRow. When it is compiled, that query is rewritten to myDataTable.Rows.Cast<DataRow>().Where(myRow => (int)myRow["RowNo"] == 1). Personally, I don't find the call to AsEnumerable() any more complicated than the call to Cast<DataRow>(). As far as I know, the performance is the same, so it's just a matter of preference.

@bonCodigo 2014-07-12 00:15:31

...If you need to convert IEnumerable<DataRow> to aDataTable, use the CopyToDataTable() extension. this I have ben searching for ;)

@Gabriel Martinez Bustos 2018-02-01 21:43:07

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"),    
});

@Programnik 2018-09-23 23:17:42

Has the world gone mad? Whats wrong with sql? DataRow[] drs = dt.Select("id=1"); Maybe this is too easy.

@xadriel 2013-04-24 17:17:47

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);

@Mohit Verma 2016-04-05 09:38:59

Try this simple line of query:

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

@MikeTeeVee 2016-08-10 07:44:29

I prefer the "Method Chaining" (as you have done here) over the "Query Syntax" (in the accepted answer) simply because this is a basic where-clause that fits on one line and is still very readable. To each their own.

@Ryan Gavin 2017-10-25 16:04:52

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();

@Uthaiah 2014-04-10 10:24:31

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 );
}

@sushil pandey 2012-01-05 08:43:15

//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();
 // 

@Iman Abidi 2015-08-04 07:32:55

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

@Matt Kemp 2015-03-18 22:13:55

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()

@LandedGently 2014-10-14 17:51:10

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);

@an phu 2015-08-07 22:09:42

I doubt this is faster, in the general cases. It has the overhead of two serialization, one deserialization and one parsing operations. Regardless, I downvoted because it is not concise, i.e. the serialization/deserialization doesn't make clear that the intent is to filter a list.

@LandedGently 2015-08-10 19:42:22

@an phu, using the .AsEnumerable extension method creates a collection of heavyweight System.Data.DataRow objects. The serialized and parsed data table creates lightweight data consisting only of the column names and values of each row. When the query runs, it will load the data into memory, which for a large dataset may involve swapping. Sometimes, the overhead of several operations is less than the overhead of copying large amounts of data in and out of memory.

@Salim 2011-07-13 11:21:07

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();

@Naomi 2013-05-09 19:27:30

The AsDataView doesn't appear in Intellisense for me. I included using System.Data.Linq and using System.Linq but still it's not working. Do you know what am I missing? Thanks in advance.

@Louis 2014-02-19 16:51:07

@Naomi It comes from System.Data.DataSetExtensions.

@Abdul Saboor 2012-10-17 16:04:52

For VB.NET The code will look like this:

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

@Vinay 2014-02-01 11:51:43

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

@Mr Anderson 2016-08-15 18:05:19

This answer as a lot of issues with it.

@AuthorProxy 2013-11-03 17:54:15

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

@midhun sankar 2012-05-18 07:15:08

Try this

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

@JoelFan 2009-03-05 02:53:10

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

@Adjit 2016-04-29 15:35:26

What about for selecting multiple rows, instead of just row 1?

@JoelFan 2016-05-01 02:48:59

Just remove the "where" line and you will get all the rows

@Jonas 2017-08-25 14:35:46

Yes, this is how I use to do it, except for replacing (int)myRow["RowNo"] with the generic form myRow.Field<int>("RowNo") to more conveniently support nullable types.

@Keith 2008-08-14 11:07:52

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

@David Wengier 2008-08-14 10:11:08

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;

@onedaywhen 2016-07-11 08:45:02

Because DataTable.Rows does not implement IEnumerable, I can't see how this query could compile.

@BVernon 2018-01-26 22:42:56

@onedaywhen I just saw this being done in some code and it does compile. Trying to figure out why right now.

@Jon Limjap 2008-08-14 10:10:55

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.

Related Questions

Sponsored Content

26 Answered Questions

[SOLVED] Why not inherit from List<T>?

12 Answered Questions

[SOLVED] LINQ Aggregate algorithm explained

  • 2011-08-18 09:51:21
  • Alexander Beletsky
  • 243787 View
  • 690 Score
  • 12 Answer
  • Tags:   c# .net linq

13 Answered Questions

[SOLVED] When to use .First and when to use .FirstOrDefault with LINQ?

  • 2009-06-21 19:15:29
  • Metro Smurf
  • 540471 View
  • 798 Score
  • 13 Answer
  • Tags:   c# .net linq

7 Answered Questions

[SOLVED] Multiple "order by" in LINQ

  • 2008-11-18 13:34:11
  • Sasha
  • 578185 View
  • 1538 Score
  • 7 Answer
  • Tags:   linq sql-order-by

10 Answered Questions

[SOLVED] IEnumerable vs List - What to Use? How do they work?

9 Answered Questions

[SOLVED] Group by in LINQ

  • 2011-09-06 19:44:20
  • test123
  • 1225780 View
  • 1007 Score
  • 9 Answer
  • Tags:   c# linq group-by

4 Answered Questions

[SOLVED] Convert Linq Query Result to Dictionary

20 Answered Questions

[SOLVED] LEFT OUTER JOIN in LINQ

  • 2010-08-04 11:18:21
  • Toy
  • 643185 View
  • 506 Score
  • 20 Answer
  • Tags:   c# linq join

3 Answered Questions

[SOLVED] Querying DataColumnCollection with LINQ

2 Answered Questions

[SOLVED] LINQ query to return a Dictionary<string, string>

Sponsored Content