How to query a Dataset using LINQ?

Anitha Anu

I want to display results in grid aafter qurying dataset using linQ. How can we do this?

Lost User

See How to get an answer to your question - LINQ Discussion Boards[^] and Articles/LINQ[^].

Maciej Los

Start with basics - reading MSDN documentation: [LINQ to DataSet](https://msdn.microsoft.com/en-us/library/bb386977(v=vs.110).aspx) [LINQ to DataSet Examples](https://msdn.microsoft.com/en-us/library/bb399401(v=vs.110).aspx)

Sheila Pontes

Hi Friend.

First - select the data in a variable type var.

Below two example of the select using lingQ

Example 1

Unique table, read all fields

var query = from vep in dataset.Tables["name_my_table"].AsEnumerable()
where (Int16)vep["field1"] == Convert.ToInt16(search_parameter_1)
&& (string)vep["field2"] == search_parameter_2
select vep;

Example 2

two tables with join, read some fields and field ordinate

var query = from lan in dataset.Tables["name_my_table1"].AsEnumerable()
join noc in dataset.Tables["name_my_table2"].AsEnumerable()
on (string)lan["id"] equals (string)pap["id"]
where (int)noc["id"] > 0
orderby noc["date"]
select new
{
data = ((DateTime)noc["date"]).ToString("dd/MM/yyyy")
,
num = noc["field1"].ToString()
,
qtd = lan["field1"].ToString()
,
val = ((decimal)lan["value"]).ToString("0,0.00")
};

Second - Create a datatable with the selected data

datatable dt = new datatable();
dt.Columns.Add("data", typeof(string));
dt.Columns.Add("num", typeof(string));
dt.Columns.Add("qtd", typeof(string));
dt.Columns.Add("val", typeof(string));

DataRow dr;

Example 1

foreach (var item in query)
{
dr = dt.NewRow();
dr["data"] = item.vep["field1"].toString();
dr["num"] = item.vep["field2"].toString();
dr["qtd"] = item.vep["field3"].toString();
dr["val_unitario"] = item.vep["field4"].toString();

dt.Rows.Add(dr);
}

Example 2

foreach (var item in query)
{
dr = dt.NewRow();
dr["data"] = item.data;
dr["num"] = item.num;
dr["qtd"] = item.qtd;
dr["val_unitario"] = item.val;

dt.Rows.Add(dr);
}

Third - Display in grid

my_grid.DataSource = dt;
my_grid.DataBind();

I hope has been helping.

Sheila

User 13592306

Thanks for all the help, and I will reference all of it. My issue was null values...

var q =
from a1 in dt_Jobs.AsEnumerable()
orderby a1.Field("City")
where (a1.Field("City") != null)
select a1.Field("City");
var lstCity = q.Distinct().ToList();
cboCity.DataSource = lstCity;

David McGraw

To avoid null values in your linq result, then you need to write the query like as shown below.

                var q = from a1 in dt\_Jobs.AsEnumerable()
                where !string.IsNullOrEmpty(a1.Field("City"))
                orderby a1.Field("City")
                select a1.Field("City");
                var lstCity = q.Distinct().ToList();
                cboCity.DataSource = lstCity;

To know how to use linq with dataset, check following article it will help you. LINQ to Dataset with Example[^]