I'm looking to add an "advanced search" capability to my ASP.NET/SQL Server 2005 application. Ideally, I'd like it to be table driven. For example, if my schema changes with the addition of a new column to a table that I want to search, I'd like to have the UI reflect the addition of the new column as a searchable field. I can envision some control tables that contain the searchable fields, their types, associated lookups, etc.
These tables can be consulted when constructing the UI. My question lies with the best approach to create the dynamic SQL. One approach would be to construct a parameterized SQL statement myself. But I'm intrigued by Linq, and somehow I think that the System.Linq.Dynamic namespace -- and Linq Expression Trees -- might provide a more elegant/robust solution?
Hopefully any such solution would work with either Linq to Sql or Linq to Entities. Am I right to investigate this path, or are is this problem space not really one of the use cases for expression trees?