Linq : select value in a datatable column

Question

Hi,

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

Thanks in advance.

Answer 1

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 2

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

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?