DataTable Select vs LINQ Select

Question

Any advice on when DataTable.Select should be used versus LINQ Select when dealing with an in-memory DataTable?

I find LINQ syntax easier and more powerful, but I'm not sure if there are performance or other issues which make a DataTable select preferable.

(I'm using a third party API that provides a DataTable that has been pre-populated from the database. I need to filter that further in-memory.)

Answer 1

Based upon personal experience, I try to avoid the Datatable.Select. I find it to be slow and has some odd bugs.

One (confirmed and documented by Microsoft) bug I ran into was that DataTable.Select doesn't always evaluate AND conditions correctly when there are parenthesis in the statement.

For example, (Col1 > 1) AND (Col < 10) can fail to return correct answers, whereas Col1 > 1 AND Col < 10 will work correctly.

This bug doesn't show up on every computer. In my case the check I was using ran fine on my development platform and every client computer except one. After I discovered this bug I began shifting to using LINQ for selects and noticed a significant increase in the speed of the operations.

Side note: Without going into long explanations, my company doesn't use a database to store data. All of our operations with DataTables involve in memory tables loaded from flat-files. So I am not talking about LINQ 2 SQL, but LINQ to Dataset.

Answer 2

Without even mentioning LINQ, I would not use DataTable.Select anywhere unless I absolutely had to, since in most cases it means performing in the client something that should probably be performed in the database.

Update: my answer here is probably a bit overstated. There are sometimes legitimate reasons for using a DataTable as a (hopefully) small in-memory database that minimizes client-to-database round trips.