Filter a list results in call to Oracle

[afva]

Hello,

I have a list of items and I want to filter them. First time I fill the list like this:

Code: Select all

var q = from c in DB.V_CT_GBRK
                        orderby c.NAME
                        select c;

When a users types in text in a textBox, after every typed-in character I call:

Code: Select all

var q = from c in DB.V_CT_GBRK
                        where c.NAME.ToUpper().StartsWith(filter_ipar.ToUpper())
                        orderby c.NAME
                        select c;

I assumed that only the first time the query would actually go to Oracle to get the list of users. But, when I look in dbMonitor, I see that the query is sent to Oracle repeatedly: for every character I type in the textBox.
Off course this is not what I want.

Is this meant to be?

Thanks in advance.[/code]

[Halbert]

Hello,

I have a list of items and I want to filter them. First time I fill the list like this:

Code: Select all

var q = from c in DB.V_CT_GBRK
                        orderby c.NAME
                        select c;

When a users types in text in a textBox, after every typed-in character I call:

Code: Select all

var q = from c in DB.V_CT_GBRK
                        where c.NAME.ToUpper().StartsWith(filter_ipar.ToUpper())
                        orderby c.NAME
                        select c;

I assumed that only the first time the query would actually go to Oracle to get the list of users. But, when I look in dbMonitor, I see that the query is sent to Oracle repeatedly: for every character I type in the textBox.
Off course this is not what I want.

Is this meant to be?

Thanks in advance.[/code]

This is very nice post thanks for sharing the great post.

[StanislavK]

Could you please specify the ORM solution you are working with?

For example, this is the expected behaviour for LinqConnect. After the first query was executed, LinqConnect caches the resulting entity objects, and gets them from the cache on further queries (so that there is no need to materialize them again). However, further queries (even if they are the same as the first one) do cause roundtrips to the server, as it is necessary to check the actual rows that satisfy the query conditions (e.g., a row could have been added or deleted).

You can, e.g., enumerate the complete list of users and then work with this local collection instead of the DataContext table:

Code: Select all

var list = (from c in DB.V_CT_GBRK orderby c.NAME select c).ToList();
var q = from c in list 
        where c.NAME.ToUpper().StartsWith(filter_ipar.ToUpper())
        orderby c.NAME 
        select c;