Linq with DataTables vs plain SQL

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 开发者_如何学Pythonin 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.

"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"

That's a pretty good reason in itself. You application gains a certain degree of language independence from not being tied directly to sql. If you ever choose to change the datastore / backing you'll have a much easier time of it.

Assuming your parsing code is solid, another advantage you gain through your application code using LINQ is that it's strongly typed code and less brittle than sql strings.

Maybe you hadn't considered this or its not really important, but by exposing LINQ you create a friendlier layer to work with.

As suggested, you could put your data into SQLite and still use LINQ. You can get connectors for both entity framework and LinqToSql and then there are libraries like dbLinq.

If you can parse your dynamic queries to linq and then have a linq provider translate this to the underyling database code you abstract your application from technology and infrastructure choices that may seem good now but may not be appropriate in the future. You already stated that you were considering switching to SQLite.

As with all abstractions though it depends on what your priorities are and what you have to live with (or without) that determines whether or not the extra layers of abstractions are important.

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.

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?)