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

[dumian]

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.

[Shalex]

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/hie ... ork-4.html
http://stackoverflow.com/questions/1091 ... ramework-5
https://code.msdn.microsoft.com/windows ... l-bf43a96e