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).
我有一个非常简单的数据库,我正在使用linq to sql。我有一个datagridview来显示表的内容。我希望用户能够过滤出现在datagridview中的行,如果可能的话,不需要对数据库进行另一次查询(我的资源非常少,所以解决方案必须尽可能快)。
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.
我想过使用BindingSource类的Filter属性,所以我创建了一个,将DataSource属性设置为linq to sql表达式。当用户添加过滤器时,我设置了Filter属性。在半小时之后我发现,BindingSource不支持过滤。见鬼,太好了;但那又怎样呢?在使用谷歌再花了半个小时并且基本上没有找到任何可用的东西后,我决定使用System.Collections.Generic.List来存储行,因为我能够过滤掉它。没关系,但我还需要存储原始列表(如果用户删除过滤器),我还需要支持多个过滤器。
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:
虽然这不是一个非常好的解决方案(至少我没有发现它很吸引人),但这就是我的工作。我决定写一个包装类,因为我可能需要在以后的任何时候重新使用这个解决方案。基于以下理论,我考虑过创建一个FilteredList类(在我使用Google进行一些搜索并找不到任何现有的实现之后):
- 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.
我存储一个包含表中所有行的List,
我将过滤器(它们是Predictate表达式)存储在BindingList中(所以我可以知道列表是否已更改并重新过滤行),
我将过滤的行存储在List中,当在源列表或过滤器上没有进行任何修改时,将其用作缓存,
我保留一个布尔值(_NeedsRefiltering),意味着是否必须在源行上应用现有的过滤器来重新生成缓存,
该类必须实现IList接口,因此它可以作为DataGridView的DataSource。
Here comes the source code of my FilteredList class:
这是我的FilteredList类的源代码:
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.
由于两个列表(源列表和筛选列表),我遇到了一些问题,但我认为我已经正确处理了它们。或者我可能没有,因为DataGridView似乎不接受它作为DataSource:没有异常抛出,简单地说,没有任何东西出现(不是空的datagridview出现,但没有任何东西 - 不是列,也不是空行来添加更多项目)。好吧,那很奇怪。我尝试将_FilteredSource直接设置为DataSource,它很好 - 直到我添加了一个过滤器并在我收到错误时尝试向下滚动:System.IndexOutOfRangeException:Index 180没有值。
Screenshot: alt text http://shadow.crysis.hu/dgv_error.png
屏幕截图: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.
说实话,我不知道出了什么问题。我试图调用DataGridView的Invalidate,Update和Refresh方法 - 结果相同。
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?
如何使用linq to sql有效地过滤出现在DataGridView中的结果?
为什么我不能将FilteredList用作DataGridView的DataSource?
上面的代码有什么问题?
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.
因此,我尝试遵循Marc Gravell建议的内容,并实现了System.Collections.IList接口而不是通用接口。它工作,所以我可以将它绑定到DataGridView的DataSource属性,它显示所有行,但当我添加一个过滤器并开始向下滚动(由于某种原因,列表不刷新,直到我开始滚动 - Invalidate(),Refresh()和Update()没有帮助它开始将那些奇怪的IndexOutOfRangeException-s作为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)...
有什么想法怎么做这个东西?我不敢相信带有datagridview的linq to sks如此刻苦(很遗憾,但这太荒谬了)...
1 个解决方案
#1
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).
要使用DataGridView,您需要实现非通用IList,而不是通用IList
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:
有效地重新过滤LINQ-to-SQL,你不想使用Predicate
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...
编写真正的过滤列表非常棘手。我已经为内存中的对象完成了它(虽然我不能发布代码) - 但它需要大量的对象跟踪等。除非你绝对需要这个,否则可能更容易保持简单并且只显示简单snapsnots,跟踪添加/删除。对于简单的快照,只需ToBindingList()就足够了......
#1
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).
要使用DataGridView,您需要实现非通用IList,而不是通用IList
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:
有效地重新过滤LINQ-to-SQL,你不想使用Predicate
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...
编写真正的过滤列表非常棘手。我已经为内存中的对象完成了它(虽然我不能发布代码) - 但它需要大量的对象跟踪等。除非你绝对需要这个,否则可能更容易保持简单并且只显示简单snapsnots,跟踪添加/删除。对于简单的快照,只需ToBindingList()就足够了......