Collections from LINQ to SQL and the ability to filter

Question

Hi, I have asked this question on more than one forums and it seems no one would even want to take a crak at it.

My problem is simple and i would guess everyone has run into it when using LINQ to SQL.

If your have a LINQ object called: Person and you would like to poulate a list box based on all of the people you have in your DB the task is simple:

BindingListCollectionView view;
view = (BindingListCollectionView)CollectionViewSource.GetDefault (dataContext.Persons);

Now say you wish to have a text box over the list to filter the results. that would not work since IBindingList interface implemented by the LINQ to SQL objects returns false on "CanFilter" property.

What most people do is create an ObservebleCollection, the folloing is an example im sure most of you use.

ObservebleCollection<Person> col = new ObservebleCollection<Person>(dataContext.Persons.ToList());
ListCollectionView view = (ListCollectionView)CollectionViewSource.GetDefault(col);

Since this will return a ListCollectionView and not a BindingListCollectionView it will be filterbale and all is well with the world.

Here comes the problem, say you have Multi levels of Forign key relations: Person<---Alias<---Tickets

and now you wish to have 3 list boxes binded when a person is selected the second list box will diplay only his Alias's and when an Alias is selected only it's Ticket's are shown, this is very simple with binding and syncronizing. the problem is if i want to add a textbox filter on tob of all of the listboxes ( say a person has over 1000 Aliases and i want to be able to filter them to choose 1 ).

The prvious solution of ObservebleCollection will not work since all the Person objects returned will have EntitySet objects for the forgin relation, and this will again return a none filterbale BindingListCollectionView and not a ListCollectionView.

The only way i found around this is to manually bulid an ObserverbleCollection based on the retunred Query this is tedious work and causes me to tie the BusnessObjects layer and the application Layer. also its very slow since you need to make many trips to the database...

Does anyone have a solution to this?

Thanks, Eric.

Answer 1

I think the Model View View-Model pattern (MVVM) will help you out here. Create a view for your first listbox and ensure it exposes its collection as something that implements INotifyCollectionChanged. Same with your second and third listboxes. You can also make any of them include properties for binding to your textbox for filtering. When the value changes, you simple adjust the in-memory collection that the list is bound to.

Have a google for MVVM since it works quite well. Most examples will relate to WPF but are still applicable for what you are doing.

Also check out a product on codeplex called 'Bindable Linq' which allows you to do things like:

var q = from p in Persons.AsBindable() select p;

Answer 2

Hi DarkwingDuck, I took some time to lookup the MVVM pattern and some examples for it. this is not what i was searching for, When using LINQ to SLQ you actually get all your bussness objects auto generated (thorugh the dbml file) the suggestion of this pattern would be to wrap them up in an additional layer which defires the purpose in my eyes, working with a view on top of the bussnessobjects is enough to control the state and not work on the direct binded object, and is easily wrappable in a transactional system.

say the DB im using has 1000 Objects this method would have me make 1000 wappers and use them to interact with the bussness objects, also since i would be hiding the LINQ to SQL implemenation i think i wouls lose all the lazy loading of the EntitySets since i would need to fill these objects manually after getting the data through linq.

this is exactly what im trying to avoid.

Eric,

Answer 3

Dear Eric I have same problem of your. did you find out any answer for yours? please let me know. amini

Answer 4

Dear Eric I have same problem as you. Did you find out any answer for yours? please let me know. Thanks, Robbert