Call LPAD function in Linq Query

[Rupesh]

Hello Team,

I am using DotConnect for Oracle (trial Profressional Edition) with Entity Framework 6. I am new to this DotConnect provider and having some issues as below :
1. How do we use or import native Oracle functions through Linq. I tried below code but got exception as "No overload of canonical function 'Edm.Substring' is..."
LPAD function :
var sql = "SELECT VALUE tbl FROM Product as tbl";
var predicate = "Substring(Concat('0000000',it.Product_Number), 7) = '0004321'";
var query = objectContext.CreateQuery<Product>(sql).Where(predicate);

Please reply ASAP.Thanks in advance.

[Shalex]

There are public OracleFunctions (includes LPAD), OracleTextFunctions, OracleSpatialFunctions classes. Unfortunately, the documentation for these classes is not available yet. But you can use IntelliSense to find out the list of functions. JIC: refer to the corresponding article in other dotConnect provider (https://www.devart.com/dotconnect/db2/d ... tions.html, its first sample could be used with EFv1,4,5,6 via ObjectContext descendant, its second sample could be used with EFv4,5,6 via ObjectContext or DbContext descendant), the list of functions in Oracle is different, but approach and use cases are the same.

Try this code:

Code: Select all

    // http://www.devart.com/dotconnect/oracle/docs/?dbmonitor.html
    // var monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

    var query = objectContext.Products.Where(p => Devart.Data.Oracle.Entity.OracleFunctions.Lpad(p.Product_Number, 7) == "0004321");
    var result = query.ToList();