How to remove NULL columns from a LINQ query for ObjectDataSource

Question

As the question suggests I want to remove all of the columns from a LINQ query where all the rows in that column are empty.

At the minute I am using LINQ to run my initial query with the required date and site filters, this then returns an IEnumerable object to my ObjectDataSource within a GridView. This all works fine.

But I am having difficulties implementing the code to remove the empty / NULL columns (LINQ is very new to me).

Here is my working code to return the IEnumerable to the ObjectDataSource:

public IEnumerable<WebApplication1.HL_Log> Select(string startDate, string endDate)
    {

        DateTime start = DateTime.Now.AddDays(-1);
        DateTime end = DateTime.Now;

        if (startDate != "01/01/1900")
        {
            DateTime.TryParse(startDate, out start);
        }

        if (endDate != "01/01/1900")
        {
            DateTime.TryParse(endDate, out end);
        }


        DataClasses1DataContext db = new DataClasses1DataContext();

        var query = from h in db.HL_Logs
                    where h.ID_Location == 45
                    && h.Time_Stamp >= start.Date
                    && h.Time_Stamp <= end.Date.AddDays(1)
                    orderby h.Time_Stamp
                    select h;

        return query;

    }

And so far I have been able to convert the result to a datatable and remove the null columns as I have done in other projects with a datatable using the below:

//Convert to a Datatable
DataTable dt = new DataTable();
dt = LINQToDataTable(query);

//Now remove the completely NULL columns
foreach(var column in dt.Columns.Cast<DataColumn>().ToArray())
{
    if (dt.AsEnumerable().All(dr => dr.IsNull(column)))
        dt.Columns.Remove(column);
}

But I need to then convert this back to IEnumerable before I can return it to my ObjectDataSource. So really my qustion is either, how do I convert the datatable back to an IEnumerable or am I using a very inefficent approch to this, if so what would be the best way to achieve my result??

My Overall code so far for this::

    public IEnumerable<WebApplication1.HL_Log> Select(string startDate, string endDate)
    {

        DateTime start = DateTime.Now.AddDays(-1);
        DateTime end = DateTime.Now;

        if (startDate != "01/01/1900")
        {
            DateTime.TryParse(startDate, out start);
        }

        if (endDate != "01/01/1900")
        {
            DateTime.TryParse(endDate, out end);
        }


        DataClasses1DataContext db = new DataClasses1DataContext();

        var query = from h in db.HL_Logs
                    where h.ID_Location == 45
                    && h.Time_Stamp >= start.Date
                    && h.Time_Stamp <= end.Date.AddDays(1)
                    orderby h.Time_Stamp
                    select h;

        //Convert to a Datatable
        DataTable dt = new DataTable();
        dt = LINQToDataTable(query);         

        //Now remove the completely NULL columns
        foreach(var column in dt.Columns.Cast<DataColumn>().ToArray())
        {
            if (dt.AsEnumerable().All(dr => dr.IsNull(column)))
                dt.Columns.Remove(column);
        }

        //return ""HELP"";

    }

Thanks for your help!!

possible duplicate of [Filtering Null values in Select](http://stackoverflow.com/questions/401935/filtering-null-values-in-select) — Rajesh, Nov 20 '14 at 11:22
Looking at the existing code, I believe OP wants to filter columns, not rows, in order to hide them from a `GridView`. — StuartLC, Nov 20 '14 at 11:53

score 1 · Accepted Answer · edited May 23 '17 at 11:57

TL;DR

If the consumer of the Select method is the GridView DataBind(), and you require an entirely dynamic solution (e.g. the GridView can be bound to other types other than HL_Log), you may be better off staying with returning the filtered DataTable solution, as it will be difficult to implement dynamic row hiding against an arbitrary collection of an unknown type.

To be honest, I can't see a better, generic way to achieve what you are already doing - AFAIK metadata like DataTable, Xml or ExpandoObject would be required?

In more Detail

Since your method returns an enumeration of a strongly typed entity (IEnumerable<WebApplication1.HL_Log>), you can't selectively 'drop' properties or fields from the entity - all instances of HL_Log will have all properties. I'm assuming you currently have AutoGenerateColumns set to true on the GridView, hence the need to trim useless columns in the source. It will also be difficult / tedious to project different types based on whether all values of a database column are null (e.g. trying to return IEnumerable and adding properties dynamically to ExpandoObject).

An alternative to this approach would be to hide the column from the GridView as a presentation tier concern. Note however, that this would mean that you would need to change the AutoGenerateColumns setting to false (reason here), and explicitly check all of the visible properties of Hl_Log

var hlLogs = myObject.Select(someStartDate, someEndDate).ToList();
if (hlLogs.All(hl => hl.Property1 == null))
{
    myGridView.Columns["Property1"].Visible = false;
}
// Same for Property2, etc.

This could of course quickly becomes tedious.

thanks for your response, how would I go about returning the database to the objectdatasource as you suggest? As you have mentioned modifying the gridview at a presentation level would be very tedious, so I want to try and avoid this... — neilrudds, Nov 20 '14 at 15:48
What I'm trying to say is that if the method returns `IEnumerable` and is then bound to the `GridView`, you will explicitly need to check `.All()` for each column to see if the column should be hidden or not. You might be able to use reflection (to get all Properties of an entity) and then iterate the rows, but this is all rather messy. Your original design returning `DataTable` (or other metadata) is probably about as simple as it gets? — StuartLC, Nov 20 '14 at 15:54
Marking this as the answer as your advice has helped me to produce my desired result.. Many Thanks — neilrudds, Nov 22 '14 at 16:51
Can you possibly paste or describe your solution by editing your question? This will help future users :) — StuartLC, Nov 22 '14 at 16:57

How to remove NULL columns from a LINQ query for ObjectDataSource

1 Answers1