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.)

Answer 2

It is not trivial to do this dynamically, but if you have a limited set of combinations of columns you want to retreive you can do an explicit select like this:

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

The extra select step is necessary because LINQ2SQL won't create partial entities for you.

Create a method for each common combination of columns (especially the initial) the users wants to retrieve.

However to make this dynamic you can build a query with you entity stored as a property in an anonymous class and collect your result properties in another anonymous class in second property in the same anonymous class. Finally you select your entities from the collected objects into objects of the correct type.

public List<Entity> GetEntities()
{
    using (var context = new CensusEntities())
    {
        var combinedResult = (from e in context.Entities
            select new {
                Entity = e,
                CollectedValues = new
                                  {
                                      // Insert default values of the correct type as placeholders
                                      col1 = 0, // or "" for string or false for bool
                                      col2 = 0, // or "" for string or false for bool
                                      // ...
                                      col49 = 0, // or "" for string or false for bool
                                      col50 = 0, // or "" for string or false for bool
                                  }
        );

        // Then copy each requested property

        // col1
        if (useCol1)
        {
            var combinedResult = (from e in combinedResult
                select new {
                    Entity = e,
                    CollectedValues = new
                                      {
                                          col1 = e.Enitity.col1, // <-- here we update with the real value
                                          col2 = e.CollectedValues.col2, // <-- here we just use any previous value
                                          // ...
                                          col49 = e.CollectedValues.col49, // <-- here we just use any previous value
                                          col50 = e.CollectedValues.col50, // <-- here we just use any previous value                                          }
            );
        }

        // col2
        if (useCol2)
        {
         // same as last time
                                          col1 = e.CollectedValues.col1, // <-- here we just use any previous value
                                          col2 = e.Enitity.col2, // <-- here we update with the real value
                                          // ...
        }

        // repeat for all columns, update the column you want to fetch

        // Just get the collected objects, discard the temporary
        // Entity property. When the query is executed here only
        // The properties we actually have used from the Entity object
        // will be fetched from the database and mapped.
        return combinedResult.Select(x => x.CollectedValues).ToList()
        .Select(x=>new Entity{col1 = x.col1, col2 = x.col2, ... col50 = x.col50}).ToList();
    }
}

There will be lots of code, and a pain to maintain, but it should work.
If you are going this route I suggest that you build a code generator that builds this code with reflection from your LINQ context.

Answer 3

Try something like this

using (var context = new CensusEntities())
{
    var q = from e in context.Entities
        select e.myfield1,e.myfield2;
    return q.Tolist();
}

The resulting query should be lighter and also all the data conversion that goes underneath.

But if you really need to build dynamic input, I think some dynamic sql should be involved. So

1. build the dynamic SQL and get a data table
2. use a datatable to a dynamic object conversion as shown here How can I convert a DataTable into a Dynamic object?

BTW a lot of hard work, I think you should considered using the first block of code.