LINQ to SQL的，在一个DataGridView筛选结果 [英] Linq to sql, filtering results in a datagridview

问题描述

我有我使用LINQ to SQL中为其一个非常简单的数据库。
口有一个DataGridView显示表的内容。我希望用户能够如果可能筛选中出现的datagridview行，而不会使另一查询数据库（我在资源非常低，所以该解决方案必须是尽可能快）。

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

我想过使用的BindingSource类的过滤器属性，所以我创建了一个，DataSource属性设置为LINQ to SQL的表达。当用户添加了一个过滤器，我设置了Filter属性。像半一小时后，我发现，这BindingSource的不支持过滤。该死的，伟大的;那么是什么呢？再花一个半小时使用谷歌和发现基本没什么可用之后，我deceided使用System.Collections.Generic.List存储行，因为我能够过滤。这是所有的权利，但我也需要存储原始列表（万一用户删除一个过滤器），我还需要支持多个过滤器。

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.

这工作，虽然它不是一个非常好的解决方案（至少我没有发现它的吸引力） ，但是这是所有我非得。我deceided写一个包装类，因为我可能需要以后重新使用该解决方案随时随地。我想到了创建一个FilteredList类（后我做了一些搜索与谷歌，并没有发现任何现有的实现），基于以下原理：

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存储列表与表中的所有行，


• 我存储在一个的BindingList（过滤器（这是Predictate表达式）这样我就可以知道，如果列表改变和重新-filter行）


• 我过滤的行存储在一个列表中，当没有信号源列表或过滤器，
$ b上所做的修改作为缓存$ b
• 我保持一个布尔值（_NeedsRefiltering），这意味着在现有的筛选无论是否有应用在源行重新生成缓存，


• 的类必须实现IList接口，因此它可以作为DataGridView的数据源

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

下面来我FilteredList类的源代码：

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();
    }
}

我有，因为这两个列表中的一些问题（在源列表和过滤列表），但我想我已经正确地处理它们。或者，也许我没有，因为DataGridView中似乎并不接受它作为数据源：未见异常抛出，简单地说，没有出现（不出现一个空的DataGridView，但什么都没有 - 而不是列，也不是一个空行来添加更多项目）。好吧，好吧，那是不可思议。我试着直接设置_FilteredSource作为数据源，这是很好 - 直到我加入了筛选，并试图当我得到的错误向下滚动：System.IndexOutOfRangeException：索引180不具有值

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:

说实话，我不知道什么是错的。我曾试着拨打的DataGridView的取消以及更新和刷新方法 - 同样的结果。

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.

所以...

• 我怎么能高效过滤器使用LINQ在DataGridView显示的结果为sql？


• 为什么我不能用我的FilteredList作为DataGridView的数据源？


• 什么是与代码解决上述问题？

非常感谢您的宝贵时间（当你读到这一切），并帮助（提前）！

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

所以，我试图遵循什么马克Gravell建议，并实现了System.Collections.IList接口而不是通用的。它的工作，这样我就可以把它绑定到DataGridView的DataSource属性，它显示的所有行，但我添加了一个过滤器，并开始向下滚动（出于某种原因，列表不会刷新，直到我开始滚动 - 无效（），刷新（）和Update（）不帮助它），它开始给那些怪异的IndexOutOfRangeException-S作为DataError-S。

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.

任何想法如何做这个东西？我不能相信，LINQ用的datagridview为sql吸这么辛苦（抱歉，但这是越来越ridicolous）...

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

推荐答案

要使用 DataGridView的，你需要实现非通用的IList ，而不是普通的 IList的< T> （或者更简单，更好的：继承的BindingList< T> ，它通过提供事情，比如修改通知 INotifyPropertyChanged的）。对于LINQ到SQL工作，我有一些的在Usenet 信息，可能是有用的（假设它仍持有的水 - 它已经有一段时间）

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?

重新过滤LINQ到SQL有效，你不希望使用谓词< T> ;要使用表达式来; Func键< T，BOOL>> ;这使您可以通过 Queryable.Where 传下来到数据库，也就是（你有一个的IQueryable< T> 来源）是这样的：

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);
}

编写一个真正的过滤列表是非常棘手的。我已经做到了在内存中的对象（我不能发布的代码，虽然） - 但它需要目标跟踪等的许多除非你绝对需要这一点，可能更容易保持简单，只显示简单的snapsnots，只是跟踪的添加/删除。对于简单的快照，只需 ToBindingList（）就足够了...

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

这篇关于LINQ to SQL的，在一个DataGridView筛选结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！