I am working on Web Api odata, backend database is oracle.
I have a table in oracle database called student. Columns are RollNo, FIRST_NAME, LAST_NAME, city, county, status.
Records have duplicates in the table. below query gives me correct data.
Select distinct RollNo, FIRST_NAME, LAST_NAME from student where STATUS = 'A' order by FIRST_NAME
Issue: 'Status' and 'order by' filter i am passing through odata query which works fine for me. But then distinct filter doesn't works for me.
I tried to pass through odata like http://stackoverflow.com/questions/30121610/applying-distinct-to-odata-query but no use.
I am getting error that 'Outer Apply not supported in Oracle 11g'.
Then i tried distinct clause in lambda in my api but got same error.
Seems EF uses 'outer apply' for distinct which is not supported in oracle.
I need to apply odata filters, thus return type should be Iqueryable<student>.
Can someone please help me in with distinct clause on EF which should work on oracle.
Member
213 Points
189 Posts
WebApi Odata distinct issue
Jun 23, 2016 06:18 PM|Harry US11|LINK
I am working on Web Api odata, backend database is oracle.
I have a table in oracle database called student. Columns are RollNo, FIRST_NAME, LAST_NAME, city, county, status.
Records have duplicates in the table. below query gives me correct data.
Select distinct RollNo, FIRST_NAME, LAST_NAME from student where STATUS = 'A' order by FIRST_NAME
Issue: 'Status' and 'order by' filter i am passing through odata query which works fine for me. But then distinct filter doesn't works for me.
I tried to pass through odata like http://stackoverflow.com/questions/30121610/applying-distinct-to-odata-query but no use.
I am getting error that 'Outer Apply not supported in Oracle 11g'.
Then i tried distinct clause in lambda in my api but got same error.
Seems EF uses 'outer apply' for distinct which is not supported in oracle.
I need to apply odata filters, thus return type should be Iqueryable<student>.
Can someone please help me in with distinct clause on EF which should work on oracle.
Star
10024 Points
1821 Posts
Microsoft
Re: WebApi Odata distinct issue
Jun 24, 2016 03:22 AM|Yohann Lu|LINK
Hi Harry US11,
I suggest you can try to use EF5 and install the following packages.
First thing is to install Entity Framework via NuGet Package Manager
Next you need to install WebApi v2.2 with OData support.
Finally you need to install the Oracle.ManagedDataAccess library, which is called ODP.NET.
Then, test your program again.
Best Regards,
Yohann Lu
MSDN Community Support
Please remember to "Mark as Answer" the responses that resolved your issue.