Linq : select value in a datatable column

Question

How do you use LINQ (C#) to select the value in a particular column for a particular row in a datatable. The equivalent SQL would be:

select NAME from TABLE where ID = 0

Answer 1

Thanks for your answers. I didn't understand what type of object "MyTable" was (in your answers) and the following code gave me the error shown below.

DataTable dt = ds.Tables[0];
var name = from r in dt
           where r.ID == 0
           select r.Name;

Could not find an implementation of the query pattern for source type 'System.Data.DataTable'. 'Where' not found

So I continued my googling and found something that does work:

var rowColl = ds.Tables[0].AsEnumerable();
string name = (from r in rowColl
              where r.Field<int>("ID") == 0
              select r.Field<string>("NAME")).First<string>();

What do you think?

Answer 2

var name = from r in MyTable
            where r.ID == 0
            select r.Name;

If the row is unique then you could even just do:

var row = DataContext.MyTable.SingleOrDefault(r => r.ID == 0);
var name = row != null ? row.Name : String.Empty;

Answer 3

I notice others have given the non-lambda syntax so just to have this complete I'll put in the lambda syntax equivalent:

Non-lambda (as per James's post):

var name = from i in DataContext.MyTable
           where i.ID == 0
           select i.Name

Equivalent lambda syntax:

var name = DataContext.MyTable.Where(i => i.ID == 0)
                              .Select(i => new { Name = i.Name });

There's not really much practical difference, just personal opinion on which you prefer.

Answer 4

If the return value is string and you need to search by Id you can use:

string name = datatable.AsEnumerable().Where(row => Convert.ToInt32(row["Id"]) == Id).Select(row => row.Field<string>("name")).ToString();

or using generic variable:

var name = datatable.AsEnumerable().Where(row => Convert.ToInt32(row["Id"]) == Id).Select(row => row.Field<string>("name"));

Answer 5

var name = from DataRow dr in tblClassCode.Rows where (long)dr["ID"] == Convert.ToInt32(i) select (int)dr["Name"]).FirstOrDefault().ToString() 

Answer 6

var x  =  from row in table
          where row.ID == 0
          select row

Supposing you have a DataTable that knows about the rows, other wise you'll need to use the row index:

where row[rowNumber] == 0

In this instance you'd also want to use the select to place the row data into an anonymous class or a preprepared class (if you want to pass it to another method)

Answer 7

Use linq and set the data table as Enumerable and select the fields from the data table field that matches what you are looking for.

Example

I want to get the currency Id and currency Name from the currency table where currency is local currency, and assign the currency id and name to a text boxes on the form:

DataTable dt = curData.loadCurrency();
            var curId = from c in dt.AsEnumerable()
                        where c.Field<bool>("LocalCurrency") == true
                        select c.Field<int>("CURID");

            foreach (int cid in curId)
            {
                txtCURID.Text = cid.ToString();
            }
            var curName = from c in dt.AsEnumerable()
                          where c.Field<bool>("LocalCurrency") == true
                          select c.Field<string>("CurName");
            foreach (string cName in curName)
            {
                txtCurrency.Text = cName.ToString();
            }