Search code examples
c#collectionsdatatabletolist

DataTable.ToList() raises collection was modified exception


My application modifies certain DataTable from multiple threads. I have already implemented exclusive locking mechanism for adding rows and committing to DB (2 different threads were handling it). Now, UI thread (3rd) uses this table to narrow down data using LINQ and then bind result to Chart control. I have already split it into smallest commands possible to find out where it is happening.

var s1 = dataTable.ToList(); //originally it was dataTable.ToEnumerable()
var s2 = s1.Select(LINQ_expr1);
var s3 = s2.Where(LINQ_expr2);
var s4 = s3.OrderBy(LINQ_expr3);

In all cases it happened while executing the first command. The only 100% working option I can think of right now, would be to include my locking mechanism here. I would like to avoid it, so maybe someone knows solution here? I guess it would have to be something that creates a copy of this variable and is resistant to this exception.

This table can have few hundred thousands rows (it is being truncated regularly, but only after being analysed), that's why I am asking for alternative to my locking solution. It would block other threads, but on the other hand would cost less than DataTable.Copy().


Solution

  • It makes no sense to start with a ToList() since the following LINQ methods do process Enumerables and produce IEnumerable<T>s. It may however be necessary to call ToList() at the end, in order to decouple the chart control from the DataTable and the LINQ query. Otherwise the DataTable may execute the whole query repeatedly.

    You are not allowed to modify the DataTable while enumerating it (with ToList() or any another LINQ method). So, make sure that other threads don't modify the DataTable while the query is executing.

    It would probably help to put a lock around the query.

    lock (myLockObject) {
        var result = dataTable.Rows
            .Where(...)
            .Select(...);
            .OrderBy(...);
    }
    chart.DataSource = result;
    

    It is important that all threads use the same static lock object when accessing the data table.

    private/public static readonly object _lock = new();
    

    Since .NET 9 you can also use the new Lock Object which is slightly more efficient.


    It also makes sense to begin with the Where method to narrow down the number of records as soon as possible. There will be less work to be done by the following LINQ methods.

    But the question is, whether multi-threading will really speed up things here, since I/O and UI are involved. I/O is often the bottleneck and I/O can't be multi-threaded.