locked
Filetering on columns and EF RRS feed

  • Question

  • Let's say that I have a table with 12 colums, and i have a form displaying a grid and 12 dropdownboxes.

    I want to usethe dropdownboxes to filter the data. But if I select a value from the combobox(wich is a select distict of a specific column) all the other comboboxes and the grid should filter accordingly. It should be possible to extend the filter over all the 12 comboboxes. Narrowing each search with the extra filter criteria.

    I have implemented something like this with an old orm mapper, how would i implementr this with the EF ?

     


    Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread
    Wednesday, November 10, 2010 12:14 PM

All replies

  • Hi Well0549,

    Do you mean that when you select one value from a combobox, the other comboboxes will show the values corresponding to the item you selected in the first combobox? If so, you could bind these comboboxes to a BindingSource, set the proper DisplayMember, then set the data source of the BindingSource to the EF query results. Please correct me if I misunderstood you.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, November 11, 2010 6:27 AM
  • Well at first the comboboxes will show the (distinct) result from a specific column so each combobox will cover a specific column. If i select an item in the combobox, the selected value should be a kind of filter expression. So after selection, the dataset should be retrieved again (but now ith much less records) and every combobox should show the data after the filter is applied.

    This is an operation on very large datasets. So what you effectively do is a kind of drill down. There is some styff taht i didn't tell yet.

    1. The SQL Table on wich we have to filter can be very large. (we will never retrieve more than 5000 rows for the grid)
    2. The Comboboxes should show a distinct select of a specific column (but now over all columns)

    So when you select for example a specific month in the combobox (assuming month is a field in the db) the grid will follow on the filtered conditions (but only top 5000) and the comboboxes should be following the filter as well.

    Suppose we have a sold items table.......

    So when I select month = 3 ( inthe combo) and go to the Article Combo i dont want to see all the christmas stuff, cause there was no christmas stuff sold in March......

    Hope this clears up what i want ?

     

     

     


    Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread
    Thursday, November 11, 2010 10:27 AM
  • Hi Well0549,

    Then I think you need to subscribe the SelectIndexChanged or SelectValueChanged event of the combobox, in the event handler to filter the data and re-assign the data source of the bindingsource, something like :

    private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {           
        int iMonth = (int)comboBox1.SelectedValue;
        var temp = ((ObjectQuery<myTable>)(db.myTable.Where(p => p.Month == iMonth))).Execute(MergeOption.AppendOnly);
        myTableBindingSource.DataSource = temp;
    }

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, November 12, 2010 10:10 AM
  • But how do I include all the other selected values in the where clause.

    can I do something like

    If combo1.selectedvalue != "All"
    {
    db.myTable.Where(p.Month == combo1.selectedvalue;
    }
    If combo2.selectedvalue != "All"

    {
    db.myTable.Where(p.Type == combo2.selectedvalue;
    }
    If combo3.selectedvalue != "All"

    {
    db.myTable.Where(p.Selector == combo3.selectedvalue;
    }

    My current DAL allows me to do something like this....

     

    If combo1.selectedvalue != "All"
    {
    db.myTable.Where.Add(p.Month == combo1.selectedvalue);
    }
    If combo2.selectedvalue != "All"

    {
    db.myTable.Where.Add(p.Type == combo2.selectedvalue;
    }
    If combo3.selectedvalue != "All"

    {
    db.myTable.Where.Add(p.Selector == combo3.selectedvalue;
    }

    wich would result in a query:

    Select * from xyz where month=1 and Type="Seller" and Selector="Voice"

    This makes it very easy to add extra conditions to the where clause.
    I am wondering if such a construct is possible for the EF as well ?

     


    Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread
    Wednesday, November 17, 2010 8:43 AM