entity framework oracle dot connect

Question

User240584894 posted

Hi, I need some help...

when I use 'distinct' and 'take' with entity framework pointing to oracle db using dot connect, the query being generated is bad.

it does the take 10 first followed by distinct which results in data loss....

any work around for these ?'

the entity framework query is - query.Select(select).Distinct().Take(100).ToList()

SELECT
"Limit1".NM AS name
FROM ( SELECT DISTINCT
 "Extent2".NM AS name
 FROM  item "Extent1"
 LEFT OUTER JOIN category "Extent2" ON "Extent1".ID = "Extent2".ID
 WHERE ("Extent1".ID = 8070) AND ROWNUM <= 100
)  "Limit1"

Answer 1

User269602965 posted

Try this from SQL prompt

and if works move into code without the double quotes

In addition, you might need to put the SCHEMA_NAME before each TABLE_NAME separated by Period Symbol

SELECT 
  Limit1.NM AS name
FROM 
  (
   SELECT DISTINCT 
     Extent2.NM AS name
   FROM  
     item Extent1
   LEFT OUTER JOIN 
     category Extent2 
     ON Extent1.ID = Extent2.ID
   WHERE 
     Extent1.ID = 8070 
     AND 
     ROWNUM <= 100
  ) Limit1
  

Answer 2

User240584894 posted

The query  is generated by entity framework and I suppose its an issue with dot connect or entiy framework.

Answer 3

User269602965 posted

go to the ORacle OTN for .NET forum as it is more frequently visited by Oracle Corporation employees who developed .NET