Reputation: 11266

Perform .Select() on IQueryable with List of Func<T, string> delegates

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.

Upvotes: 4

Answers (4)

David Nelson

Reputation: 3714

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 if I have a choice I never will again.

Upvotes: 1