Filter a list results in call to Oracle

Filter a list results in call to Oracle

Postby afva » Fri 21 Oct 2011 10:45

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]
afva
 
Posts: 39
Joined: Thu 13 Aug 2009 21:22

Re: Filter a list results in call to Oracle

Postby Halbert » Mon 24 Oct 2011 05:24

afva wrote: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.
Halbert
 
Posts: 1
Joined: Mon 24 Oct 2011 05:19
Location: USA

Postby StanislavK » Tue 25 Oct 2011 14:47

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;
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle