C# Selections with SQL Syntax (Not LINQ!)

added by AdrianAisemberg
2/22/2012 1:55:36 PM

1 Kicks, 313 Views

Select objects in C# with real SQL syntax. Give your users the ability to filter/select data during runtime.


2 comments

dpeterson
2/22/2012 1:55:37 PM
Now, I like having the ability to use SQL syntax, but is there a performance concern over loading the collection into a DataTable just to apply the filter? LINQ is lazy, wouldn't this require loading the entire collection into memory twice, then copying the values you want into another collection?

AdrianAisemberg
2/22/2012 2:15:02 PM
You are right. LINQ queries are lazy and perform better, but the queries are compiled and cannot be defined by a user during runtime (unless you parse the user's expression into a dynamic LINQ expression).

The collection is already in memory. It doesn't matter how do you run a query over it. Even if you run a lazy LINQ query on a collection - the collection is already there, the query itself is executed in a lazy way.

However, creating a DataTable and loading the data into it is an expensive operation. If you already have a way to parse user-defined expressions - I wouldn't recommend using the DataTable approach. I don't know of any way to parse such expression so I use DataTables in my projects (assuming the data is small).
You can take a look at my CLAP project: http://adrianaisemberg.github.com/CLAP/.
I use such a mechanism to parse user-defined validation expressions (here: http://adrianaisemberg.github.com/CLAP/#validation_expression).

The post is about a clever use of an old existing feature. It doesn't talk about the performance of it neither recommends it over LINQ.