5

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));
                }
Aaronaught
  • 120,909
  • 25
  • 266
  • 342
mcass20
  • 1,668
  • 1
  • 22
  • 39
  • 1
    re the scope of `sExtFilters` variable: http://stackoverflow.com/questions/1095707/what-is-the-exact-definition-of-a-closure/1095770#1095770 – Mehrdad Afshari Jun 07 '10 at 14:13
  • P.S. I'm removing the `dynamic-linq` tag because that tag refers to the [dynamic query extension](http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx) (note: that won't help you here, unfortunately). – Aaronaught Jun 07 '10 at 14:17
  • Thanks for bringing the scope pitfall to my attention. – mcass20 Jun 07 '10 at 14:20

2 Answers2

3

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.

Aaronaught
  • 120,909
  • 25
  • 266
  • 342
  • Well it wasn't the answer I was looking for, you bring up an interesting point. – mcass20 Jun 07 '10 at 14:13
  • Is this even *possible* with L2S? The anonymous types used in L2S for custom selects are all compile-time generated; I would be surprised if this was even something that is possible to do even as an intellectual exercise. – Adam Robinson Jun 07 '10 at 14:15
  • @Adam: It's theoretically possible if you dynamically build an expression tree for the `Select`. I say theoretically because I haven't tried this, nor would I. ;) – Aaronaught Jun 07 '10 at 14:19
  • @Aaronaught, it is possible indeed, using the `Expression.MemberInit` method. However, this method requires a `Type` or a `ConstructorInfo`, so you would have to dynamically create a type at runtime – Thomas Levesque Jun 07 '10 at 14:23
  • @Aaronaught, @Thomas: Exactly; while I have no problem believing that you can pass an initialization expression to L2S, you'll have to (dynamically at runtime) create a type that it can populate. – Adam Robinson Jun 07 '10 at 14:25
  • @Adam: Which you could do using `Reflection.Emit` or possibly `ExpandoObject` in .NET 4 (not positive about the latter). Like I said, it's a gong show. :) – Aaronaught Jun 07 '10 at 14:26
  • 1
    @Aaronaught: Indeed. And now, for my next trick, I'll use a strongly-typed ORM tool to perform a completely dynamic (in both conditions and results) query! – Adam Robinson Jun 07 '10 at 14:29
  • need not generate a new type. Just project each row into a XmlElement – Amy B Jun 07 '10 at 17:48
  • @David: Even if you meant `XElement` and not `XmlElement` - how do you plan to do data binding on it? It's an interesting but ultimately ineffective workaround given the context here. – Aaronaught Jun 07 '10 at 18:20
  • We call Select() with a dynamically built expression tree to create a self-projection of our entity in order to clear properties that the user may not have permission to or does not plan on binding to (they specify a projection) before returning it from our service. Unfortunately, Linq to SQL explicitly disallows self-projection, so we actually project into a derived type (one that adds nothing to the base entity type). Funny, self-projections are now touted as a feature of WCF Data Services, see http://msdn.microsoft.com/en-us/magazine/ee336312.aspx. – luksan Jun 08 '10 at 22:41
1

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.

Thomas Levesque
  • 286,951
  • 70
  • 623
  • 758