0

Basically I have a query where I need to check to see if the data exists on another table.

 bool isInUse;
 IsInUse = entity.H83SAF_HEALTH_PLAN.H83SAF_CONSENT.Any();

When I used clutch to trap the query to see what was going on, I could see the query running basically like this:

 SELECT 
 "Extent1"."ACTIVE" AS "ACTIVE", 
 "Extent1"."IS_COMPLETE" AS "IS_COMPLETE", 
 "Extent1"."DATE_CREATED" AS "DATE_CREATED", 
 "Extent1"."DATE_MODIFIED" AS "DATE_MODIFIED", 
 "Extent1"."CREATED_BY" AS "CREATED_BY", 
 "Extent1"."MODIFIED_BY" AS "MODIFIED_BY"
  ...more columns..natter, natter..
 FROM "H83FTF"."H83SAF_CONSENT" "Extent1"
 WHERE ("Extent1"."HEALTH_PLAN_ID" = 1)

The query itself is fine. I have a problem with the .Any() statement. What I thought should happen is that the query should quit abruptly when the .Any() condition is met.

Yet when the query I run, it looks like like the query is bringing back over 18,000 records (which I don't use) I only want to see if the data exists on the other table if the condition is met - as it is, the query hangs up the website while 18,000 rows are executed with the .Any() statement.

The first row has the condition met but my understanding is that .Any() should quit or stop the moment the condition is met.

I tried firstordefault() yet it still fetches 18000 rows in the memory...

dawriter
  • 425
  • 3
  • 8
  • 21
  • You don't have a condition in the `Any()`. In any case (sorry for the pun), why not just use a `Where()` clause? – Heretic Monkey Apr 05 '17 at 22:02
  • lol - I tried: .Any(x=> x.HEALTH_PLAN_ID = 1) still got that lag where its doing 18000 records. – dawriter Apr 05 '17 at 22:06
  • Use `Where(x=> x.HEALTH_PLAN_ID == 1).Take(1)` and check length/count. Theoretically, that should make the query do `SELECT TOP 1`, which should only return one record to EF... – Heretic Monkey Apr 05 '17 at 22:15
  • What database are your running against anyway? According to [this comment](http://stackoverflow.com/questions/1802286/best-way-to-check-if-object-exists-in-entity-framework#comment67014706_1802366), `Any()` should map to `EXISTS` in SQL Server, which returns a Boolean value... – Heretic Monkey Apr 05 '17 at 22:19
  • Against oracle... – dawriter Apr 05 '17 at 22:20
  • I don't think .Any() translates into exists in Entity Framework 4.0 against Oracle DB...that is what I'm suspecting... – dawriter Apr 05 '17 at 22:44

1 Answers1

0

Finally came to a conclusion is that using .Any() on EF 5.0 does not translate to exist in Oracle 11g - solution was to do a direct call and bring back yes or no.

dawriter
  • 425
  • 3
  • 8
  • 21