C# – Linq to sql, filtering results in a datagridview

c++datagridviewlinq-to-sql

I have a very simple database for which I'm using linq to sql.
I have a datagridview to show the contents of the table. I want the user to be able to filter the rows appearing in the datagridview, if possible without making another query to the database (I'm really low on resources, so the solution has to be as fast as possible).

I thought about using the Filter property of BindingSource class, so I created one, set the DataSource property to the linq to sql expression. When the user added a filter, I set the Filter property. After like half an hour I found out, that BindingSource does not support filtering. Hell, great; but then what does? After spending another half an hour using Google and finding basically nothing usable, I deceided to use a System.Collections.Generic.List to store the rows, because I'm able to filter that. That was all right, but I also needed to store the original List (in case the user removes a filter) and I also need to support multiple filters.

So I had two Lists: one with all the rows the query resulted and one with the rows that met the filter's conditions. I didn't test it with multiple filters, though.

That worked, although it wasn't a really nice solution (at least I didn't find it appealing), but that was all I'd got. I deceided to write a wrapper class, because I may need to re-use this solution anytime later. I thought about creating a FilteredList class (after I made some searches with Google and didn't find any existing implementations), based on the following theory:

  • I store a List with all the rows in the table,
  • I store the filters (which are Predictate expressions) in a BindingList (so I can know if the list changed and re-filter the rows),
  • I store the filtered rows in a List, serving as a cache when there are no modifications made on the source list or the filters,
  • I keep a boolean value (_NeedsRefiltering) meaning whether or not the existing filters have to applied on the source rows to regenerate the cache,
  • The class has to implement the IList interface, so it can serve as a DataSource for the DataGridView.

Here comes the source code of my FilteredList class:

public class FilteredList<T> : IList<T>
{
    private bool _NeedsReFiltering = false;
    private BindingList<Predicate<T>> _Filters;

    public BindingList<Predicate<T>> Filters
    {
        get
        {
            if (this._Filters == null)
            {
                this._Filters = new BindingList<Predicate<T>>();
                this._Filters.RaiseListChangedEvents = true;
                this._Filters.ListChanged += delegate(object sender, ListChangedEventArgs e)
                {
                    this._NeedsReFiltering = true;
                };
            }
            return this._Filters;
        }
        set
        {
            this._Filters = value;
            this._NeedsReFiltering = true;
        }
    }

    private List<T> _Source;
    public List<T> Source
    {
        get
        {
            return this._Source;
        }
        set
        {
            this._Source = value;
            this._NeedsReFiltering = true;
        }
    }

    private List<T> __FilteredSource = new List<T>();
    private List<T> _FilteredSource
    {
        get
        {
            if (this._NeedsReFiltering)
            {
                this._NeedsReFiltering = false;
                this.Refilter();
            }
            return this.__FilteredSource;
        }
        set
        {
            this.__FilteredSource = value;
        }
    }

    public List<T> FilteredSource // Only for setting it as the DataGridView's DataSource - see my comments after the code
    {
        get
        {
            return this._FilteredSource;
        }
    }

    public FilteredList()
    {
        this._Source = new List<T>();
    }

    public FilteredList(int capacity)
    {
        this._Source = new List<T>(capacity);
    }

    public FilteredList(IEnumerable<T> source)
    {
        this._Source = new List<T>(source);
        this._NeedsReFiltering = true;
    }

    public void Refilter()
    {
        this.__FilteredSource = this._Source;

        if (this._Filters == null)
        {
            return;
        }

        foreach (var filter in this._Filters)
        {
            this.__FilteredSource.RemoveAll(item => !filter(item));
        }
    }

    public int IndexOf(T item)
    {
        return this._FilteredSource.IndexOf(item);
    }

    public void Insert(int index, T item)
    {
        this._FilteredSource.Insert(index, item);
        this._Source.Add(item);
    }

    public void RemoveAt(int index)
    {
        //this._Source.RemoveAt(index);
        this._Source.Remove(this.__FilteredSource[index]);
        this._NeedsReFiltering = true;
    }

    public T this[int index]
    {
        get
        {
            return this._FilteredSource[index];
        }
        set
        {
            this._Source[this._Source.FindIndex(item => item.Equals(this._FilteredSource[index]))] = value;
            this._NeedsReFiltering = true;
        }
    }

    public void Add(T item)
    {
        this._Source.Add(item);
        this._NeedsReFiltering = true;
    }

    public void Clear()
    {
        this._Source.Clear();
        this._FilteredSource.Clear();
        this._NeedsReFiltering = false;
    }

    public bool Contains(T item)
    {
        return this._FilteredSource.Contains(item);
    }

    public void CopyTo(T[] array, int arrayIndex)
    {
        this._FilteredSource.CopyTo(array, arrayIndex);
    }

    public int Count
    {
        get { return this._FilteredSource.Count; }
    }

    public bool IsReadOnly
    {
        get { return false; }
    }

    public bool Remove(T item)
    {
        var r = this._Source.Remove(item);
        this._FilteredSource.Remove(item);
        return r;
    }

    public IEnumerator<T> GetEnumerator()
    {
        return this._FilteredSource.GetEnumerator();
    }

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return this._FilteredSource.GetEnumerator();
    }
}

I had some problems because of the two lists (the source list and the filtered list), but I think I've handled them correctly. Or maybe I haven't, because DataGridView doesn't seem to accept it as DataSource: no exception thrown, simply, nothing appears (not an empty datagridview appears, but nothing at all – not the columns, nor an empty row to add more items). Well, well, that's weird. I tried setting the _FilteredSource directly as a DataSource, and it was fine – until I added a filter and tried to scroll down when I get the error: System.IndexOutOfRangeException: Index 180 does not have a value.

Screenshot:
alt text http://shadow.crysis.hu/dgv_error.png

To be honest, I have no idea what's wrong. I have tried to call the DataGridView's Invalidate, Update and Refresh methods – same results.

So…

  • How could I efficiently filter the results appearing in the DataGridView using linq to sql?
  • Why can't I use my FilteredList as a DataSource for the DataGridView?
  • What's the problem with the code above?

Thank you very much for your time (if you read all this) and help (in advance)!

So, I tried to follow what Marc Gravell advised, and implemented the System.Collections.IList interface instead of the generic one. It worked, so I could bind it to the DataSource property of the DataGridView, and it displayed all rows, but as I added a filter and began to scroll down (for some reason, the list isn't refreshed until I start to scroll – Invalidate(), Refresh() and Update() doesn't help it) it started to give those weird IndexOutOfRangeException-s as DataError-s.

Any ideas how to do this stuff? I can't believe that linq to sql with datagridview sucks so hard (sorry, but this is getting ridicolous)…

Best Solution

To work with DataGridView, you need to implement the non-generic IList, not the generic IList<T> (or simpler and better: inherit from BindingList<T>, which provides things like change notifications via INotifyPropertyChanged). For working with LINQ-to-SQL I have some info on usenet that might be useful (assuming it still holds water - it has been a while).

re "rest of the problem"... can you be more specific?

Re filtering LINQ-to-SQL efficiently, you don't want to use Predicate<T>; you want to use Expression<Func<T,bool>>; this allows you to pass this down to the database via Queryable.Where, i.e. (where you have an IQueryable<T> source) something like:

IQueryable<T> data = tableSource;
// then for each filter "expr"
{
  data = data.Where(expr);
}

Writing a true filtered list is very tricky. I've done it for in-memory objects (I can't post the code, though) - but it takes a lot of object tracking etc. Unless you absolutely need this, it may be easier to keep things simple and just display simple snapsnots, tracking just additions/removals. For simple snapshots, just ToBindingList() may suffice...

Related Question