query - select specific columns from datatable in c# using linq




LINQ query on a DataTable (16)

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!


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


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

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 *

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 )


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.


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

Try this

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

Try this simple line of query:

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

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


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;

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


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


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

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

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




.net-3.5