Hi,

I noticed that other than Collections, TypedLists do not have the Filter property in their DefaultView, but only a RowFilter.

I'm using

invoiceAndSupplierTypedList.Clear();
invoiceAndSupplierTypedList.Fill(MAX_RESULTS_INVOICE, invoiceSorter, false, filter);

for the fetches from the database. filter comes from a method that composes from various textbox-, combobox-, checkbox-, datetimepickers- contents (or skips them, if they're unset).

My problem is that I do not want to access the database everytime the filter changes. Instead I want to do that only if the number of Rows goes beneath a certain threshold (or when the filter weakens and I need more rows).

! My request now is to have a method in the PredicateExpression that converts it into returns a RowFilter.

I'd also be grateful for tips on how to do it with my present version.

The invoiceAndSupplierTypedList is composed of 15 fields from 4 tables in the database (invoice, supplier, invoicegroup, clerk).

ORM assembly, runtime version : 2.6.8.1013 Selfservicing for .NET 2.0

Edit: I forgot to mention it, but I'm only filtering on things that are in the TypedList here. The filter doesn't include fields that are not in the TypedList. It's kinda obvious, really, that in-memory-filtering wouldn't work otherwise. That might also be the reason it's not there yet, but I'd still appreciate some pointers here.

The filter for DataView objects (which is the filter you're looking for) is a bit cumbersome to create.

What you could try, as you're using selfservicing, is to implement in your project a subclass of the class DbSpecificCreatorBase and implement the methods you see fit, you could copy the MS Access' dbspecific creator class for example to start.

Then, on your predicate expression instance (the filter), set its DatabaseSpecificCreator property to an instance of your DbSpecificCreatorBase.

Then, call the filter's ToQueryText(ref int uniqueMarker) method. This will give a string.

You're not there yet, you've to replace the parameter names in the string with the VALUES of the parameters in the filter.Parameters collection..

that should at least give you a string which could be used as filter. Not sure if it 100% matches the RowFilter syntax, but you could tweak that a bit I think with the dbspecificcreator implementation.

Thanks for the hint! I'll look into it and try that. My database is postgreSQL 8.4 but that shouldn't make it much different.

I'll give you an update as soon as I've some results.

Actually, where can I find a sample of the DbSpecificCreator? I checked the generated code folder and there's nothing of the like in it, though it might be because of an altered code generation process.

What do have to do to get such a sample?

csharpbeginner wrote:

Thanks for the hint! I'll look into it and try that. My database is postgreSQL 8.4 but that shouldn't make it much different.

I'll give you an update as soon as I've some results.

Actually, where can I find a sample of the DbSpecificCreator?

Look in the runtime lib sourcecode, every DQE has an implementation

Thanks for all the help, but it grew increasingly more complex, the farther down I went that path. Also the Predicate filters on the column names as they are in the database, e.g. Invoice.InvoiceID while the RowFilter expects the column names as the column names of the DataTable (the Field Alias of the TypedList). I now went with an extension of my previous method that creates both Filter at the same time:

        // Declarations
        private readonly int MAX_RESULTS_INVOICE = Properties.Settings.Default.Max_Results_Invoice;
        private readonly int MIN_RESULTS_INVOICE = Properties.Settings.Default.Min_Results_Invoice;
        private int invoiceCollectionItemsCount;

        private InvoiceAndSupplierRow selectedInvoice;
        private enum InvoiceFilterElements
        {
            Date_From,
            Date_To,
            Clerk,
            [...]
            Supplier,
            Description,
            Total_Count_Filter_Elements
        }

        private Boolean[] invoiceFilterOn = new Boolean[ (int)InvoiceFilterElements.Total_Count_Filter_Elements ];
        private IPredicate[] invoiceFilter = new IPredicate[ (int)InvoiceFilterElements.Total_Count_Filter_Elements ];
        private String[] invoiceRowFilter = new String[ (int)InvoiceFilterElements.Total_Count_Filter_Elements ];

        private void initInvFilter()
        {
            for ( int i = 0 ; i < invoiceFilterOn.Length ; i++ )
                invoiceFilterOn[ i ] = false;
        }

Example Eventhandler:

        private void tbxInvFilterDescription_TextChanged(object sender, EventArgs e)
        {
            invoiceFilterOn[ (int)InvoiceFilterElements.Description ] = ( ( (TextBox)sender ).Text.Length > 0 );
            if ( invoiceFilterOn[ (int)InvoiceFilterElements.Description ] )
            {
                invoiceFilter[ (int)InvoiceFilterElements.Description ] =
                     new FieldLikePredicate(InvoiceFields.Description, "%" + ( (TextBox)sender ).Text + "%");
                invoiceRowFilter[ (int)InvoiceFilterElements.Description ] =
                    "Description LIKE '%" + ( (TextBox)sender ).Text + "%'";
            }
            doFilterInvoices();
        }

The "stitching together":

        private IPredicateExpression getInvoiceFilter()
        {
            PredicateExpression predExp = new PredicateExpression(InvoiceFields.InvoiceID > 0); // *)

            for ( int i = 0 ; i < (int)InvoiceFilterElements.Total_Count_Filter_Elements ; i++ )
            {
                if ( invoiceFilterOn[ i ] ) { predExp.AddWithAnd(invoiceFilter[ i ]); }
            }
            return predExp;
        }

        private String getInvoiceRowFilter()
        {
            String rowFilter = "";
            int i = 0;
            for ( ; i < (int)InvoiceFilterElements.Total_Count_Filter_Elements ; i++ )
            {
                if ( invoiceFilterOn[ i ] )
                {
                    rowFilter = invoiceRowFilter[ i ];
                    break;
                }
            }
            i++;
            for ( ; i < (int)InvoiceFilterElements.Total_Count_Filter_Elements ; i++ )
            {
                if ( invoiceFilterOn[ i ] )
                {
                    rowFilter += " AND " + invoiceRowFilter[ i ];
                }
            }
            return rowFilter;
        }

*) the invoices with negative IDs contain management information and are not "normal" - the users never see them.

And the actual filtering:

        private void doFilterInvoices()
        {
            IPredicateExpression filter = getInvoiceFilter();
            invoiceAndSupplierTypedList.DefaultView.RowFilter = getInvoiceRowFilter();

            if ( ( dgvInvoice.Rows.Count < MIN_RESULTS_INVOICE
                && invoiceAndSupplierTypedList.GetDbCount(false, filter) > dgvInvoice.Rows.Count )
                // database may contain more elements
                || ( dgvInvoice.Rows.Count == invoiceCollectionItemsCount
                && invoiceCollectionItemsCount < MAX_RESULTS_INVOICE ) )
            // filter weakened, fetch more
            {
                refetchInvoices(filter);
            }
        }

        private void refetchInvoices(IPredicateExpression filter)
        {
            //refetch from Database with the selected Filters
            Status = "Loading invoices...";
            invoiceAndSupplierTypedList.Clear();
            invoiceAndSupplierTypedList.Fill(MAX_RESULTS_INVOICE, invoiceSorter, false, filter);
            invoiceCollectionItemsCount = invoiceAndSupplierTypedList.Count;
            Status = "";
        }

I did some testing and it queries remarkably seldom, even though it reacts on each change of input. At the moment the MIN/MAX are at 50 / 200.

Thanks for the feedback