Linq : select value in a datatable column

How do you use LINQ (C#) to select the value in a particular column for a particular row in a datatable. The equivalent SQL woul开发者_StackOverflow社区d be:

select NAME from TABLE where ID = 0

Thanks in advance.

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?

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;

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.

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

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

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)

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