Linq with DataTable .ToList() very slow

Question

facts.UnderlyingDataTable is a DataTable

var queryResults4 = //get all facts
    (from f in facts.UnderlyingDataTable.AsEnumerable()
        where f.RowState != DataRowState.Deleted &&
            FactIDsToSelect.Contains(f.Field<int>("FactID"))
        select f);

var queryResults5 = (from f in queryResults4.AsEnumerable()
    orderby UF.Rnd.Next()
    select f);


return queryResults5.ToList();

The problem is this line queryResults5.ToList();

It returns a list of DataRows. But is super slow to do this.

I am happy to return any object that implements IEnumerable. What should I do? I seems the conversion from whatever the var is to List<DataRow> is slow.

Thanks for your time.

score 2 · Answer 1 · edited May 23 '17 at 11:50

2

First, not the ToList itself is slow but the query that gets executed in this method. So maybe your DataTable contains many rows. I assume also that FactIDsToSelect is large which makes the Contains check for every row slow .

You could use CopyToDataTable to create a new DataTable with the same schema instead of a List since that is more natural for an IEnumerable<DataRow>. However, as i have mentioned, that would not solve your performance issue.

You could optimize the query with a Join which is much more efficient:

var q =   from row in UnderlyingDataTable.AsEnumerable()
          where row.RowState != DataRowState.Deleted 
          join id in FactIDsToSelect
          on row.Field<int>("FactID") equals id
          select row;
var newTable = q.CopyToDataTable();

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

edited May 23 '17 at 11:50

Community

1
1

answered Oct 15 '13 at 07:40

Tim Schmelter

450,073
74
686
939

Great that does seem a whole lot faster! Thanks for your help. – Vincent Vandervaere Oct 15 '13 at 09:16
Just out of interest, how long did it take before and how long does it take now? – Tim Schmelter Oct 15 '13 at 09:30
I didn't really time it. But I would say at least 4 time faster. – Vincent Vandervaere Oct 15 '13 at 16:39

score 0 · Answer 2 · edited Feb 06 '14 at 07:34

0

Please try with following.

List<DataRow> list = new List<DataRow>(UnderlyingDataTable.Select("FactID = " + id.ToString(),DataViewRowState.Unchanged));

You may need to change the DataViewRowState argument in .Select method.

edited Feb 06 '14 at 07:34

John Saunders

160,644
26
247
397

answered Oct 15 '13 at 07:50

mit

1,763
4
16
27

I joined the query with FactIDsToSelect and now it is a whole lot damn faster. Thanks for your time. – Vincent Vandervaere Oct 15 '13 at 09:17

Linq with DataTable .ToList() very slow

2 Answers2