Select rows from a table according to indexes on other list

Question

Let's see if you can help me. I'm not very good at LINQ, so I don't even know if it is possible. Actually, I think it is, but I couldn't find yet the best way to do it.

I have an object that has a List and a DataTable. This DataTable has the same amount of rows as the amount of items in the list, and they're correlated (first item in the list is related to first row, second item to second row and so it goes). I want to be able to select a date range and its related rows from the DataTable. I'm currently doing it, but it has some loops and an IndexOf, and it is taking me a long time to process. Do you guys have any suggestion on how I can make it faster?

This is basically the structure of the object (I've simplified a bit, but what matters is here):

public class CustomObject(){
    public List<DateTime> dates { get; set; }
    public DataTable table { get; set; }
}

And here is how I'm selecting over it:

private bool SelectRange(DateTime begin, DateTime end, CustomObject custom)
{
    var range = from date in custom.dates
                where date.CompareTo(begin) >= 0 &&
                      date.CompareTo(end) < 0
                select date;

    DataTable tmpTable = custom.table.Copy();  // I'm doing this just to copy the structure of the DataTable
    tmpTable.Clear();

    if (range.Count() > 0)
    {
        List<DataRow> rowList = new List<DataRow>();
        foreach (var date in range)
        {
            int dateIndex = custom.dates.IndexOf(date);
            rowList.Add(custom.table.Rows[dateIndex]);
        }

        foreach (DataRow row in rowList)
        {
            tmpTable.Rows.Add(row.ItemArray);
        }
        custom.table = tmpTable;
    }
    else
    {
        custom.table.Rows.Clear();
    }                    
}

Do you have any ideas on how to optimize this?

Thanks a lot for your attention. Any ideas will be very welcome (corrections on my non-native English will be welcome too).

What is your definition of optimize? Is your code running slow? Or are you hoping to find a "one line solution" ? — Matt Houser, Jun 06 '13 at 20:42
`DataTable.Copy` copies the structure of the table **+ data**. I assume you want to copy the structure(columns) only with an empty table(because you execute `DataTable.Clear` afterwards anyway). Therefore use `DataTable.Clone`. — Tim Schmelter, Jun 06 '13 at 20:46
Would this be a more appropriate question for http://codereview.stackexchange.com ? — gunr2171, Jun 06 '13 at 20:47

score 2 · Accepted Answer · answered Jun 06 '13 at 20:49

If you want to make things simpler, use .Zip() to convert your two independent lists into a single related list.

var newList = custom.dates.Zip(custom.table.Rows, 
  (first, second) => new { Date = first, Row = second });

Once you have that, then you can do a simple select on each date/row pair:

var reducedList = newList.Where(i => i.Date >= begin && i.Date <= end);

After you have that, you can add that reduced list into your data table.

(I have not compiled or tested the above)

+1 I didn't think of Zip but it's a pretty good solution in this case — p.s.w.g, Jun 06 '13 at 20:53

score 1 · Answer 2 · edited May 23 '17 at 10:25

DataTable.Copy copies the structure of the table + data. I assume you want to copy the structure(columns) only with an empty table(because you execute DataTable.Clear afterwards anyway). Therefore use DataTable.Clone:

DataTable tmpTable = custom.table.Clone();

However, you don't need it at all with CopyToDataTable. You can join both collections on the date-column which is more appropriate then the row-index:

var rows = from dt in range
           join row in custom.table.AsEnumerable()
           on dt equals row.Field<DateTime>("DateColumn")
           select row;
DataTable newTable = rows.CopyToDataTAble();

Why is LINQ JOIN so much faster than linking with WHERE?

If you insist on the index linking i would use this query instead of a second index lookup:

int[] indices = custom.dates
    .Select((dt, index) => new{dt,index})
    .Where(x => x.dt >= begin && x.dt < end)
    .Select(x => x.index)
    .ToArray();
var newTable = custom.table.AsEnumerable()
    .Where((row, index) => indices.Contains(index))
    .CopyToDataTable();

Side-note: Instead of range.Count() > 0 you should use range.Any(). Enumerable.Count needs to execute the entire query to get to the total-count even if you just want to know if there is at least one result. Therefore use Enumerable.Any which breaks as soon as possible.

if(range.Any())
{
    // ...
}

Select rows from a table according to indexes on other list

2 Answers2