Use LINQ to get datatable row numbers meeting certain conditions

Question

How can I get an array of datatable row numbers which meet a certain criteria? For example I have a datatable with a column "DateTime". I want to retrieve the row numbers of the datatable where "DateTime" equals the variable startTime.

I know how to retrieve the actual row, but not the number of the row in the datatable.

Any help will be appreciated :)

See http://stackoverflow.com/questions/1165028/how-do-i-add-row-number-to-a-linq-query-or-entity — Muhammad Hasan Khan, Feb 15 '12 at 14:35
See http://code.msdn.microsoft.com/LINQ-to-DataSets-09787825 and scroll down to Select - Indexed — Anders Wendt, Feb 15 '12 at 14:41

Anthony Pegram · Accepted Answer · 2012-02-15T14:43:21.663

7

If I am reading the question right, using the overload of Select that allows a second input for the index may work for you. Something like

var indices = 
    table.AsEnumerable()
         .Select((row, index) => new { row, index })
         .Where(item => item.row.Field<DateTime?>("DateTime") == startTime)
         .Select(item => item.index)
         .ToArray();

If that date matches on the first, third, and sixth rows, the array will contain indices 0, 2, and 5. You can, of course, add 1 to each index in the query if you would like row numbers to start at 1. (ex: .Select(item => item.index + 1))

edited Feb 15 '12 at 14:43

answered Feb 15 '12 at 14:36

Anthony Pegram

123,721
27
225
246

Anthony, is it possible to get the results into another `DataTable` instead of `var` using `ToArray()`? Or should I just convert/cast `var` into a `DataTable`? Wish there was `ToDataTable()` ;) – bonCodigo Jul 11 '14 at 03:26

Tim Schmelter · Answer 2 · 2012-02-15T14:41:44.923

7

int count = tblData.AsEnumerable()
    .Count(row => row.Field<DateTime>("DateTime").Equals(startTime));

or as query:

int count = (from row in tblData.AsEnumerable()
             where row.Field<DateTime>("DateTime").Equals(startTime)
             select row).Count();

edited Feb 15 '12 at 14:41

answered Feb 15 '12 at 14:36

Tim Schmelter

450,073
74
686
939

+1 Are you sure that you need `AsEnumerable()` there? I think `Count` plays nicely with SQL, so you can rely on LINQ2SQL doing the job for you entirely in the RDBMS layer. – Sergey Kalinichenko Feb 15 '12 at 14:39
@dasblinkenlight, it's not clear that he is using Linq-to-SQL. When the OP says datatable, I take it to mean an actual in-memory object of the `DataTable` type. This could, of course, be very wrong. – Anthony Pegram Feb 15 '12 at 14:42
@AnthonyPegram You're right, I assumed it's linq-2-sql when in fact the question does not say so. – Sergey Kalinichenko Feb 15 '12 at 14:43

score -1 · Answer 3 · answered Feb 15 '12 at 14:37

This is not possible. Note that with SQL (I assume you use SQL), the row order returned is not guaranteed. Your rows are ordered physically according to the primary key. So if you want a reliable row identifier, you must use your primary key number/id.

Use LINQ to get datatable row numbers meeting certain conditions

3 Answers3