IN() clause using Linq with Native sql and Oracle temporary tables

IN() clause using Linq with Native sql and Oracle temporary tables

Postby dumian » Tue 28 Oct 2014 14:39

Hi Team,

We have following Problem using latest Devart Driver and ef 6.1 with an Oracle backend.

The IN() Clause it is generating Oracle Execution plan always to be reparsed and cached because of “where in” parameters, and that it is performed sequential, and the objects are locked till the parsing is done. On any requests on same objects is generating delay on Application Side.

sample :
Code: Select all
var prodIDs=GetIDsbyName(prodname).ToList;
var query = from Prod in ctx.G_PRODUCT
                        .Include("G_OLDPRODUCT.G_PROD")
                        where prodIDs.Contains(Prod.PROD_ID)  -- here is a list with numeric items resulted from the subquery
                        && Prod.AKTIV_FLAG == Constants.Yes
                        select pr;


the List of IDs is almost always for Oracle parser different, and I investigate the case and I Try to use and Native Sql in Subquery to define the “where in” clause or second approach will be to use Oracle general temporary tables.

After rewriting the linq expression so :

Code: Select all
var query = from Prod in ctx.G_PRODUCT
                        .Include("G_OLDPRODUCT.G_PROD")
                        where
ctx.Database.SqlQuery<long>
                        (@"SELECT PROD_ID FROM G_PRODUCT
                        CONNECT BY PRIOR PROD_ID = PROD_PROD_ID
                        START WITH PROD_ID = (
                       SELECT PROD_ID FROM G_PRODUCT WHERE PROD_TR_ID = :trId AND AKTIV_FLAG = 'J' AND ROWNUM <= 1))", new OracleParameter("triId", (long)Old.Id)).ToList().Contains(Prod.PROD_ID)
                        && Prod.AKTIV_FLAG == Constants.Yes
                        select pr;


I’ve got following exception

Test method Repository.RepositoryTest.TestLinq_nativeSql threw exception:
System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Data.Entity.Infrastructure.DbRawSqlQuery`1[System.Int64] SqlQuery[Int64](System.String, System.Object[])' method, and this method cannot be translated into a store expression.


My question is what is the best approach to get an better Oracle execution support for this cases.
dumian
 
Posts: 13
Joined: Sat 09 Jun 2012 19:10

Re: IN() clause using Linq with Native sql and Oracle temporary tables

Postby Shalex » Thu 30 Oct 2014 14:37

dumian wrote:Test method Repository.RepositoryTest.TestLinq_nativeSql threw exception:
System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Data.Entity.Infrastructure.DbRawSqlQuery`1[System.Int64] SqlQuery[Int64](System.String, System.Object[])' method, and this method cannot be translated into a store expression.

Entity Framework doesn't allow mixing SQL query and LINQ query.

Useful resources concerning hierarchical queries:
http://nosalan.blogspot.com/2012/09/hierarchical-data-and-entity-framework-4.html
http://stackoverflow.com/questions/10912994/how-to-integrate-a-cte-query-in-entity-framework-5
https://code.msdn.microsoft.com/windowsdesktop/Recursive-or-hierarchical-bf43a96e
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle