Linq with DataTables vs plain SQL

Question

I'm working on an app that generates reports that are basically standard SQL operations (sum/average on data grouped by A/B where X=Y etc)

the parts of the query can be defined by the user at runtime. as implemented, the raw data is in a a DataTable and I "parse" the query parameters into a linq expression (basically query operands map to DataTable column names). it's nifty and all, and wasn't much work, but I'm not entirely sure why I don't just put the data in a SQLite table and just use actual SQL, and just create query strings from the user's input.

I know this is pretty broad, but is there some advantage that I'm missing now that down the line might prove linq to be a better implementation choice? the only thing that comes to mind is that if I want to move beyond a DataTable to my own classes for some reason, I can still use linq to query them.

Answer 1

You could put the datatable into a SQLite data and use actual SQL. I personally believe the only reason to refactor code for that reason is memory footprint. If the datatable is huge (big enough it ends up in the Large Object Heap), then you may not want to hold it in memory for long periods of time. Otherwise there isn't much reason to go that route, operating on information in memory is always faster then accessing information from disk. Otherwise I see no reason to remove the work you did with LINQ and redo it all with SQL and a SQLite file.

Answer 2

Sounds like you've got a good solution already.

If you want to move to SQLite, think of other advantages eg saving to disk, transactions, etc.

Or best of both worlds: LINQ over Sqlite (i assume this is possible?)