views:

1709

answers:

4

First of all, let me apologize in case the title doesn't make sense. I'm having a hard time understanding what I'm doing, let alone being able to use the right words for describing what I'm doing.

I'm building a generic grid class in which I define the columns by a header / linq expression combination:

public class Column<T>
{
    public string Header { get; set; }
    public Func<T, string> ValueExpression { get; set; }
}

Usage:

Columns = new List<Column<Employee>>
              {
                  new Column<Employee> {Header = "Employee Id", ValueExpression = e => e.EmployeeID.ToString()},
                  new Column<Employee> {Header = "Name", ValueExpression = e => e.FirstName + " " + e.LastName},
                  new Column<Employee> {Header = "Employee Birthday Year", ValueExpression = e => e.BirthDate.HasValue ? e.BirthDate.Value.Year.ToString() : ""}
              },

I want to project the ValueExpression's (Func<T, string>) on an IQueryable 'employees':

var db = new NorthwindDataContext();
var employees = db.Employees.Select(e => e);

I can get that to work while extracting a IEnumerable<IEnumerable<string>> from employees (a list of lists of strings used in my views) like this:

var elementsList = employees.ToPagedList(PageIndex, PageSize);
var elementStringList = elementsList.ToStringList(Columns.Select(c => c.ValueExpression).ToArray());

Don't mind the PagedList stuff, it's irrelevant and kinda the same as ToList();

Here's the ToStringList() Extension method:

public static IEnumerable<IEnumerable<string>> ToStringList<T>(this IPagedList<T> enumerable, params Func<T, string>[] fields)
{
    foreach (var element in enumerable)
        yield return element.ToStringList(fields);
}

private static IEnumerable<string> ToStringList<T>(this T element, params Func<T, string>[] fields)
{
    foreach (var field in fields)
        yield return field(element);
}

The problem is that this approach involves executing the IQueryable before specifying the fields that have to be returned.

As a result, the following query gets executed somewhere along the way:

SELECT [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[ReportsTo], [t0].[PhotoPath]
FROM [dbo].[Employees] AS [t0]

As you might figure, it is undesired to retrieve ALL fields from the table Employees.

I'm looking for a way to somehow build an extension method on the IQueryable employees in which I can pass a List of Func's (the "ValueExpression" members of Column) and "build" a new IQuerable that way, that will execute SQL that just retrieves the needed fields from the database.

So I'm looking for something like this:

IQueryable employees = employees.SelectByExpressions(Columns.Select(c => c.ValueExpression).ToArray());

Thanks in advance.

A: 

I am not sure I completely understand what your wanting to do but it looks suspiciously like the Predicate Builder

If not it may help you get in the right direction...

J.13.L
Thanks, never heard of it. Lots of sample code on that page, so I might find what I need there. Will check it out this evening.
Thomas Stock
I don't believe that Predicate Builder will do what you want. It is about dynamically building predicates (i.e. Where clauses), whereas you are trying to dynamically build the Select statement.
A: 

Interesting question. I think it is related to this one:

http://stackoverflow.com/questions/606104/linq-expression-tree-question

You basically need a dynamic select.

dmo
A: 

Have you looked into Dynamic Linq. I believe it does what you are attempting to do.

If you have the columns as strings then you can use this to do your select predicate and it will only have those columns in the sql that is executed.

Martin Murphy
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
Martin Murphy
Dynamic Linq will not work. It uses string expressions for predicate and order clauses, but, as I explain below, you cannot use it in Select statements because the ultimate result has to be of a concrete type.
+1  A: 

The basic problem that you are going to run into is that a Select must return an object of some type, but you want that type to have different fields depending on the dynamic query that has been generated. When you use anonymous types, the compiler generates a type for you that has the appropriate fields. But in your case, you don't know at compile time what the field list will look like, so you can't get the compiler to generate the type for you.

While it is certainly possible to dynamically build a type that would only contain the fields you want, I have to stop and question whether this is necessary. Part of the justification of using O/R mappers like LINQ-to-SQL, particularly as opposed to DataSets, is that the cost of maintaining code which dynamically returns different field sets at different times is not worth the minor savings you get from the query or from memory usage. In fact in some cases it can even make query performance worse, because the database server cannot optimize multiple queries with different field lists the same way that it can optimize multiple queries with identical field lists.

Another option would simply be to always return all of the fields, but only display the selected fields. That would certainly be simpler and much easier to maintain. However, if you have measured the performance impact of that solution and determined that it does not meet your requirements, you can certainly look into dynamically generating the necessary types. If you need help with that solution I might be able to work up a sample for you. But if I were you, I would be absolutely certain that I needed to go down that path before starting in that direction.

UPDATE: The other question here is whether your ONLY goal with these queries will be to display them in grids, or some other kind of dynamically bound interface. If that is the case, then it might just as easy to go with a kind of "property bag" solution, where there is no concrete type with the specific fields involved, but merely a container for key/value pairs, similar to a DataRow. However, if you are trying to dynamically create types which will then be manipulated in code, I would strongly advise against it. The technical implementation can be interesting, but maintenance very quickly becomes a nightmare. I have been forced to maintain applications like that before, and I have a choice I never will again.

Thank you for the very interesting answer. Especially the part about the DB server not being able to optimize queries with different field lists is a good point. I have not yet measured the performance, I'll get into that.Either way, I would like to have a solution with the dynamic type generation so that I can compare the performance for different types of tables and queries (many columns, many rows, ...).For that reason and for educational purposes I would really appreciate if you would be willing to cook up a sample to get me in the right direction.
Thomas Stock