LINQ To SQL Dynamic Select

Question

Can someone show me how to indicate which columns I would like returned at run-time from a LINQ To SQL statement?

I am allowing the user to select items in a checkboxlist representing the columns they would like displayed in a gridview that is bound to the results of a L2S query.

I am able to dynamically generate the WHERE clause but am unable to do the same with the SELECT piece. Here is a sample:

var query = from log in context.Logs select log;
                query = query.Where(Log => Log.Timestamp > CustomReport.ReportDateStart);
                query = query.Where(Log => Log.Timestamp < CustomReport.ReportDateEnd);
                query = query.Where(Log => Log.ProcessName == CustomReport.ProcessName);

                foreach (Pair filter in CustomReport.ExtColsToFilter)
                {
                    sExtFilters = "<key>" + filter.First + "</key><value>" + filter.Second + "</value>";
                    query = query.Where(Log => Log.FormattedMessage.Contains(sExtFilters));
                }

Answer 1

The short answer is don't.

A method has to have a known, specific return type. That type can be System.Object but then you have to use a lot of ugly reflection code to actually get the members. And in this case you'd also have to use a lot of ugly reflection expression tree code to generate the return value.

If you're trying to dynamically generate the columns on the UI side - stop doing that. Define the columns at design time, then simply show/hide the columns you actually need/want the user to see. Have your query return all of the columns that might be visible.

Unless you're noticing a serious performance problem selecting all of the data columns (in which case, you probably have non-covering index issues at the database level) then you will be far better off with this approach. It's perfectly fine to generate predicates and sort orders dynamically but you really don't want to do this with the output list.

---

Some of the comments have forced me to seriously consider whether or not I was correct in my implication that a dynamic output list is actually possible, and I conclude that it is, in spite of being a dangerous swimming-against-the-current idea. In order to pull off this stunt, you'd have to:

1. Generate a new type using Reflection.Emit.
2. Generate an expression tree that initializes it by using Expression.MemberInit.
3. Compile the expression and pass it to the Select method.
4. Return a weakly-typed System.Object from your method and use Reflection to access the members by name.

It's not the kind of thing I would ever want to see in production code, but there you have it - it's possible.

Answer 2

You don't need to do that at the query level (that would be pretty hard anyway, since you would need to dynamically create a type at runtime)... It's much easier to handle that in the GridView itself, by explicitly declaring the columns you want to display.