Linq To SQL Select Dynamic Columns

Question

Is it possible to dynamically limit the number of columns returned from a LINQ to SQL query?

I have a database SQL View with over 50 columns. My app has a domain object with over 50 properties, one for each column. In my winforms project I bind a list of domain objects to a grid. By default only a few of the columns are visible however the user can turn on/off any of the columns.

Users are complaining the grid takes too long to load. I captured the LINQ generated SQL query then executed it within SQL Server Management Studio and verified its slow. If I alter the SQL statement, removing all the invisible columns, it runs almost instantly. There is a direct correlation between performance and the number of columns in the query.

I'm wondering if its possible to dynamically alter the number of columns returned from the LINQ generated SQL query? For example, here is what my code currently looks like:

public List<Entity> GetEntities()
{
    using (var context = new CensusEntities())
    {
        return (from e in context.Entities
            select e).ToList();
    }
}

The context.Entities object was generated from a SQL View that contains over 50 columns so when the above executes it generates SQL like "SELECT Col1, Col2, Col3, ... Col50 FROM Entity INNER JOIN...". I would like to change the method signature to look like this:

public List<Entity> GetEntities(string[] visibleColumns)
{
    using (var context = new CensusEntities())
    {
        return (from e in context.Entities
            select e).ToList();
    }
}

I'm not sure how to alter the body of this method to change the generated SQL statement to only return the column values I care about, all others can be NULL.

Answer 1

Something like this should work:

 List<string> columns = new List<string>();
 columns.Add("EmployeeID");
 columns.Add("HireDate");
 columns.Add("City");

Add columns to your list ^.

var result = Class.ReturnList(columns);  

Pass the List to a method ^.

public static List<Entity> ReturnList(List<string> VisibleColumns)
        {
            StringBuilder SqlStatement = new StringBuilder();
            SqlStatement.Append("Select ");
            for (int i = 0; i < VisibleColumns.Count; i++)
            {
                if (i == VisibleColumns.Count - 1)
                {
                    SqlStatement.Append(VisibleColumns[i]);
                }
                else
                {
                    SqlStatement.Append(VisibleColumns[i]);
                    SqlStatement.Append(",");
                }
            }
            SqlStatement.Append(" FROM Entity");
            using (var ctx = new DataClasses1DataContext())
            {
                var result = ctx.ExecuteQuery<Entity>(SqlStatement.ToString());
                return result.ToList();
            }

        }

This basically just makes a SELECT statement with all the fields you passed in with the VisibleColumns list.

In this case, the SQL statement that will be generated by the strings in the VisibleColumns list is:

Select EmployeeID, HireDate, City From Employee

(note: i used the Northwind database to try this out, hence the EmployeeID etc column names. You should replace them with your own, obviously.)