c# - Oracle query using Entity Framework is ridiculously slow -

c# - Oracle query using Entity Framework is ridiculously slow -

here setup:

compiling @ .net 4.0 (i can't go higher this) using entityframework against oracle 11gr2 database using odp.net 11.2.0.3.20 using visual studio 2012 ultimate , oracle developer tools visual studio 11.2.0.3.20 performing queries using linq entity

so here's problem: have entities i've created oracle developer tools visual studio (11.2.0.3.20). of entities homecoming results quickly. however, others querying against view/table contains more 20 1000000 records, consistently taking 10 minutes homecoming results (i've verified time via unit tests) simple query:

var fellow member = (from m in context.members m.memberid.equals(memberid, stringcomparison.ordinalignorecase) select m).firstordefault();

i used using devart dotconnect oracle worked well...but company isn't renewing license product , have told me utilize new oracle developer tools visual studio accomplish things.

as work around, i've made direct connection database using oraclecommand provided odp.net (oracle.dataaccess.dll) , results in less second. same doing other queries straight against database using management client.

my best guess why happening perhaps entity loading entire database memory , running queries on it...which horrible, don't believe happening.

can please explain why happening , how can prepare using entity don't have manually rewrite of db queries? update: found reason queries taking 10 minutes complete. (with little experience databases) had set in edmx file:

... <entitycontainer name="mystorecontainer"> <entityset name="my_table" entitytype="mydb.store.my_table" store:type="views" store:schema="myusername" store:name="my_table"> <definingquery> select "my_table"."col1" "col1", "my_table"."col2" "col2", "my_table"."col3" "col3", "my_table"."col4" "col4", "my_table"."col5" "col5", "my_table"."col6" "col6", "my_table"."membership_id" "membership_id", "members"."extra_info1" "extra_info1", "members"."extra_info2" "extra_info2" "myusername"."my_table" "my_table" left bring together "myusername"."members" on "my_table"."membership_id" = "members"."member_id" </definingquery> </entityset> ...

turns out left join takes 10 minutes when straight querying management client well. took left join out...and see increment in speed. here's catch, entityset not entityset querying against when getting slow responses. still response 4-5 times faster if manually write code oraclecommand. can explain why entity slowing things downwards much , when not accessing left bring together query?

wrap input parameter entityfunctions.asnonunicode(memberid).

var fellow member = (from m in context.members m.memberid.equals(entityfunctions.asnonunicode(memberid), stringcomparison.ordinalignorecase) select m).firstordefault();

see https://community.oracle.com/message/10725648

c# linq oracle entity-framework